Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> UPDATE GLOBAL INDEXES

UPDATE GLOBAL INDEXES

From: Vsevolod Afanassiev <vafanassiev_at_yahoo.com>
Date: Wed, 17 Oct 2007 16:34:53 -0700
Message-ID: <1192664093.740996.191500@z24g2000prh.googlegroups.com>


It is interesting that global and *non-partitioned* indexes can be kept valid during operations
such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause while similar option isn't available for non-partitioned table or for partitioned table with local indexes.

For example:
1. Create a partitioned table with non-partitioned index. Execute ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in VALID state when ALTER TABLE is running. 2. Create a non-partitioned table and an index on it. There is no way to keep the
index VALID during ALTER TABLE MOVE operation. 3. Create a partitioned table and a local index on this table. There is no way to keep partition
of this index VALID during ALTER TABLE MOVE PARTITION operation.

So a partitioned table with a single partition is more flexible than a non-partitioned table: this single partition can be moved ONLINE, with all indexes maintained.
It may be necessary to compress this table, or to rebuild table after purge,
or to relocate it to a different tablespace.

Why Oracle didn't make this functionality available for nonpartitioned  tables? Received on Wed Oct 17 2007 - 18:34:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US