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

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.