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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have to truncate a partition (392,000 records) of a table with 20.000.000 records.

At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 minutes to rebuild them.

Taking a look at the Oracle Docs it gives me three options:

1- (This is what I'm doing)

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD; 

2-

DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98;

3-

ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;

So I have two questions:

1- Which of these options would be the faster in my case? 2- What is the meaning (in option 2) of deleting and then truncating the partition?

Thanks in advance!

share|improve this question

Are the 37 indexes you have on the table global indexes?

Option 1:

Where you truncate and then rebuild This method is most appropriate if the partition that you are truncating has at least 10% of the total data in the table. But rebuilding these 37 indexes is going to take time.

Option 2:

Where you delete and truncate the partition The delete statement here updates any global indexes, fires triggers and generates redo and undo logs. To avoid the generation of logs and make this faster, Just before the deletion, you may alter the partition as

ALTER TABLE sales MODIFY PARTITION dec98 NOLOGGING;

and then proceed with deletion and truncation. But for partitions containing a large percentage of the total data in table, this will still be slower

Option 3:

If you have a global index on the partition, UPDATE INDEXES will truncate this index as well.

My suggestion here would be use Option 2 with NOLOGGING; as below,

ALTER TABLE sales MODIFY PARTITION dec98 NOLOGGING;
DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;
share|improve this answer

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.