PL/SQL for Updates with Frequent Commit

Dear Experts,

I'm very new to PL/SQL. I want to write a procedure to update a table. The table TA fas fields (x number, y varchar(20)) and want to update field y with value 'xyz' if it is null. However since there are hundreds of millions of records with this condition, I would like to use cursors and commit after each update of 1.000 record.

Could you please help me?  

Best Regards
GurcanKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Even then you should ask the DBA prior to that. Cause even batch updating may be a problem with a small table space.
1
GurcanKAuthor Commented:
That's OK, I talked to DBA.
0
sdstuberCommented:
you'll probably want to commit in larger batches in order to reduce run time and consume fewer resources.


for something small like that, assuming no triggers, I would probably do 100,000 or more per commit.

basically, do as many as your undo space will allow.
0
johnsoneSenior Oracle DBACommented:
There are a few ways to do this.  One of the easiest would be something like this:
UPDATE ta 
SET    y = 'xyz' 
WHERE  ROWID IN (SELECT ROWID 
                 FROM   ta 
                 WHERE  y IS NULL 
                        AND ROWNUM < 1001); 

Open in new window

That would update the first 1000 rows.  Then just keep doing that until you have no rows updated.  No need to create a PL/SQL structure at all.  You can commit every 1000 records.  If you want a different number, change the ROWNUM comparison, or run the update multiple times to get to the point where you want to commit.

If you want to do a PL/SQL loop, this is the general approach that I would use.

Create a temporary table that has all the rowids to be updated.
CREATE TABLE ta_upd AS 
  SELECT ROWID upd_rid 
  FROM   ta 
  WHERE  y IS NULL; 

Open in new window

The the update would look something like this:
DECLARE 
    cnt PLS_INTEGER := 0; 
BEGIN 
    FOR c1 IN (SELECT upd_rid 
               FROM   ta_upd) LOOP 
        UPDATE ta 
        SET    y = 'xyz' 
        WHERE  ROWID = c1.upd_rid; 

        cnt := cnt + 1; 

        IF cnt = 1000 THEN 
          cnt := 0; 

          COMMIT; 
        END IF; 
    END LOOP; 
END; 
/ 

Open in new window


NOTE:  I didn't test that code, but it should be pretty close.

The reason for the temporary table is to avoid a ORA-01555, which in this case would indicate a fetch across commits.

For your own education, I will leave you to modify that block so that it uses BULK COLLECT and does the updates from the collection.  It should be a better performing method.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.