Re: UPDATE GLOBAL INDEXES

On Oct 18, 9:34 am, Vsevolod Afanassiev <vafanass..._at_yahoo.com> wrote:
> 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 non-
> partitioned tables?

They did. It's called 'alter index blah rebuild;' !!

The 'update global indexes' option only really achieves what a rebuild would. It's just that it does it during the table move rather than after it. But it's not doing some completely new magic.

The other way of looking at it is that you **need** the option for partitioned tables because rebuilding a thumping great index just because you moved a tiny piece of a table isn't exactly a good idea. But an unpartitioned table is either moved in its entirety or not at all, so you either rebuild the "global" index on it or not at all. You don't need an option that says, 'Let me move all this large table and prevent me from having to rebuild the entire index by taking steps which amount to rebuilding the entire index'. It just doesn't make sense. Received on Thu Oct 18 2007 - 00:04:09 CDT