Rename Table and Index - quickly put new table in production

Posted on 2007-10-18
Medium Priority
Last Modified: 2013-12-19
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!
Question by:CalDude
  • 2
LVL 74

Expert Comment

ID: 20102044
The indexes will not move by renaming the table.  You'll have to recreate them on the new table.

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?

Author Comment

ID: 20102285
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...  The index is there, but is it bad; pointing to the wrong table name?
LVL 74

Accepted Solution

sdstuber earned 600 total points
ID: 20102463
you analyze it (dbms_stats.gather_table_stats)  so the optimizer can use your table effectively.

no those indexes are ok.
They came with the rename, they are the same indexes that were on it when it was called tabname

I thought you were asking if you had table A with index I and then you created AA
and renamed AA to A would I move to the new AA.  That answer is no.

rename A with I to B and I will point to B.
rename AA with index II to A and II will point to A.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question