Unusable indexes

• ·       An unusable index is ignored by the optimizer and is not maintained by DML.
• ·       One reason to make an index unusable is to improve bulk load performance. (Bulk loads go more quickly if the database does not need to maintain indexes when inserting rows.)
• ·       Instead of dropping the index and later re-creating it, which requires you to recall the exact parameters of the CREATE INDEX statement, you can make the index unusable, and then rebuild it.

•  ·       An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used.

• ·       Truncating a table makes an unusable index valid.
• ·       When you make an existing index unusable, its index segment is dropped.
• ·       If the index is partitioned, then all index partitions are marked UNUSABLE

 Creating an Unusable Index

Create a 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

       PARTITION BY HASH (employee_id)

       PARTITIONS 2

      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 making p1_i_emp_ename unusable:

    hr@PROD> CREATE INDEX i_emp_ename ON employees_part (employee_id)

     LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename);

     Index created.

Verify that the index is unusable by querying the data dictionary.

     The following example queries the status of index i_emp_ename and its two partitions, showing that only partition p2_i_emp_ename is unusable:

    hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS

FROM   USER_INDEXES

WHERE INDEX_NAME = 'I_EMP_ENAME'

UNION ALL

SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS

FROM   USER_IND_PARTITIONS

WHERE PARTITION_NAME LIKE '%I_EMP_ENAME%';

    

    INDEX OR PARTITION NAME        STATUS

    --------------------------------------   ------------------------

    I_EMP_ENAME                           N/A

    P1_I_EMP_ENAME                 UNUSABLE

    P2_I_EMP_ENAME                 USABLE

    Query the data dictionary to determine whether storage exists for the partitions.

     The following query shows that only index partition p2_i_emp_ename occupies a segment. Because you created p1_i_emp_ename as unusable, the database did not allocate a segment for it.

    hr@PROD> COL PARTITION_NAME FORMAT a14

    hr@PROD> COL SEG_CREATED FORMAT a11

    hr@PROD> SELECT p.PARTITION_NAME, p.STATUS AS "PART_STATUS",

                         p.SEGMENT_CREATED AS "SEG_CREATED",  

                        FROM   USER_IND_PARTITIONS p, USER_SEGMENTS s

                        WHERE s.SEGMENT_NAME = 'I_EMP_ENAME';

    

    PARTITION_NAME      PART_STATUS    SEG_CREATED

    -------------------------- ----------------------   --------------------------

    P2_I_EMP_ENAME     USABLE               YES      

    P1_I_EMP_ENAME     UNUSABLE         NO

To make an index unusable:

    Query the data dictionary to determine whether an existing index or index partition is usable or unusable.

    hr@PROD> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED

      FROM   USER_INDEXES

     UNION ALL

    SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED

    FROM   USER_IND_PARTITIONS;

    

    INDEX OR PART NAME             STATUS  SEG

    ------------------------------ -------- ---------------------

    I_EMP_ENAME                           N/A      N/A

    JHIST_EMP_ID_ST_DATE_PK  VALID    YES

    JHIST_JOB_IX                              VALID    YES

    JHIST_EMPLOYEE_IX                 VALID    YES

    JHIST_DEPARTMENT_IX            VALID    YES

    EMP_EMAIL_UK                         VALID    NO

    .

    .

    .

    COUNTRY_C_ID_PK                   VALID    YES

    REG_ID_PK                                  VALID    YES

    P2_I_EMP_ENAME                     USABLE   YES

    P1_I_EMP_ENAME                    UNUSABLE NO

    

    22 rows selected.

    The preceding output shows that only index partition p1_i_emp_ename is unusable.

    Make an index or index partition unusable by specifying the UNUSABLE keyword.

    The following example makes index emp_email_uk unusable:

    hr@PROD> ALTER INDEX emp_email_uk UNUSABLE;

    

    Index altered.

    The following example makes index partition p2_i_emp_ename unusable:

    hr@PROD> ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;

    

    Index altered.

    Query the data dictionary to verify the status change.

    For example, issue the following query

    hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS,  SEGMENT_CREATED 

                            FROM   USER_INDEXES

                             UNION ALL

SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS, SEGMENT_CREATED

                             FROM   USER_IND_PARTITIONS;

    

    INDEX OR PARTITION NAME        STATUS   SEG

    ------------------------------ -------- ----------------------------

    I_EMP_ENAME                                         N/A      N/A

    JHIST_EMP_ID_ST_DATE_PK                 VALID    YES

    JHIST_JOB_IX                                             VALID    YES

    JHIST_EMPLOYEE_IX                               VALID    YES

    JHIST_DEPARTMENT_IX                           VALID    YES

    EMP_EMAIL_UK                         UNUSABLE         NO

    .

    .

    .

    COUNTRY_C_ID_PK                                 VALID    YES

    REG_ID_PK                                                VALID    YES

    P2_I_EMP_ENAME                    UNUSABLE         NO

    P1_I_EMP_ENAME                    UNUSABLE         NO

    

    22 rows selected.

    A query of space consumed by the i_emp_ename and emp_email_uk segments shows that the segments no longer exist:

    hr@PROD> SELECT SEGMENT_NAME, BYTES

     FROM   USER_SEGMENTS

      WHERE  SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');