asked on

Update cursor in stored procedure

I need a stored procedure which updates a GROUPID column in my ACCOUNTS table. I need to pass the stored procedure a parameter which will be the batchsize. So If I have 1m records in my ACCOUNTS table and I pass the stored procedure the parameter of 100,000. I need ten different values to be populated in the GROUPID column. So:

The first 100,000 records in ACCOUNTS have GROUPID = 1
The second 100,000 records in ACCOUNTS have GROUPID = 2
and so on, until GROUPID is updated for all records in the table.

The ACCOUNTS table can potentially have 10's of millions of records so ideally I'd like to put an update cursor into the stored procedure. Is this possible?

johnsone

Something like this?

procedure update_group (groupsize number) as
begin
for c1rec in (select rownum, rowid from <tab> order by <col>) loop

update <tab>
set <col> = ceil(c1rec.rownum / groupsize)
where rowid = c1rec.rowid;

end loop;
end;
/

I have to run to a meeting, so I did not have time to test. But it should give you an indea.

servantis

ASKER

That works fine, but is extremely slow on large volumes (e.g. 1m) is there any way to make it more efficient?

johnsone

How important is "first xxx records"? You can remove the order by if order of the group is not important.

The only other way I would think of is to use parallel to your advantage:

create table <tab>_temp as
select /*+ full(<tab>) parallel(<tab> 8)*/ rowid rid, ceil(rownum / <grpsize>)group_id
from <tab>
;

create index <tab>_temp_idx on <tab>_temp(rid)
;

update table <tab>
set group_id = (select group_id from <tab_temp> where rid = <tab>.rowid)
;

I have tried to parallelize updates like this, but have not found a useful way to parallelize the update part. It is all by rowid, so it is pretty fast. You can change the update statement to a PL/SQL block like this:

declare
row_cnt pls_integer := 0;
begin
for c1rec in (select * from <tab>_temp) loop
update <tab> set group_id = c1rec.group_id where rowid = c1rec.rid;
row_cnt := row_cnt + 1;
if (row_cnt > 50000) then
commit;
row_cnt := 0;
end if;
end loop;
/

MohanKNair

create procedure update_groupid(batchsize in number) IS
cursor c1 is select * from ACCOUNTS forupdate;
c1t c1%ROWTYPE;
gid number :=0;
BEGIN
open c1;
LOOP
fetch c1 INTO c1t;
exit when c1%notfound;
gid:=gid+1;
update ACCOUNTS set <col1>=c1.col1, .....
GROUPID=ceil(gid/batchsize) WHERE CURRENT OF C1;
end loop;
close c1;
end;
/

MohanKNair

>>cursor c1 is select * from ACCOUNTS forupdate;

The correct syntax is
cursor c1 is select * from ACCOUNTS FOR UPDATE;

ExpertAdmin

Cursors are almost always slower than stright SQL. Try setting up a loop like this:

LOOP
BEGIN
SELECT iGrp = iGrp + 1
UPDATE Accounts
SET GROUPID = iGrp
WHERE ROWNUM BETWEEN (iGrp * 100000) AND (iGrp - 1 * 100000)
END
END LOOP

This simply increments the group ID and updates all records that fall in the range that correspond to it.

I cannot test the syntax since I do not have Oracle on this machine, but it should be close.

M@

ASKER CERTIFIED SOLUTION

ExpertAdmin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MohanKNair

It is better to update inline view

UPDATE (select GROUPID, col1, col2, rownum r1 from accounts order by col2)
set GROUPID=ceil(r1/batchsize);

MohanKNair

Update inline view does not work for inline views containing pseudo column ROWNUM

ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Here is another method using two UPDATE statements

SQL> var batchsize number
SQL> begin :batchsize:=100000; end;
/
SQL> update ACCOUNTS set GROUPID=rownum;
SQL> update ACCOUNTS set GROUPID=ceil(GROUPID/:batchsize);
SQL>