Home » RDBMS Server » Performance Tuning » Update vs CTAS
Update vs CTAS [message #199865] Thu, 26 October 2006 08:31 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have to update 46M rows of a table which has 219M rows. Is it better to update it or just recreate? Recreating using CTAS (then table renaming) should be fine, but I have to recreate the indexes also.. and I have 12 indexes (9 bitmap and 3 b-tree)?? Any suggestion before I begin my tedious testing.?? Thanks!
Re: Update vs CTAS [message #199879 is a reply to message #199865] Thu, 26 October 2006 09:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
We are having too many discussions around the same topic recently Smile.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DISPLAYID:6407993912330
Re: Update vs CTAS [message #199884 is a reply to message #199879] Thu, 26 October 2006 10:02 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
a couple of things to keep in mind:

1) make sure you have enough space for a second copy. If you have to keep the old table in place till the new one is ready, then you need to essentially have enough disk space for two copies of the table and each index. This may sound obvious but so many people don't check.

2) along the same lines, make sure you have sufficient sort area space in memory if possible. Index creation will take much less time if it can all be done in memory.

Good luck, Kevin
Previous Topic: Regarding v$librarycache
Next Topic: setting trace file name created using sql trace
Goto Forum:
  


Current Time: Sun Dec 04 18:24:45 CST 2016

Total time taken to generate the page: 0.07429 seconds