I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no triggers or anything) it's just a data store.

Every month we are required to purge records from the two of the main tables. The criteria for the purge varies and is a combination of row age and a couple of status fields. We typically end up purging between 10 and 50 million rows per month (we add about 3-5 million rows a week via imports).

Currently we have to do this delete in batches of about 50,000 rows (ie. delete 50000, comit, delete 50000, commit, repeat). Attempting to delete the entire batch all at one time makes the database unresponsive for about an hour (depending on the # of rows). Deleting the rows in batches like this is very rough on the system and we typically have to do it "as time permits" over the course of a week; allowing the script to run continuously can result in the a performance degradation that is unacceptable to the user.

I believe that this kind of batch deleting also degrades index performance and has other impacts that eventually cause the performance of the database to degrade. There are 34 indexes on just one table, and the index data size is actually larger than the data itself.

Here is the script that one of our IT people uses to do this purge:


delete FROM tbl_raw 
  where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;

  exit when SQL%rowcount < 49999;





This database must be up 99.99999% and we've only got a 2 day maintenance window once a year.

I'm looking for a better method for removing these records, but I've yet to find any. Any suggestions?

  • Also note there are 30+ indexes in play here
    – jcolebrand
    Commented Jan 19, 2011 at 17:40

6 Answers 6


The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                        ) virtual,
  data            varchar2(100) 
partition   by range  (date_too_old) 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;
  • I may have over simplified the logic behind how records to purge are determined, but this is a very interesting idea. One thing that must be considered, however, is the day to day performance. Purging is "our problem", the client wont accept degraded performance just to solve that. It sounds, from some of the comments and Gary's answer that this might be an issue with partitioning? Commented Jan 20, 2011 at 0:54
  • I'm not sure if this is the answer we're looking for, but this is definitely a very interesting approach that we will investigate. Commented Jan 21, 2011 at 12:50

The classic solution to this is to partition your tables, e.g. by month or by week. If you have not come across them before, a partitioned table is like several identically structured tables with an implicit UNION when selecting, and Oracle will automatically store a row in the appropriate partition when inserting it based on the partitioning criteria. You mention indexes - well each partition gets its own partitioned indexes too. It is a very cheap operation in Oracle to drop a partition (it is analogous to a TRUNCATE in terms of load because that is what you are really doing - truncating or dropping one of these invisible sub-tables). It will be a significant amount of processing to partition "after the fact", but there's no sense crying over spilt milk - the advantages to doing so far outweigh the costs. Every month you would split the top partition to create a new partition for the next month's data (you can easily automate ths with a DBMS_JOB).

And with partitions you can also exploit parallel query and partition elimination, which should make your users very happy...

  • FWIW we use use this technique at my site on a 30Tb+ database
    – Gaius
    Commented Jan 19, 2011 at 18:20
  • The problem with partitioning is that there is no clear cut way to partition the data. In one of the two tables (not the one shown below) the criteria used to do the purge is based on two different (and distinct) date fields, and a status field. For example, if the status is A then if DateA is older than 3 years, it gets purged. If Status is B and DateB is older than 10 years, it gets purged. If my understanding of partitioning is correct, then the partitioning wouldn't be useful in a situation like this (at least as far as the purging is concerned). Commented Jan 19, 2011 at 18:56
  • You can partition by status and subpartition by date range. But if the status (or date) changes, it does effectively a delete from one sub-partition and an insert into the other. In short you can get a hit on your everyday processes to save time on your purging.
    – Gary
    Commented Jan 19, 2011 at 21:53
  • 6
    Alternatively you could create a virtual column that shows DateA when status is A and DateB when status is B and then partition on the virtual column. The same partition migration would occur, but it would help your purging. It looks like this was already posted as an answer. Commented Jan 19, 2011 at 22:00

One aspect to consider is how much of the delete performance result from indexes and how much from the raw table. Every record deleted from the table requires the same deletion of the row from every btree index. If you've got 30+ btree indexes, I suspect most of your time is spent in index maintenance.

This has an impact on the usefulness of partitioning. Say you have an index on name. A standard Btree index, all in one segment, might have to do four jumps to get from the root block to the leaf block and a fifth read to get the row. If that index is partitioned into 50 segments and you don't have the partition key as part of the query, then each of those 50 segments will need to be checked. Each segment will be smaller, so you may only have to do 2 jumps but you may still end up doing 100 reads rather than the previous 5.

If they are bitmap indexes, the equations are different. You probably aren't using indexes to identify individual rows, but rather sets of them. So rather than a query using 5 IOs to return a single record, it was using 10,000 IOs. As such the extra overhead in extra partitions for the index won't matter.


deletion of 50 million records per month in batches of 50,000 is only 1000 iterations. if you do 1 delete every 30 minutes it should meet your requirement. a scheduled task to run the query you posted but remove the loop so it only executes once should not cause a noticeable degredation to users. We do about the same volume of records in our manufacturing plant that runs pretty much 24/7 and it meets our needs. We actually spread it out a little more 10,000 records every 10 minutes, which executes in about 1 or 2 second running on our Oracle unix servers.

  • What about massive 'undo' and 'redo' 'delete' will generate? It chokes IO too...'delete' based approach should certainly be a NO.. NO for large tables. Commented Sep 18, 2016 at 22:10

If disk space is not at a premium, you could be able to create a "work" copy of the table, say my_table_new, using CTAS (Create Table As Select) with criteria that would omit the records to be dropped. You can do the create statement in parallel, and with the append hint to make it fast, and then build all your indexes. Then, once it it finished, (and tested), rename the existing table to my_table_old and rename the "work" table to my_table. Once you are comfortable with everything drop my_table_old purge to get rid of the old table. If there are a bunch of foreign key restraints, take a look at the dbms_redefinition PL/SQL package. It will clone your indexes, contraints, etc. when using the appropriate options. This is a summation of a suggestion by Tom Kyte of AskTom fame. After the first run, you can automate everything, and the create table should go much quicker, and can be done while the system is up, and application downtime would be limited to less than a minute to doing the renaming of the tables. Using CTAS will be much faster than doing several batch deletes. This approach can be particularly useful if you don't have partitioning licensed.

Sample CTAS, keeping rows with data from the last 365 days and flag_inactive = 'N':

create /*+ append */ table my_table_new 
   tablespace data as
   select /*+ parallel */ * from my_table 
       where some_date >= sysdate -365 
       and flag_inactive = 'N';

-- test out my_table_new. then if all is well:

alter table my_table rename to my_table_old;
alter table my_table_new rename to my_table;
-- test some more
drop table my_table_old purge;
  • 1
    This can be considered if (a) purging is a one-off task. (b) if you fewer rows to retain and most of the data to remove... Commented Sep 18, 2016 at 22:14

when dropping a partition, you leave global indexes unusable, that need to rebuild, the rebuild of global indexes would be a big issue, as if you do it online, it will be quite slow, otherwise you need downtime. in either case, can't fit for the requirement.

"We typically end up purging between 10 and 50 million rows per month"

i would recommended using PL/SQL batch delete, several hours is ok i think.

  • 1
    If you have a primary key, then dropping a partition should not make any global indexes unusable. But if the OP has a lot of global indexes there will be a high cost for dropping partitions. In an ideal case when someone is partitioning a table the partitioning is based on the primary key and they don't need any global indexes. That every query is able to take advantage of partition pruning.
    – Gandolf989
    Commented Dec 15, 2014 at 14:03
  • @Gandolf989 dropping a partition wil always make a global index unusable
    – miracle173
    Commented May 1, 2016 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.