Database DataWarehousing (MOSC)

MOSC Banner

Rebuilding a partition with local primary key index and 'UPDATE INDEXES' sets partition index to unu

edited Jan 21, 2013 7:00PM in Database DataWarehousing (MOSC) 1 commentAnswered
I am running Oracle 11.2.0.1 and am trying to rebuild a partition into another tablespace with HCC compression.  I am using a statement like this:

alter table FOO.PARTITIONED_TABLE move partition LP_NOV_2009 COMPRESS FOR QUERY HIGH tablespace DATA_L_C parallel 6 update indexes;

Both the table partitions and index partitions are around 20G.  The documentation says that Oracle will not mark the index unusable with the 'update indexes' clause.  What I am seeing is that It stays usable while the table segment is rebuilt, but then Oracle simply rebuilds the index partition, setting it unusable while it is rebuilt.  If anything is running in the database against the table, it starts doing (a lot!) of partition scans.  Is there some parameter that I'm missing?  Or is this simply how it works?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center