Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why is Update faster than Delete?

User_1EG3DJan 7 2011 — edited Jan 9 2011
I have a huge table L_CLAIMTEMP_RSMREPORTS. This contains 9 million records. I'm truncating and populating it daily using sql*Loader.
I'll just give you the sequence of steps which I do , the time and the number of rows affected.
1. DELETE from the table l_claimtemp_rsmreports- 4 million rows deleted - 45 mins Time taken, Commit Issued
2. Update the table l_claimtemp_rsmreports using values from other table - 5 million rows updated -10 mins , Commit
3. Delete from the table l_claimtemp_rsmreports again based on some conditions - 4 million rows deleted - 45 mins, Commit Issued

Finally, the rows which I require is 1 million. I then modified my procedure this way:
1. UPdate the table ll_claimtemp_rsmreports whcih I require-,set a flag to 'U' 5million rows updated- 10 mins taken,
2. Update the table l_claimtemp_rsmreports which has flag as 'U' using values from other table - 5 million rows updated -10 mins , Commit
3. Update the table l_cliamtemp_rsmreports with flag as 'X' where flag='U' - 1 million rows updated - 5 mins, Commit

Here, I am fetching the records from the table l_claimtemp_rsmreports where flag='X'

This I realized is a lot faster. But, I couldn't understand why. I had faced the problem earlier too. Can I generalize that update is faster than delete?
Or is it just my assumption?

My oracle version is 11g.
This post has been answered by Sven W. on Jan 7 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 6 2011
Added on Jan 7 2011
19 comments
6,442 views