How to Optimize or change an update of global indexes

[concorde800]

Hi,

Is there a way to change or optimize updating of global indexes in Oracle 9i.

I have a DDL statement which executes as a part of batch job for 2-3 hours.
How do I change this to work better? The table_1 has partition and indexes on it.

The DDL statement is:

ALTER TABLE table_1 TRUNCATE PARTITION partition_name UPDATE GLOBAL INDEXES

Thanks.

[PAVB]

Time comsumption for UPDATE GLOBAL INDEXES can be correlated to a) Size of the truncated partition and, b) Quantity of Global Indexes.

Smaller partitions will result in a faster process.
Less global indexes will result in a faster process.

[concorde800]

a) Size of truncated partition - Table has 1 column with 2 partitions - One partition has 12 million rows and second has 8 million rows.

b) Quantity of Global Indexes - 13.

Clearly, the size of truncated partition is high enough for performance draining. Is there a way I can optimize this by increasing the number of partitions? Any alternative method?

[PAVB]

Your analysis is correct you can either

1- Increase the number of partitions and perform smaller purges
I.e.: I don't know your partitioning strategy but, imagine your current partitoning strategy is by "month". If you can partition your table by "week" you will end doing four purge processes instead of one -for the same amount of data- but, these smaller purge processes will work faster.

2- You might want to analyze if some of your Global Indexes can be partitioned alognside with the table.
For each global index you bring down you are going to get immediate performance improvement during purging process.