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

On Apr 2, 12:20 am, mc..._at_hotmail.com wrote:
> Oracle 10.2.0.1 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