?
Solved

Update cursor in stored procedure

Posted on 2006-05-22
9
Medium Priority
?
1,237 Views
Last Modified: 2008-01-16
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?
0
Comment
Question by:servantis
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 16736700
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.
0
 

Author Comment

by:servantis
ID: 16736893
That works fine, but is extremely slow on large volumes (e.g. 1m) is there any way to make it more efficient?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16737297
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;
/
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:MohanKNair
ID: 16739565
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;
/
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16739567
>>cursor c1 is select * from ACCOUNTS forupdate;

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

Expert Comment

by:ExpertAdmin
ID: 16756463
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@
0
 
LVL 7

Accepted Solution

by:
ExpertAdmin earned 500 total points
ID: 16756492
OOPS...forgot an exit....

...DECLARE YOUR VARIABLES HERE...

SELECT COUNT(*) INTO iRecs FROM Accounts;

LOOP
  BEGIN
     SELECT iGrp = iGrp + 1;

     UPDATE Accounts
     SET GROUPID = iGrp
     WHERE ROWNUM BETWEEN (iGrp * 100000) AND (iGrp - 1 * 100000);
     EXIT WHEN (iGrp * 100000) > iRecs;
 END
END LOOP
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16757948
It is better to update inline view

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

Expert Comment

by:MohanKNair
ID: 16757964
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>
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question