Why is Update faster than Delete?
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.