Re: UPDATE GLOBAL INDEXES

Vsevolod Afanassiev wrote:
>
> 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.

dbms_redefinition

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------