Rename Table and Index - quickly put new table in production

Posted on 2007-10-18
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
    LVL 73

    Expert Comment

    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

    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 73

    Accepted Solution

    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now