Re: Is "alter table...drop partition...update global indexes" an online operation..?
Date: Wed, 2 Apr 2008 20:21:15 -0700 (PDT)
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)
> 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
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