Back in some version of Oracle 10 the session modifiable parameter skip_unuable_indexes changed its default value from FALSE to TRUE. This meant that operations on tables with an unusable index did not fail with an error if Oracle arrived at an execution plan that utilised the index. For all intents and purposes Oracle ignores that the index is present… well, nearly ignores them as we will see shortly.
First of all, let’s take a quick look of what skip_unusable_indexes does using a 1,000,000 row table named LOTS_OF_ROWS:
SQL>CREATE TABLE lots_of_rows
2 (id NUMBER (10) NOT NULL
3 ,cat NUMBER (4) NOT NULL
4 ,padding CHAR(30) NOT NULL)
5 /
Table created.
SQL>ALTER TABLE lots_of_rows
2 ADD CONSTRAINT lots_of_rows_pk
3 PRIMARY KEY (id)
4 /
Table altered.
SQL>CREATE INDEX lots_of_rows_ix1
2 ON lots_of_rows (cat)
3 /
Index created.
SQL>INSERT INTO lots_of_rows
2 WITH row_src AS
3 (SELECT ROWNUM r FROM dual CONNECT BY ROWNUM <= 1000)
4 SELECT ROWNUM
5 , MOD(ROWNUM,1000)
6 , 'X'
7 FROM row_src r2
8 , row_src r2
9 /
1000000 rows created.
SQL>COMMIT
2 /
Commit complete.
SQL>EXEC dbms_stats.gather_table_stats ('','lots_of_rows')
PL/SQL procedure successfully completed.
SQL>ALTER SESSION SET skip_unusable_indexes = TRUE
2 /
Session altered.
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT *
2 FROM lots_of_rows
3 WHERE cat = 42
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3680026695
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 40000 | 1005 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LOTS_OF_ROWS | 1000 | 40000 | 1005 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LOTS_OF_ROWS_IX1 | 1000 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CAT"=42)
SQL>SET AUTOTRACE OFF
In the above we created a table, LOTS_OF_ROWS, with a non-unique index on the CAT column, which has 1,000 distinct values, and populated it with 1,000,000 rows. We then ran a query selecting out the rows with a CAT value of 42. Not surprisingly Oracle elected to use the index when running the index.
Now let’s see what happens if we make the index unusable and rerun the query:
SQL>ALTER INDEX lots_of_rows_ix1 UNUSABLE
2 /
Index altered.
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SELECT *
2 FROM lots_of_rows
3 WHERE cat = 42
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3711500201
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 40000 | 1687 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LOTS_OF_ROWS | 1000 | 40000 | 1687 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CAT"=42)
SQL>SET AUTOTRACE OFF
The previously generated execution plan for our query, using the index, clearly could not be used but Oracle did not fail the query. Instead Oracle came up with a new plan, using a table scan. Perhaps not the best of plans but our query did run without error.
Now let’s see what happens if SKIP_UNUSABLE_INDEXES is set to FALSE:
SQL>ALTER SESSION SET skip_unusable_indexes = FALSE 2 / Session altered. SQL>SET AUTOTRACE TRACEONLY EXPLAIN SQL>SELECT * 2 FROM lots_of_rows 3 WHERE cat = 42 4 / SELECT * * ERROR at line 1: ORA-01502: index 'DEVELOPER.LOTS_OF_ROWS_IX1' or partition of such index is in unusable state SQL>SET AUTOTRACE OFF
Not surprisingly, with the SKIP_UNUSABLE_INDEXES set to FALSE Oracle failed the query.
However, can ORA-01502 be throw even if SKIP_UNUSABLE_INDEXES is set to TRUE? The answer is yes so let’s look at how this might happen:
SQL>ALTER SESSION SET skip_unusable_indexes = TRUE 2 / Session altered. SQL>SET AUTOTRACE TRACEONLY EXPLAIN SQL>SELECT /*+ index (lots_of_rows lots_of_rows_ix1) */ 2 * 3 FROM lots_of_rows 4 WHERE cat = 42 5 / SELECT /*+ index (lots_of_rows lots_of_rows_ix1) */ * ERROR at line 1: ORA-01502: index 'DEVELOPER.LOTS_OF_ROWS_IX1' or partition of such index is in unusable state SQL>SET AUTOTRACE OFF
In the above example, we explicitly hinted the query to use the unusable index. In this situation Oracle will attempt to abide by the hint and fail the statement when it finds the index is unusable. The also applies using the variation of the INDEX hint that specifies the column name, rather than the index name:
SQL>SET AUTOTRACE TRACEONLY EXPLAIN SQL>SELECT /*+ index (lots_of_rows (cat)) */ 2 * 3 FROM lots_of_rows 4 WHERE cat = 42 5 / SELECT /*+ index (lots_of_rows (cat)) */ * ERROR at line 1: ORA-01502: index 'DEVELOPER.LOTS_OF_ROWS_IX1' or partition of such index is in unusable state SQL>SET AUTOTRACE OFF
To summarise this post then:
- When the SKIP_UNUSABLE_INDEXES parameter is set to TRUE it allows Oracle to ignore any indexes in an unusable state, and this the standard default setting
- Skipping disabled indexes can lead to poor execution plans, which should not be surprising since those indexes were created for a reason
- Queries specifically hinted to use an index will not ignore the unusable index and will throw an ORA-01502 error
Mark Hoxey 