Understanding SKIP_UNUSABLE_INDEXES Initialization Parameter

Recently there was question on Technet discussion forum where the OP complained that the Optimizer is ignoring Index for a particular query:

Need to understand why optimizer unable to use index in case of "OR" whenn we set one partition index to unusable, the same query with between uses index.

He created a partitioned table with a local index and one on the partitioned index was set to UNUSABLE state. Later, when a query is executed with Optimizer refuses to use the index.

Let’s create the table and the index:

SQL>  CREATE TABLE t (
  2     id NUMBER NOT NULL,
  3     d DATE NOT NULL,
  4     n NUMBER NOT NULL,
  5     pad VARCHAR2(4000) NOT NULL
  6   )
  7   PARTITION BY RANGE (d) (
  8     PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
  9     PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
 10     PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
 11     PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
 12     PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
 13     PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
 14     PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
 15     PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
 16     PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
 17     PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
 18     PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
 19     PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
 20   );

Table created.

SQL>
SQL>  INSERT INTO t
  2   SELECT rownum, to_date('2009-01-01','yyyy-mm-dd')+rownum/274, mod(rownum,11), rpad('*',100,'*')
  3   FROM dual
  4   CONNECT BY level <= 100000;

100000 rows created.

SQL>
SQL>  CREATE INDEX i ON t (d) LOCAL;

Index created.

SQL>
SQL>  execute dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>

Now mark the last index partition as UNUSABLE.

SQL> ALTER INDEX i MODIFY PARTITION t_dec_2009 UNUSABLE;

Index altered.

Now execute the query in question:

SQL> set autotrace traceonly exp
SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 1473098910

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   135   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T    |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
              "D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "D"<=TO_DATE('
              2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D">=TO_DATE(' 2009-02-02
              01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>
SQL>

From the execution plan, it’s clear that the Optimizer is performing a FULL TABLE SCAN on the partitions in question (t_jan_2009 & t_feb_2009).

By default starting from Oracle 10g, SKIP_UNUSABLE_INDEXES initialization parameter is set to TRUE. This means, ignore index any index or index partition with UNUSABLE state. Turning this to FALSE enables index usage when possible. Index usage was ignored by the above query as one of the index partitions is in UNUSABLE state.

Let’s disable (FALSE) this parameter and run the same query:

SQL>
SQL> alter session set skip_unusable_indexes = false;

Session altered.

SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 3795917108

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     1 |     8 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE          |      |     1 |     8 |            |          |       |       |
|   2 |   CONCATENATION          |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|   5 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter(LNNVL("D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
              LNNVL("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss')))

SQL>
SQL> set autotrace off
SQL>

There you go; Optimizer is now picking the index. As we have disabled SKIP_UNUSABLE_INDEXES parameter at the session level, all the index partitions except the one in the UNUSABLE state will be used by the optimizer when appropriate.

When we try to access the last partition we will receive an error as shown below as the index is in UNUSABLE state.

SQL> set autotrace traceonly exp
SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-12-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );
SELECT count(d)
*
ERROR at line 1:
ORA-01502: index 'TEST.I' or partition of such index is in unusable state

SQL>

Enabling SKIP_UNUSABLE_INDEXES will cause the query to complete successfully instead of failing.

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-12-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 1473098910

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   135   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T    |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D">=TO_DATE(' 2009-12-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-12-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "D"<=TO_DATE('
              2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D">=TO_DATE(' 2009-02-02
              01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>

If you look at the 10053 trace when SKIP_UNUSABLE_INDEXES is set to TRUE, you will notice that the optimizer tends to ignore index access path and simply reports that the index is in UNUSABLE state. Below is an excerpt of 10053 trace.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T  (Using composite stats)
  (making adjustments for partition skews)
  ORIGINAL VALUES::    #Rows: 100000  #Blks:  2928  AvgRowLen:  116.00
  PARTITIONS::
  PRUNED: 2
  ANALYZED: 2  UNANALYZED: 0
    #Rows: 100000  #Blks:  488  AvgRowLen:  116.00
Index Stats::
  Index: I  Col#: 2
    USING COMPOSITE STATS
    LVLS: 1  #LB: 270  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 1696.00
    UNUSABLE
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): D(DATE)
    AvgLen: 8.00 NDV: 100000 Nulls: 0 Density: 1.0000e-005 Min: 2454833 Max: 2455198
  Table:  T  Alias: T     
    Card: Original: 100000  Rounded: 27  Computed: 26.84  Non Adjusted: 26.84
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  135.09  Resp: 135.09  Degree: 0
      Cost_io: 134.00  Cost_cpu: 8050409
      Resp_io: 134.00  Resp_cpu: 8050409
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 135.09  Degree: 1  Resp: 135.09  Card: 26.84  Bytes: 0