I have a large table, let's call it "TEST_TBL" which is partitioned and
has 4 global indexes and 2 local indexes. Local indexes are completely
irrelevant for the story. I executed the following command:
ALTER TABLE TEST_TBL TRUNCATE PARTITION PART_X UPDATE GLOBAL INDEXES;
According to the manual, this should truncate the table partition PART_X, invalidate and rebuild the global indexes. And now we come to the mystery:
According to the above steps, the statement duration should not depend on the partition size. Truncate is a DDL which simply writes high watermark to the beginning of the segment and rebuilding indexes should take about equal time, regardless of the size of the partition being truncated.
However, that is not what I see. Truncating a large partition, with 60M rows takes more than 20 times longer than truncating the partition with only 40K rows. Does anyone have an explanation? I did 10046 trace but I still don't see what the problem is.