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.
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.
https://oracle-base.com/articles/9i/ ... definition
https://oracle-base.com/articles/10g ... _10gR1.php
https://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.
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 1 guest