Re: Swapping partitions

[Ken Payton <ken.payton@xxxxxxxxxxxxxxxxxx>]

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
> 
> --
> http://www.freelists.org/webpage/oracle-l
-- 
Kenny Payton
Software Architect
Public Records Group, Boca Raton
Choicepoint, Inc.
ken.payton@xxxxxxxxxxxxxxxxxx
o: (770)752-4054
c: (561)926-4119
--
http://www.freelists.org/webpage/oracle-l

• References:
  • Re: Swapping partitions
    • From: Tim Gorman

Other related posts:

Swapping partitions

Re: Swapping partitions

Re: Swapping partitions

Re: Swapping partitions

Re: Swapping partitions

Re: Swapping partitions

Re: Swapping partitions