1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Does Create table as Select * from OLD copy indexes

Discussion in 'SQL PL/SQL' started by ericzutter, Apr 12, 2009.

  1. ericzutter

    ericzutter Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Yes the question is in the title

    Say I create a new table by
    Code (SQL):
    CREATE TABLE AS
    SELECT * FROM Old_Table
    Will That copy the indexes on the tables as well to the new tables?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No. A normal Create Table .. AS SELECT .... will create a table with rows from specified query but only with NOT NULL constraint by default. Indexes will not be copied / recreated.

    Other constraints need to be recreated.
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    No as rajavu said indexes will need to be re-created

    You can get the index definitions by using metadata.getddl

    Code (SQL):
    SET long 9999999
    SELECT DBMS_METADATA.get_ddl ('INDEX', index_name)
      FROM user_indexes
     WHERE TABLE_NAME = 'OLD_TABLE_NAME';
     
    This will give you the index's code and you can re-create on the new table.
     
    ericzutter likes this.
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And by the way , You need to re-create the index with a different name , if you create it within the same schema. ( Index name cannot be duplicated within the same schema).

    May be this is the reason why oracle Copy only table structure , record and Not Null constraints during CTAS process ( Create Table As Select).Here Not Null Constraints cannot be named but all other constraints and indexed can be named.
     
    ericzutter likes this.
  5. ericzutter

    ericzutter Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    thanks all... i found the DBMS_METADATA.get_ddl really very helpful