DANGER! Create Table AS

I see lots of talk amongst DBAs about using CREATE TABLE new_table AS SELECT columns FROM old_table WHERE some_criteria;

DROP old_table;

RENAME new_table TO old_table;

It's the first thing suggested to remove a column from a table, do massive deletes without having to hit the redo logs, prevent creation of archive logs.

What do you lose when you do this?
If you want to do massive deletes, you do not get the ON DELETE trigger fired. You do not get privs granted to the table transfered. Named constraints cannot and do not transfer over. They cannot. Compiled objects become invalid when the former old table is dropped.

If you really need to do CREATE TABLE AS; DROP; RENAME; have your creation scripts handy to recreate the named indexes and constraints, grant privs on the table. Plan to recompile all of the procedural objects which reference the table. Remember that views with SELECT * from the old_table need to be re-entered.

For a good starting point, try TOAD. It's a free GUI utility which constructs for you your table definition, constraint/key and grant SQL scripts. It's located at http://www.toadsoft.com.

Oracle Corporation recommends CREATE TABLE AS; DROP; RENAME a lot less often. Take chained row removal. Oracle gives scripts in its tuning manual that

analyze the table for chained rows

create a intermediate_table as select from table where rowid in chained_rows table.

delete the rows from table where rowid in chain_rows table

insert the rows in intermediate_table back into table

drop intermediate_table.

This keeps all the named constraints/keys, triggers, referencing objects intact. Received on Fri Jul 24 1998 - 14:55:03 CDT