Re: Strange timing

You’re not rebuilding the global indexes, you are updating them.

It seems like you’re not on 12.1 yet. On 12.1 onwards, the global index maintenance is delayed to an overnight job, making the truncate statement super quick again.

Hope that helps,
Andrew

On Mon, 13 Jan 2020 at 21:54, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l