#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Location
    Kurvi
    Posts
    490
    Rep Power
    185

    Updating millions of rows in a batch


    Sooo, I have a table which has plenty of rows. In production, I would estimate it to be from 30 millions to 300 millions. I need to update on column (flag) in all the rows (created before certain date).

    Now saying just:
    Code:
    UPDATE MyTable SET flag = 3 WHERE created < to_date('2010-10-08 23:59:59', 'YY-MM-DD HH24:MI:SS');
    COMMIT;
    Does not seem like a good idea - the commit-buffer would become too big.

    I will write a PL/SQL script for this. The question is, whether I should:
    a) Update each row separately, and commit after every 10000 rows. ( WHERE RowId = [rowId] )
    b) Update 10000 rows with set of dates ( WHERE rowId > [some_row_id] AND RowId < [some_row_id_2]

    In the latter example the some_row_ids would naturally be fetched. The rowIds come from sequence. So which one would be more effective?

    I am not too familiar with PL/SQL or Oracle for that matter.

    thanks,
    J
    Gabba Gabba Hey
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    What exactly do you think that a "commit buffer" is? There is no such thing in Oracle.

    Are you referring to the "UNDO Tablespace"?

    Oracle performs best when you commit your transaction when it needs to be. Committing in chunks slows down your process and consumes more resources.

    If your UNDO is to small to handle that UPDATE then you have to adjust your UNDO size.
    You should not not adjust your transaction handling.

    See what Tom Kyte says on this topic:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

    Comments on this post

    • jst666 agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Location
    Kurvi
    Posts
    490
    Rep Power
    185
    Thanks for the link. In my situation I could do commit after every update, the integrity is not an issue - only performance. There has been a situation before where a script had taken a very long time to run, and there has been some issue that the data to be updated had been too old to perform a commit.

    Just to clarify, the integrity is not an issue because all records before a certain date need to be updated. There will not be a need for rollback. Unless there is a grave error in the script - so it has to be tested properly.
    Gabba Gabba Hey

IMN logo majestic logo threadwatch logo seochat tools logo