Drop a large table

[aferrarese]

I need to reload a table with a great amount of record (6000000).
Someone told me it may be problems if i drop the table and then reload it.
For You?
Thanks. Andrea.

 

[Dagon]

You will have to rebuild the definition fully i.e. including grants, indexes, triggers etc. Don't just do "create table xxx as select * from yyy where 1=2" and then "drop table yyy".

As an alternative, why don't you truncate the table ? This gets rid of the data but leaves the structure intact.

The other problem you may have is with foreign key constraints. You won't be able to drop or truncate the table if there are foreign key constraints which reference it. You can do "drop table ... cascade constraints", which will automatically drop any foreign key constraints, but you will have to remember to re-create them afterwards.

The best option is to disable all the foreign key constraints. You can identify these as follows:

select constraint_Name from user_constraints
where r_constraint_name = <Primary key of table to be dropped>

Once you have disabled the foreign key constraints, you can truncate your table and reload it. I'd advise making a backup of the table before you do this, of course (use export).

I'd also advise removing any indexes from the table prior to the re-load and then rebuilding them at the end. Also check if there are any triggers on the table and whether these need to be enabled or not. Triggers will slow down the load massively.