-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|