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.)
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.
When you create an index in the UNUSABLE state, it is ignored by the optimizer and is not maintained by DML. An unusable index must be rebuilt, or dropped and re-created, before it can be used.
If the index is partitioned, then all index partitions are marked UNUSABLE.
Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index.
The following procedure illustrates how to create unusable indexes and query the database for details about the index.
To create an unusable index:
If necessary, create the table to be indexed.
For example, create a hash-partitioned table called hr.employees_part as follows:
sh@PROD> CONNECT hr
Enter password: **
Connected.
hr@PROD> CREATE TABLE employees_part
2 PARTITION BY HASH (employee_id) PARTITIONS 2
3 AS SELECT * FROM employees;
Table created.
hr@PROD> SELECT COUNT(*) FROM employees_part;
COUNT(*)
———-
107
Create an index with the keyword UNUSABLE.
The following example creates a locally partitioned index on employees_part, naming the index partitions p1_i_emp_ename and p2_i_emp_ename, and makingp1_i_emp_ename unusable:
hr@PROD> CREATE INDEX i_emp_ename ON employees_part (employee_id)
2 LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename);
Index created.
UNUSABLE: Must be rebuild manually
UNUSABLE must be REBUILT, not recreated!!!