Six types of maintenance operationscan mark index partitions INDEX UNUSABLE.
In all cases, you must rebuild the index partitions when theoperation is
1) Operations like Import Partition or conventionalpath SQL*Loader
that offer an option to bypass local indexmaintenance. When the
Import is complete, the affected local index partitions aremarked
2) Direct path SQL*Loader leaves affected localindex partitions and
global indexes in an IU state if the index is out of datewith
respect to the data that it indexes. The indexcan be out of date
for the following reasons:
a) The index was not maintained during the loaddue to a space
management error (for example, out of extents ORA-1653 or
b) The user requested the SKIP_INDEX_MAINTENANCEclause.
3) Partition maintenance operations like ALTER TABLEMOVE PARTITION
that change rowids. These operations mark theaffected local index
partition and all global index partitions IU.
4) Partition maintenance operations like ALTER TABLETRUNCATE PARTITION
or DROP PARTITION that remove rows from thetable. These operations
mark the affected local index partition and all global index
5) Partition maintenance operations like ALTER TABLESPLIT PARTITION
that modify the partition definition of local indexes but donot
automatically rebuild the index data to match the newdefinitions.
These operations mark the affected local index partitionsIU. ALTER
TABLE SPLIT PARTITION also marks all global index partitionsIU
because it results in changes to rowids.
6) Index maintenance operations like ALTER INDEXSPLIT PARTITION that
modify the partitioning definition of the index but do not
automatically rebuild the affected partitions. These operations
mark the affected index partitions IU. However,if you split a
USABLE partition of a global index, resulting partitions arecreated
USABLE. If the partition that was split wasmarked IU, then so are
the partitions resulting from the split. Notethat dropping a
partition of a global index that is either IU or is not emptycauses
the next partition of the index to become IU.
The ALTER INDEX REBUILD PARTITION statement can be used toregenerate a single
partition in a local or global partitioned index. This saves you from having
to perform DROP INDEX and then CREATE INDEX, which would affect allpartitions
in the index.
To prevent indexes to become unusable duringpartition maintenance operations, you can use 'update globalindexes' clause in 9.2,
'update global indexes' or 'update indexes' clauses in 10g andabove. For more details please consult the SQL Reference at ALTERTABLE for more details. Also, there are certain scenarios where theindexes are not marked unusable, e.g. DROP, TRUNCATE partitionnormally
marks global indexes unusable, but not when the partitionto be dropped/truncated is empty. Similar with SPLIT partition,when the split results in adding a new empty partition to the table(all the rows of the partition to be split goes into one of theresulting
partition, and the other partition remains empty).