Indexes on temp tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Indexes on temp tables

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Indexes on temp tables

    Likely this is expected behavior - can anyone confirm? I am trying to create an index on a temp table and can only do so before I load it up - not afterwards as below

    SQL> CREATE GLOBAL TEMPORARY TABLE PS_PERSONAL_DATA_TEMP ON COMMIT PRESERVE ROWS as SELECT * FROM PS_PERSONAL_DATA WHERE 1=2;

    Table created.

    Elapsed: 00:00:00.03
    SQL> INSERT INTO PS_PERSONAL_DATA_TEMP SELECT * FROM PS_PERSONAL_DATA;

    206989 rows created.

    Elapsed: 00:00:11.04
    SQL>
    SQL>
    SQL>
    SQL> CREATE INDEX PS_PERSONAL_DATA_TEMP ON PS_PERSONAL_DATA_TEMP (EMPLID);
    CREATE INDEX PS_PERSONAL_DATA_TEMP ON PS_PERSONAL_DATA_TEMP (EMPLID)
    *
    ERROR at line 1:
    ORA-14452: attempt to create, alter or drop an index on temporary table already in use


    Elapsed: 00:00:01.01
    SQL>
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    Expected behavior. Exit all current sessions, start new one and create index.

    SQL> !oerr ora 14452
    14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
    // *Cause: An attempt was made to create, alter or drop an index on temporary
    // table which is already in use.
    // *Action: All the sessions using the session-specific temporary table have
    // to truncate table and all the transactions using transaction
    // specific temporary table have to end their transactions.

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    yep yep - the obivious i know - just trying to get around a data load with tables that are already indexed
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width