Re: Swapping partitions

Good point, should have touched that option.  I haven't used it yet but
did realize it now exists.

I would do some research before using this option on a large table
though.  Depending on your requirements you may have to perform this
online and have no other choice.  If the partition is say 10% of the
index size I would bet a invalidate and parallelized rebuild with lots
of sort area would accomplish the task much faster than a serial
exchange process.  I'm assuming their is no way to parallelize the
deletes from the index.

On Thu, 2004-12-02 at 10:50, Tim Gorman wrote:
> Kenny,
> 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...
> -Tim
> 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
> --
Kenny Payton
Software Architect
Public Records Group, Boca Raton
Choicepoint, Inc.
o: (770)752-4054
c: (561)926-4119

Other related posts: