2 Replies Latest reply: Oct 11, 2012 2:14 PM by rp0428 RSS

    Deleting 5 million records(slowness issue)

    cool_ora
      Hi guys ,
      we are trying to delete 5 million records with following query .it is taking more time(more than 2 hours).

      delete from <table_name> where date<condition_DT;

      FYI
      * Table is partioned table
      * Primary Key is there
      Pls assist us on this .
        • 1. Re: Deleting 5 million records(slowness issue)
          berx
          cool_ora,

          you are probably asking in the wrong forum.
          I'd suggest [url https://forums.oracle.com/forums/forum.jspa?forumID=75]SQL and PL/SQL.

          I am sure there are plenty of people which will help you in high quality.

          Edited by: berx on Oct 11, 2012 8:41 PM - fixed URL
          • 2. Re: Deleting 5 million records(slowness issue)
            rp0428
            >
            we are trying to delete 5 million records with following query .it is taking more time(more than 2 hours).

            delete from <table_name> where date<condition_DT;

            FYI
            * Table is partioned table
            * Primary Key is there
            Pls assist us on this .
            >
            Nothing much you can do.

            About the only alternatives are
            1) create a new table that copies the records you want to keep. Then drop the old table and rename the new one to the old name. If you are deleting most of the records this is a good approach.

            2) create a new table that copies the records you want to keepl. Then truncate the partitions of the old table and use partition exchange to put the data back.

            3) delete the data in smaller batches of 100K records or so each. You could do this by using a different date value in the WHERE clause. Delete data < 2003, then delete data < 2004 and so on.

            4. If you want to delete all data in a partition you can just truncate the partition. That is the approach to use if you partition by date and are trying to remove older data.