Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

BEGIN
LOOP

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

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

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?

share|improve this question
    
Also note there are 30+ indexes in play here –  jcolebrand Jan 19 '11 at 17:40
add comment

5 Answers

up vote 13 down vote accepted

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
                                 end
                        ) 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;
share|improve this answer
    
Posted my comment before I saw your answer. Good show. +1 –  Leigh Riffel Jan 19 '11 at 22:01
    
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? –  Coding Gorilla Jan 20 '11 at 0:54
    
+1 Good solution with example code - using partitions and dropping partitions is best. –  ScottCher Jan 20 '11 at 13:20
    
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. –  Coding Gorilla Jan 21 '11 at 12:50
    
The biggest downside to partitioning, in my opinion, is that the licensing cost for it is extortionate. –  Phil Jul 5 '12 at 8:01
add comment

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...

share|improve this answer
    
FWIW we use use this technique at my site on a 30Tb+ database –  Gaius Jan 19 '11 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). –  Coding Gorilla Jan 19 '11 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 Jan 19 '11 at 21:53
4  
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. –  Leigh Riffel Jan 19 '11 at 22:00
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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