Delete rows question

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

Delete rows question

Postby Art » Tue Feb 28, 2012 8:45 pm

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.

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Delete rows question

Postby Tim... » Tue Feb 28, 2012 8:56 pm


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. ... definition ... _10gR1.php ... _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
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest