PARTITIONS and GLOBAL INDEXES

Running Oracle 9.2.0.6 on Sun Solaris 9 Server The production database is a ticketing system and the primary table containing the ticket information has been partition (range) on timestamp. There are local indexes as well as global indexes on this table.

I started a process to archive the older ticket data by exchanging the partition out to a separate table and then dropping the partition from the production table after the exchange.

Question I have is due to the time that it takes to do the actual exchange which I am assuming is driven by by UPDATE GLOBAL INDEXES statement as part of the exchange command.

Sample command:
alter table XPARTITION_TABLE
exchange partition CY2004_M4
with table X_TABLE_CY2004_M4 update global indexes;

If I wanted to exchange multiple months in one change window can I just run the exchange without the UPDATE GLOBAL INDEXES statement and include only on the very last partition I am exchanging?

For instance I would exchange Jan, Feb and Mar partitions but only include the UPDATE GLOBAL INDEXES on the very last exchange statement for the month of Mar.

The same question I have is related to a DROP PARTITION command. Could I drop many PARTITIONS and only include the UPDATE GLOBAL INDEXES on the last partition to be dropped during the change window on the production DB or is it required to have this statement in order to have indexes properly rebuilt?

Appreciate anyone's advice/time on the subject. Received on Fri Oct 13 2006 - 14:45:04 CDT