| Merge-Update vs Normal Update [message #164494] |
Thu, 23 March 2006 12:08  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
I have a 100M-row table and I have to update only 10000 rows. I read some correspondences in the forum and so I tried to do the 'create table as select from 100-M where records need not be updated' and then insert into it the updated records. it was fast, unfortunately, it takes almost the same time (as just directly updating the table) when I rebuild the indexes.
I resorted to MERGE-UPDATE. Surprisingly, it is 20% slower than the normal 'update,set=select() where.'. I wonder why is that.. I could not find any article to justify this.. any input?
|
|
|
|
| Re: Merge-Update vs Normal Update [message #164561 is a reply to message #164494] |
Fri, 24 March 2006 00:18  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
As you discovered, CTAS is only efficient up to a point. You are rebuilding 10000 times the amount of data that the update is updating. I find update more efficient (and this is not a golden rule) for up to 10% of the table. You are only updating 0.0001% of the table, so I'm not surprised CTAS is less efficient.
There is no reason why MERGE should be slower than UPDATE - it depends on your execution plan. If you get an explain plan for both you will find they are different.
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
