1 2 Previous Next 21 Replies Latest reply: Nov 25, 2012 10:54 AM by rp0428 RSS

    Delete millions of records

    Neo-b
      Hello All,

      I am using Oracle 11g R2 (11.2.0.3).

      On daily basis I am purging five millions records from a table.

      What is the best solution to delete this huge number of records?
      Is by single delete statement? or by for loop on all the records?
      Which one is faster? the disadvantage of the single delete that it is consuming my undo tablespace.

      Please advise.

      Regards,
        • 1. Re: Delete millions of records
          sb92075
          NB wrote:
          Hello All,

          I am using Oracle 11g R2 (11.2.0.3).

          On daily basis I am purging five millions records from a table.

          What is the best solution to delete this huge number of records?
          Is by single delete statement? or by for loop on all the records?
          Which one is faster? the disadvantage of the single delete that it is consuming my undo tablespace.

          Please advise.

          Regards,
          single DELETE
          • 2. Re: Delete millions of records
            mBk77
            Partition the table and drop the partition
            Small pctused: For tuning mass deletes you can reduce freelist overhead by setting Oracle to only re-add a block to the freelists when the block is dead empty by setting a low value for pctused.
            Parallelize the delete job if you have multiple CPUs
            If you have a backup then you can perform the delete operation in NOARCHIVELOG
            • 3. Re: Delete millions of records
              Neo-b
              Thanks for your answer.

              You have any explanation ?
              what is the best way to decompose my delete instead of deleting 5 millions records to do 5 delete statements of 1 million each ?

              Regards,
              • 4. Re: Delete millions of records
                Girish Sharma
                See this demo :
                SQL> set timing on;
                SQL> select count(*) from all_objects;
                
                  COUNT(*)
                ----------
                     68571
                
                Elapsed: 00:00:01.94
                SQL> drop table t purge;
                
                Table dropped.
                
                Elapsed: 00:00:00.09
                SQL> create table t
                  2  as
                  3  select * from all_objects order by mod(object_id,0);
                
                Table created.
                
                Elapsed: 00:00:02.98
                SQL> alter session enable parallel dml;
                
                Session altered.
                
                Elapsed: 00:00:00.00
                SQL> delete /*+ parallel(t,8) */ from t;
                
                68571 rows deleted.
                
                Elapsed: 00:00:01.82
                SQL> commit;
                
                Commit complete.
                
                Elapsed: 00:00:00.05
                SQL> select * from t;
                
                no rows selected
                
                Elapsed: 00:00:00.00
                SQL>
                So, I can say that there are around 65K rows per second deletion happened when I enabled parallel dml in the session and then used parallel hint with delete operation.
                Sufficient speed on 11.2.0.1 on my text PC of windows 7 64 bit 2GB RAM.

                Ok, now try with PL/SQL i.e.
                SQL> alter session disable parallel dml;
                
                Session altered.
                
                Elapsed: 00:00:00.00
                SQL> CREATE TABLE test_tbl
                  2  (test_col INTEGER)
                  3  /
                
                Table created.
                
                Elapsed: 00:00:00.03
                SQL> -- Populate it with 100,000 rows
                SQL> BEGIN
                  2  FOR i in 1..100000 LOOP
                  3  INSERT INTO test_tbl
                  4  VALUES (i - TRUNC(i, -1));
                  5  END LOOP;
                  6  END;
                  7  /
                
                PL/SQL procedure successfully completed.
                
                Elapsed: 00:00:04.01
                SQL> COMMIT
                  2  /
                
                Commit complete.
                
                Elapsed: 00:00:00.00
                SQL> -- Delete 60% of the rows. This is the actual delete. You would just replace the
                SQL> -- where clause with yours.
                SQL> DECLARE
                  2  TYPE tt_delete IS TABLE OF ROWID;
                  3  t_delete tt_delete;
                  4  CURSOR c_delete IS
                  5  SELECT ROWID
                  6  FROM test_tbl
                  7  WHERE test_col < 6;
                  8  l_delete_buffer PLS_INTEGER := 5000;
                  9  BEGIN
                 10  OPEN c_delete;
                 11  LOOP
                 12  FETCH c_delete BULK COLLECT
                 13  INTO t_delete LIMIT l_delete_buffer;
                 14  FORALL i IN 1..t_delete.COUNT
                 15  DELETE test_tbl
                 16  WHERE ROWID = t_delete (i);
                 17  EXIT WHEN c_delete%NOTFOUND;
                 18  COMMIT;
                 19  END LOOP;
                 20  CLOSE c_delete;
                 21  END;
                 22  /
                
                PL/SQL procedure successfully completed.
                
                Elapsed: 00:00:00.58
                SQL> select count(*) from test_tbl;
                
                  COUNT(*)
                ----------
                     40000
                
                Elapsed: 00:00:00.01
                SQL>
                BEST WAY TO DELETE OVER A 75,000,000 RECORDS

                And now simple delete
                SQL> drop table t purge;
                
                Table dropped.
                
                Elapsed: 00:00:00.07
                SQL> create table t
                  2  as
                  3  select * from all_objects order by mod(object_id,0);
                
                Table created.
                
                Elapsed: 00:00:02.95
                SQL> delete from t;
                
                68572 rows deleted.
                
                Elapsed: 00:00:04.68
                SQL>
                So bulk delete is fastest then any other.

                Regards
                Girish Sharma
                • 5. Re: Delete millions of records
                  Purvesh K
                  NB wrote:
                  Hello All,

                  I am using Oracle 11g R2 (11.2.0.3).

                  On daily basis I am purging five millions records from a table.

                  What is the best solution to delete this huge number of records?
                  Is by single delete statement? or by for loop on all the records?
                  Which one is faster? the disadvantage of the single delete that it is consuming my undo tablespace.
                  I will ask about your Total records in the table.

                  If you have 6 Million records in your table and you wish to delete 5 Million; it would be suggested to create a Temp table with 1 Million and you can then Drop the Base table and rename the Temp table to you Base Table.

                  But if you have over 10 Million records, then a single Delete statement shall be suggested.

                  You can read the discussion by Tom for Deleting Many rows.
                  • 6. Re: Delete millions of records
                    axlrose
                    not using the single deletion.. prefer the batch del and commit every 100 or more del
                    • 7. Re: Delete millions of records
                      Purvesh K
                      gunsnroses_xjapan wrote:
                      not using the single deletion.. prefer the batch del and commit every 100 or more del
                      Very Bad thought!!! Hope that remains a thought and is never implemented. This would be an epitome of HOW NOT TO DO A DELETE JOB!!!

                      A single delete will always perform better than a batch delete that commits every N rows.

                      Imagine all the extra work your DB has to perform you COMMIT few deleted records.
                      • 8. Re: Delete millions of records
                        Jonathan Lewis
                        NB wrote:

                        On daily basis I am purging five millions records from a table.

                        What is the best solution to delete this huge number of records?
                        Is by single delete statement? or by for loop on all the records?
                        Which one is faster? the disadvantage of the single delete that it is consuming my undo tablespace.
                        Are you also adding 5M rows per day ?
                        How do you identify the 5M rows to be deleted ?
                        How many rows are there in the table just before the delete ?
                        How many indexes are there on the table that would be affected by the delete ?

                        For how long are you allowed to make the data unavailable

                        Have you considered the possibility of enabling parallel DML

                        Have you bought the partitioning option - and have you considered using it
                        Have you considered using partition views if you don't have the partitioned option

                        Regards
                        Jonathan Lewis
                        • 9. Re: Delete millions of records
                          Neo-b
                          Are you also adding 5M rows per day ?
                          How do you identify the 5M rows to be deleted ?
                          How many rows are there in the table just before the delete ?
                          How many indexes are there on the table that would be affected by the delete ?

                          For how long are you allowed to make the data unavailable

                          Have you considered the possibility of enabling parallel DML

                          Have you bought the partitioning option - and have you considered using it
                          Have you considered using partition views if you don't have the partitioned option
                          Yes on daily basis i am inserting 5M records and deleting 5m of sysdate -30.
                          My table can reach 120M records, there is 1 unique index and 4 normal indexes. and this table is partitioned by date.
                          So during the day 5M records will be inserted on a new partition and at 2:00 AM an automatic job will fire to delete sysdate -30 records and then drop it is corresponding partition. I am using Oracle Interval partitioning (Partition each day)
                          • 10. Re: Delete millions of records
                            sb92075
                            gunsnroses_xjapan wrote:
                            not using the single deletion.. prefer the batch del and commit every 100 or more del
                            this is like running a marathon with your right wrist tied to your left ankle.

                            It will just make total completion time longer.
                            • 11. Re: Delete millions of records
                              Gamblesk
                              if you are dropping the partition you just deleted from why not skip the delete and just drop the partition
                              • 12. Re: Delete millions of records
                                Neo-b
                                If you directly dropped the partition your indexes will be unusable, you have to delete and then drop the partition.
                                • 13. Re: Delete millions of records
                                  JohnWatson
                                  You could use the UPDATE GLOBAL INDEXES clause of ALTER TABLE ... DROP PARTITION.
                                  • 14. Re: Delete millions of records
                                    Neo-b
                                    and this will not affect performance during the drop partition ?

                                    Since my indexes will be used by other select and update statements, indexes will not be affected during the drop?
                                    1 2 Previous Next