Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005

    Unanswered: Commit and Update

    I have an update statement which is updating about 6MIL records approx. I want to commit after every 50000 records.
    I saw many examples for select using Bulk collect and limiting after n number of rows. How can I do the same for Update.

    update tablename
    set col1 =
    decode(col2,'abd',(NVL((balance/var1),0) * (var2))
    + (balance * FACT) ,balance * FACT)
    where period = pi_period;

    Any suggestions will be greatly appreciated.


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    Quote Originally Posted by nandinir
    I have an update statement which is updating about 6MIL records approx. I want to commit after every 50000 records.
    Don't do that. You'll make things worse.

    Details on why you should not commit frequently are here:

    Edit: here are the most important points from Tom why you should only have a single commit:

    Quote Originally Posted by TomKyte
    There is only one thing that dicates when you commit -- when is your transaction complete? Then you commit, period.

    Frequently commiting in the mistaken belief that you are conserving resources or making things go faster only does this for you:

    o slows you down, yes, that is right, SLOWS YOU DOWN -- makes you run SLOOOWWWWEEERRR
    o doesn't conserve any resources, in fact, it consumes more (you generate MORE redo)
    o seriously puts into question the integrity of your data

  3. #3
    Join Date
    Dec 2008

    Thumbs down update

    You can do this by using the rownum, but you have to go for a small programming block. You said that you wanted do a commit every 50000 record so you can do it by using rownum.

    If you want more clarification i will give you what i did using this rownum.

  4. #4
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    And I repeat what @shammat has already said: do NOT commit after every 50000 (or 1000 or 100 or ...) records. Not by using ROWNUM, not by using anything else. Just don't do it.

  5. #5
    Join Date
    Dec 2003
    We have a situation where we read in a ton of recs via an EXTERNAL TABLE and insert them into a table. Every once in awhile the sender puts a string into a column meant for a number, and the whole process errors out.

    We started committing every 1,000,000 records, because restarting the process from scratch meant waiting the ~10 minutes per million records for the insert to replay itself.

    I agree with you mostly, that you should wait until the transaction is complete, but depending on your needs & your knowledge of the system, you can commit mid-transaction on occasion.


  6. #6
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    IMO, describes a MUCH better solution!
    It avoids periodic COMMIT while identifying failed records.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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