oracle alter index unusable,可能导致index unusable的维护操作

Six types of maintenance operations

can mark index partitions INDEX UNUSABLE.

In all cases, you must rebuild the index partitions when the

operation is

complete.

1) Operations like Import Partition or conventional

path SQL*Loader

that offer an option to bypass local index

maintenance. When the

Import is complete, the affected local index partitions are

marked

IU.

2) Direct path SQL*Loader leaves affected local

index partitions and

global indexes in an IU state if the index is out of date

with

respect to the data that it indexes. The index

can be out of date

for the following reasons:

a) The index was not maintained during the load

due to a space

management error (for example, out of extents ORA-1653 or

ORA-1652).

b) The user requested the SKIP_INDEX_MAINTENANCE

clause.

3) Partition maintenance operations like ALTER TABLE

MOVE PARTITION

that change rowids. These operations mark the

affected local index

partition and all global index partitions IU.

4) Partition maintenance operations like ALTER TABLE

TRUNCATE PARTITION

or DROP PARTITION that remove rows from the

table. These operations

mark the affected local index partition and all global index

partitions IU.

5) Partition maintenance operations like ALTER TABLE

SPLIT PARTITION

that modify the partition definition of local indexes but do

not

automatically rebuild the index data to match the new

definitions.

These operations mark the affected local index partitions

IU. ALTER

TABLE SPLIT PARTITION also marks all global index partitions

IU

because it results in changes to rowids.

6) Index maintenance operations like ALTER INDEX

SPLIT 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 are

created

USABLE. If the partition that was split was

marked IU, then so are

the partitions resulting from the split. Note

that dropping a

partition of a global index that is either IU or is not empty

causes

the next partition of the index to become IU.

The ALTER INDEX REBUILD PARTITION statement can be used to

regenerate 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 all

partitions

in the index.

To prevent indexes to become unusable during

partition maintenance operations, you can use 'update global

indexes' clause in 9.2,

'update global indexes' or 'update indexes' clauses in 10g and

above. For more details please consult the SQL Reference at ALTER

TABLE for more details. Also, there are certain scenarios where the

indexes are not marked unusable, e.g. DROP, TRUNCATE partition

normally marks global indexes unusable, but not when the partition

to 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 the

resulting partition, and the other partition remains empty).