Re: Swapping partitions

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 02 Dec 2004 08:50:14 -0700


Slight correction:

The clause "[ INVALIDATE | UPDATE ] GLOBAL INDEXES" clause on the partition
management variations of the ALTER TABLE command provides some flexibility.

Introduced with Oracle9i, the default is INVALIDATE GLOBAL INDEXES, which
was the (unavoidable) behavior in Oracle8 and Oracle8i.

Using the UPDATE GLOBAL INDEXES clause causes the ALTER TABLE partition
maintenance operation (i.e. [ EXCHANGE | MOVE | SPLIT | MERGE | TRUNCATE |
DROP ] PARTITION, etc) to transactionally maintain the affected entries in
the global index.  While this makes the ALTER TABLE command complete much
more slowly, it also avoids the need for a complete rebuild of the global
index, something that become infeasible as things get larger and larger...

Hope this helps...


on 12/2/04 8:04 AM, Ken Payton at ken.payton@xxxxxxxxxxxxxxxxxx wrote:

> You can use the partition exchange clause if you are not using global
> indexes.  If you are using global indexes you would need to rebuild
> them.
> precreate empty exact matching table, with indexes.
> alter table exchange partition partition_name with table table_name
> including indexes without validation;
> You could then use the same command to exchange the resulting table into
> a partitioned archiving table.
> Kenny


Other related posts: