Delete rows question

[Art]

Hello,
I have a table with 5 mill. of rows. It has constraints, indexes, triggers... you name it - it has it all.
Of those 5 mill. rows only 1 mill is active and 4 mill. rows are inactive. I need to move those 4 mill. to a history table then remove them from main table leaving only 1 mill of active rows. What is the best and most efficient way to do this please?
I thought about CTAS - create as select, then drop the orig table, then rename the new table into the name of original name table... But this involves recreating constraints, triggers etc... Is there other way to delete a few mill. of rows efficiently? Please advise.
Thank you very much.
Art.

[Tim...]

Hi.

No. Even with the creation of the triggers and indexes etc, you really need to use CTAS or online table redefinition, which does an "INSERT /*+APPEND */" internally, so it is similar in performance to CTAS.

http://www.oracle-base.com/articles/9i/ ... definition
http://www.oracle-base.com/articles/10g ... _10gR1.php
http://www.oracle-base.com/articles/11g ... _11gR1.php

Large scale updates or deletes are painfully inefficient. Almost always better to CTAS or OTR than performing DML.

Cheers

Tim...