Examine the following output: SQL SELECT index_name,status FROM dba_indexes WHERE status='UNUSABLE'; INDEX_NAME STATUS --------------- --------------- EIND UNUSABLE Which two statements about the above index are true? (Choose two.)

Question

Examine the following output:

SQL> SELECT index_name,status FROM dba_indexes WHERE status='UNUSABLE'; INDEX_NAME STATUS --------------- --------------- EIND UNUSABLE Which two statements about the above index are true? (Choose two.)

Accepted Answer

A. It is ignored by the query optimizer. Oracle's query optimizer completely ignores indexes in UNUSABLE status when generating execution plans, meaning full table scans or other access paths will be chosen instead - this is a deliberate design to prevent errors from corrupted or incomplete index structures. B. It is not used while the index is being rebuilt. When an index rebuild operation is in progress, the index remains in UNUSABLE status and is therefore not consulted by the optimizer or DML operations until the rebuild completes and the status returns to VALID.

Answer

C. The index cannot be rebuilt, and has to be re-created. An UNUSABLE index can be repaired by issuing ALTER INDEX index_name REBUILD - dropping and re-creating it is not required.

Answer

D. The index is automatically rebuilt when used the next time. Oracle does not have any mechanism to automatically rebuild indexes; an UNUSABLE index will remain in that state indefinitely until a DBA explicitly issues a rebuild command.

Examine the following output: SQL> SELECT index_name,status FROM dba_indexes WHERE status='UNUSABLE'; INDEX_NAME STATUS --------------- --------------- EIND UNUSABLE Which two statements about the…

Question

Options

How the community answered

Why each option

Topics

Community Discussion