On Oct 18, 3:04 pm, hjr.pyth..._at_gmail.com wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
I want to be able to move a non-partitioned table and have indexes
maintained in the process.
Yes, there is 'alter index rebuild online' statement but there is no
'alter table move online' statement.
Example where it would be helpful: one of my applications creates 3
new tables every day.
The tables get populated within 24-48 hours after creation and then
remain nearly
static for the next 100 days, then they get dropped. In order to
reduce space usage
we compress tables after 30 days by doing 'alter table move compress',
but it makes indexes unusable, and they have to be rebuild. So we need
an outage to rebuild the indexes.
If these tables were created a partitioned tables with singe partition
we would be able to do it
without outage.
Received on Thu Oct 18 2007 - 01:02:19 CDT
