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_at_choicepointprg.net 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_at_choicepointprg.net
o: (770)752-4054
c: (561)926-4119
--
http://www.freelists.org/webpage/oracle-l