Re: Is "alter table...drop partition...update global indexes" an online operation..?

From: <>
Date: Wed, 2 Apr 2008 20:21:15 -0700 (PDT)
Message-ID: <>

On Apr 2, 12:20 am, wrote:
> Oracle EE on RHEL 4
> We have a very large table which is partitioned by range and then
> further sub partitioned by list. All indexes have been created as
> locally partitioned but the primary key is a non-partitioned (global)
> index.
> When dropping an old partition I am planning on using the 'update
> global indexes' clause in order to avoid invalidating the primary key
> index and having to rebuild it.
> My question is... will the primary key index be useable while this
> operation is in progress...?
> What about the read consistency of the statement - its effectivly a
> DDL statement. Will users still be able to see index entries for the
> dropped rows up until the drop statement completes..?
> Can the table (and primary key index) be updated while the drop is
> occuring..?
> Thanks
> Matt

Yes - it's a safe online operation but can be slow. The index would be in usable state all along. I have not tested as to until what time the users can see the index entries. At our place we do it monthly as part of archiving, and ours is a high activity database.

-Madhu Sreeram Received on Wed Apr 02 2008 - 22:21:15 CDT

Original text of this message