Re: Unusable Index problem

In article <38853D7A.9D04F357_at_citec.com.au>, Jerome Chik <jerome.chik_at_citec.com.au> writes:

>Hi,
>
>I'm wondering what would cause an index to be unusable and how do I
>avoid this?
>
>It seems to happen after I do a data load with sql*loader using a direct
>path method. Once the data is loaded, certain indexes become unusable.
>
>Using a 8.1.5 database.
>
>Thanks,
>
>Jerome.
>

Indexes Left in Index Unusable State
SQL*Loader will leave indexes in Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.

Any SQL statement that tries to use an index that is in Index Unusable state returns an error. The following conditions cause the direct path option to leave an index or a partition of a partitioned index in Index Unusable state:

SQL*Loader runs out of space for the index, and cannot update the index.

The data is not in the order specified by the SORTED INDEXES clause.

There is an instance failure, or the Oracle shadow process fails while building the index.

There are duplicate keys in a unique index.

Data save points are being used, and the load fails or is terminated via a keyboard interrupt after a data save point occurred.

To determine if an index is in Index Unusable state, you can execute a simple query:

SELECT INDEX_NAME, STATUS
   FROM USER_INDEXES
   WHERE TABLE_NAME = 'tablename';

To determine if an index partition is in unusable state,

SELECT INDEX_NAME,

       PARTITION_NAME,
       STATUS FROM USER_IND_PARTITIONS
       WHERE STATUS != 'VALID';

If you are not the owner of the table, then search ALL_INDEXES or DBA_INDEXES instead of USER_INDEXES. For partitioned indexes, search ALL_IND_PARTITIONS and DBA_IND_PARTITIONS instead of USER_IND_PARTITIONS.

Paul in VT Received on Thu Jan 27 2000 - 12:21:28 CST