Updating table containing millions of records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Updating table containing millions of records

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    Updating table containing millions of records

    Hi,

    I tried to update a table with 34 million records in single update,
    but it has thrown snapshot too old error.

    Then I tried it with following procedure to make commit frequently
    so that I will not get snapshot error,

    DECLARE
    CURSOR c1 IS SELECT ROWID FROM VEHICLE;
    BEGIN
    --DBMS_OUTPUT.PUT_LINE(P_DUMMY_OUTPUT);
    FOR i IN c1 LOOP
    UPDATE /*+ parallel 8 */VEHICLE v SET OPTION_CD=(SELECT OPTION_CD FROM TRY_OPTION_CD t1
    WHERE v.YEAR_ID=t1.YEAR_ID) WHERE v.ROWID=i.ROWID;
    COMMIT;
    END LOOP;
    END;
    but this procedure also gave the same error.

    Why it is giving this error eventhough I'm commiting frequently?
    what I've to modify in above procedure to run this update successfully?

    Thanks..

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Why it is giving this error eventhough I'm commiting frequently?
    Because you HAVE the old snapshot - the cursor c1 that selects from table being updated.

    what I've to modify in above procedure to run this update successfully?
    Code:
    loop
      update ... where ... and rownum<=10000;
      exit when sql%rowcount<10000;
      commit;
    end loop;
    commit;
    In the where clause you must put the condition to restrict the update to not yet updated rows.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    or configure your undo well enough and do it in one statement again

  4. #4
    If this is a one-time update you could do a create table as select nologging with the new format then swap the tables at the end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width