Examine the output:
SQL> SELECT index_name, status FROM dba_indexes WHEREstatus=’UNUSABLE’;
INDEX_NAME STATUS
——————— —————–
EIND UNUSABLE
Which two statements about this index are true?
A.
It is ignored by the query optimizer.
B.
It is not used while the index is being rebuilt.
C.
The index cannot be rebuilt, and has to be re-created.
D.
The index is automatically rebuilt when used the next time.
Explanation:
* Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is
marked as ‘unusable’ with an ALTER INDEX command. A direct path load against a table or partition will alsoleave its indexes unusable.
Queries and other operations agains a table with unuable indexes will generate errors:
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
* The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:
Indexes:
SELECT ‘alter index ‘||index_name||’ rebuild tablespace ‘||tablespace_name ||’;’
FROM dba_indexes
WHERE status = ‘UNUSABLE’;