Re: Swapping partitions
- From: Ken Payton <ken.payton@xxxxxxxxxxxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Thu, 02 Dec 2004 11:41:39 -0500
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
>
> --
> //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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
