Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Thu Dec 02 2004 - 11:10:06 CST
![]() |
![]() |