Rename Table and Index - quickly put new table in production
I have a table with 20 million rows that is rebuilt from scratch daily. The process of populating the table and building the index on this table takes several hours. I would like to know if it's possible/wise to create this table and it's indexes under a temporary name, then simply drop the old one and rename the temporary to the real name; thereby minimizing the downtime of the table to only a few seconds. When I alter the table name do the indexes follow it to the new name or do they need to be rebuilt? Any downside in doing this? Thanks!
ASKER
Unfortunately, rebuilding this is necessary...
Why do I need to analyze the table?
Also I just tried a to rename the table with 'alter table tabname rename to newtabname' and then went into TOAD indexes tab and saw the indexes that were on tabname could now be found under newtabname. Status=Valid, Table=schema.newtabname...
Why do I need to analyze the table?
Also I just tried a to rename the table with 'alter table tabname rename to newtabname' and then went into TOAD indexes tab and saw the indexes that were on tabname could now be found under newtabname. Status=Valid, Table=schema.newtabname...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, if you have the space to hold two copies of the data and indexes, then definitely that's a good way to go to reduce the downtime.
I assume you already have a process to cleanup invalid objects and keep users from locking the table with dml while you refresh. You'll need to include those steps in your new process with the temp table.
And analyze your new table before renaming it.
And last, the fastest tuning is to simply NOT do something.
Is a 20million row rebuild really necessary?
Can you update that data in some other way?