Unused indexes are a major topic for Oracle DBAs, since each index a table has increases the maintenance “cost” of inserts, updates and deletes. Many a blog post has been written providing ways to find such ‘unused’ indexes, many relying on a relatively newer Oracle feature, invisible indexes. Two other articles discuss this, one an effort to help the DBA discover unused indexes and the other discusses an issue where those invisible indexes aren’t totally invisible. Neither one shows what could happen when an index, thought to be unused and dropped, really wasn’t. [Invisible indexes were not used in this example, the indexes were simiply dropped based upon the execution plan generated.] Depending on the release of Oracle in use the behaviour can be slightly different; we’ll look at 11.2.0.4 and 12.1.0.2 and show the behavior of each. The example used is modified from one provided by Jonathan Lewis and it illustrates the point quite well. Let’s get this party started.
The example creates three tables, populates them, creates indexes and then performs a join between them. Looking at the original execution plan provides the basis for the index evaluation and eventual drop. The queries run afterwards illustrate that the dropped index WAS being used by the optimizer even though it wasn’t mentioned in the plan. Proof of this will be recorded by the 10053 traces generated for each session.
Using Oracle 11.2.0.4 the following results are obtained:
SQL> alter session set tracefile_identifier='index_sanity';
Session altered.
SQL> alter session set events='10053 trace name context forever, level 2';
Session altered.
SQL>
SQL> select
2 t1.small_vc, t2.small_vc, t3.small_vc
3 from
4 t1, t2, t3
5 where
6 t1.n1 between 40 and 50
7 and t2.id1 = t1.id1
8 and t2.ind_pad = t1.ind_pad
9 and t2.id2 = t1.id2
10 and t3.id = t1.id1
11 ;
SMALL_VC SMALL_VC SMALL_VC
---------- ---------- ----------
0000003941 0000000001 1
0000003841 0000000001 1
0000003741 0000000001 1
0000003641 0000000001 1
0000003541 0000000001 1
0000003441 0000000001 1
0000003341 0000000001 1
...
0000000749 0000000009 9
0000000649 0000000009 9
0000000549 0000000009 9
0000000449 0000000009 9
0000000349 0000000009 9
0000000249 0000000009 9
0000000149 0000000009 9
0000000049 0000000009 9
360 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1184213596
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 484 | 64856 | 481 (1)| 00:00:06 |
|* 1 | HASH JOIN | | 484 | 64856 | 481 (1)| 00:00:06 |
|* 2 | HASH JOIN | | 484 | 57596 | 27 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 20 | 1160 | 7 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 484 | 29524 | 20 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 5000 | 75000 | 454 (1)| 00:00:06 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ID"="T1"."ID1")
2 - access("T2"."ID1"="T1"."ID1" AND "T2"."IND_PAD"="T1"."IND_PAD"
AND "T2"."ID2"="T1"."ID2")
4 - filter("T1"."N1"<=50 AND "T1"."N1">=40)
SQL>
SQL> drop index t1_i1;
Index dropped.
SQL>
SQL> accept X prompt "Press return to continue"
Press return to continue
SQL>
SQL> select
2 t1.small_vc, t2.small_vc, t3.small_vc
3 from
4 t1, t2, t3
5 where
6 t1.n1 between 40 and 50
7 and t2.id1 = t1.id1
8 and t2.ind_pad = t1.ind_pad
9 and t2.id2 = t1.id2
10 and t3.id = t1.id1
11 ;
SMALL_VC SMALL_VC SMALL_VC
---------- ---------- ----------
0000000041 0000000001 1
0000000042 0000000002 2
0000000043 0000000003 3
0000000044 0000000004 4
0000000045 0000000005 5
0000000046 0000000006 6
...
0000003942 0000000002 2
0000003943 0000000003 3
0000003944 0000000004 4
0000003945 0000000005 5
0000003946 0000000006 6
0000003947 0000000007 7
0000003948 0000000008 8
0000003949 0000000009 9
360 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2290830436
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 6968 | 79 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 52 | 6188 | 27 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 20 | 1160 | 7 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 484 | 29524 | 20 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T3_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 15 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."ID1"="T1"."ID1" AND "T2"."IND_PAD"="T1"."IND_PAD" AND
"T2"."ID2"="T1"."ID2")
5 - filter("T1"."N1"<=50 AND "T1"."N1">=40)
6 - access("T3"."ID"="T1"."ID1")
SQL>
SQL> accept X prompt "Press return to continue"
Press return to continue
SQL>
SQL> create index t1_i1 on t1(id1, ind_pad, id2) pctfree 91;
Index created.
SQL>
SQL> drop index t2_i1;
Index dropped.
SQL>
SQL> select
2 t1.small_vc, t2.small_vc, t3.small_vc
3 from
4 t1, t2, t3
5 where
6 t1.n1 between 40 and 50
7 and t2.id1 = t1.id1
8 and t2.ind_pad = t1.ind_pad
9 and t2.id2 = t1.id2
10 and t3.id = t1.id1
11 ;
SMALL_VC SMALL_VC SMALL_VC
---------- ---------- ----------
0000000041 0000000001 1
0000000042 0000000002 2
0000000043 0000000003 3
0000000044 0000000004 4
0000000045 0000000005 5
0000000046 0000000006 6
0000000047 0000000007 7
...
0000003942 0000000002 2
0000003943 0000000003 3
0000003944 0000000004 4
0000003945 0000000005 5
0000003946 0000000006 6
0000003947 0000000007 7
0000003948 0000000008 8
0000003949 0000000009 9
360 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2290830436
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 6968 | 79 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 52 | 6188 | 27 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 20 | 1160 | 7 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 484 | 29524 | 20 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T3_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 15 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."ID1"="T1"."ID1" AND "T2"."IND_PAD"="T1"."IND_PAD" AND
"T2"."ID2"="T1"."ID2")
5 - filter("T1"."N1"<=50 AND "T1"."N1">=40)
6 - access("T3"."ID"="T1"."ID1")
SQL>
SQL> alter session set events='10053 trace name context off';
Session altered.
The 10053 trace file from the 11.2.0.4 run shows this for the first query:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T3 Alias: T3
#Rows: 5000 #Blks: 1667 AvgRowLen: 116.00 ChainCnt: 0.00
Column (#1): ID(
AvgLen: 4 NDV: 5000 Nulls: 0 Density: 0.000200 Min: 1 Max: 5000
Index Stats::
Index: T3_PK Col#: 1
LVLS: 1 #LB: 10 #DK: 5000 LB/K: 1.00 DB/K: 1.00 CLUF: 1667.00
***********************
Table Stats::
Table: T2 Alias: T2
#Rows: 20 #Blks: 20 AvgRowLen: 262.00 ChainCnt: 0.00
Column (#1): ID1(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 0 Max: 9
Column (#3): IND_PAD(
AvgLen: 41 NDV: 1 Nulls: 0 Density: 1.000000
Column (#2): ID2(
AvgLen: 3 NDV: 20 Nulls: 0 Density: 0.050000 Min: 0 Max: 19
Index Stats::
Index: T2_I1 Col#: 1 3 2
LVLS: 1 #LB: 3 #DK: 20 LB/K: 1.00 DB/K: 1.00 CLUF: 20.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 4000 #Blks: 66 AvgRowLen: 112.00 ChainCnt: 0.00
Column (#1): ID1(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 0 Max: 9
Column (#3): IND_PAD(
AvgLen: 41 NDV: 1 Nulls: 0 Density: 1.000000
Column (#2): ID2(
AvgLen: 3 NDV: 20 Nulls: 0 Density: 0.050000 Min: 0 Max: 19
Index Stats::
Index: T1_I1 Col#: 1 3 2
LVLS: 2 #LB: 443 #DK: 20 LB/K: 22.00 DB/K: 66.00 CLUF: 1320.00
***************************************
Even though the optimizer elected to use full table scans to access the data the index statistics were used to make that decision. For the second query (after the index drop) the optimizer reports these statistics:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T3 Alias: T3
#Rows: 5000 #Blks: 1667 AvgRowLen: 116.00 ChainCnt: 0.00
Column (#1): ID(
AvgLen: 4 NDV: 5000 Nulls: 0 Density: 0.000200 Min: 1 Max: 5000
Index Stats::
Index: T3_PK Col#: 1
LVLS: 1 #LB: 10 #DK: 5000 LB/K: 1.00 DB/K: 1.00 CLUF: 1667.00
***********************
Table Stats::
Table: T2 Alias: T2
#Rows: 20 #Blks: 20 AvgRowLen: 262.00 ChainCnt: 0.00
Column (#1): ID1(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 0 Max: 9
Column (#3): IND_PAD(
AvgLen: 41 NDV: 1 Nulls: 0 Density: 1.000000
Column (#2): ID2(
AvgLen: 3 NDV: 20 Nulls: 0 Density: 0.050000 Min: 0 Max: 19
Index Stats::
Index: T2_I1 Col#: 1 3 2
LVLS: 1 #LB: 3 #DK: 20 LB/K: 1.00 DB/K: 1.00 CLUF: 20.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 4000 #Blks: 66 AvgRowLen: 112.00 ChainCnt: 0.00
Column (#1): ID1(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 0 Max: 9
Column (#3): IND_PAD(
AvgLen: 41 NDV: 1 Nulls: 0 Density: 1.000000
Column (#2): ID2(
AvgLen: 3 NDV: 20 Nulls: 0 Density: 0.050000 Min: 0 Max: 19
***************************************
This now causes the index on table T3 to ‘kick in’. The interesting point in this example is that dropping the index on table T1 or T2 (keeping the other index in place) produces the same plan.
Using Oracle 12.1.0.2 similar behavior results:
SQL> alter session set tracefile_identifier='index_sanity';
Session altered.
SQL> alter session set events='10053 trace name context forever, level 2';
Session altered.
SQL>
SQL> select
2 t1.small_vc, t2.small_vc, t3.small_vc
3 from
4 t1, t2, t3
5 where
6 t1.n1 between 40 and 50
7 and t2.id1 = t1.id1
8 and t2.ind_pad = t1.ind_pad
9 and t2.id2 = t1.id2
10 and t3.id = t1.id1
11 ;
SMALL_VC SMALL_VC SMALL_VC
---------- ---------- ----------
0000000041 0000000001 1
0000000042 0000000002 2
0000000043 0000000003 3
0000000044 0000000004 4
0000000045 0000000005 5
0000000046 0000000006 6
...
0000003942 0000000002 2
0000003943 0000000003 3
0000003944 0000000004 4
0000003945 0000000005 5
0000003946 0000000006 6
0000003947 0000000007 7
0000003948 0000000008 8
0000003949 0000000009 9
360 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1184213596
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 484 | 64856 | 481 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 484 | 64856 | 481 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 484 | 57596 | 27 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 20 | 1160 | 7 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 484 | 29524 | 20 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 5000 | 75000 | 454 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ID"="T1"."ID1")
2 - access("T2"."ID1"="T1"."ID1" AND "T2"."IND_PAD"="T1"."IND_PAD"
AND "T2"."ID2"="T1"."ID2")
4 - filter("T1"."N1"<=50 AND "T1"."N1">=40)
Note
-----
- this is an adaptive plan
SQL>
SQL> drop index t1_i1;
Index dropped.
SQL>
SQL> accept X prompt "Press return to continue"
Press return to continue
SQL>
SQL> select
2 t1.small_vc, t2.small_vc, t3.small_vc
3 from
4 t1, t2, t3
5 where
6 t1.n1 between 40 and 50
7 and t2.id1 = t1.id1
8 and t2.ind_pad = t1.ind_pad
9 and t2.id2 = t1.id2
10 and t3.id = t1.id1
11 ;
SMALL_VC SMALL_VC SMALL_VC
---------- ---------- ----------
0000000041 0000000001 1
0000000042 0000000002 2
0000000043 0000000003 3
0000000044 0000000004 4
0000000045 0000000005 5
0000000046 0000000006 6
...
0000003942 0000000002 2
0000003943 0000000003 3
0000003944 0000000004 4
0000003945 0000000005 5
0000003946 0000000006 6
0000003947 0000000007 7
0000003948 0000000008 8
0000003949 0000000009 9
360 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2290830436
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 6968 | 79 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 52 | 6188 | 27 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 20 | 1160 | 7 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 484 | 29524 | 20 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T3_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 15 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."ID1"="T1"."ID1" AND "T2"."IND_PAD"="T1"."IND_PAD" AND
"T2"."ID2"="T1"."ID2")
5 - filter("T1"."N1"<=50 AND "T1"."N1">=40)
6 - access("T3"."ID"="T1"."ID1")
Note
-----
- this is an adaptive plan
SQL>
SQL> accept X prompt "Press return to continue"
Press return to continue
SQL>
SQL> create index t1_i1 on t1(id1, ind_pad, id2) pctfree 91;
Index created.
SQL>
SQL> drop index t2_i1;
Index dropped.
SQL>
SQL> select
2 t1.small_vc, t2.small_vc, t3.small_vc
3 from
4 t1, t2, t3
5 where
6 t1.n1 between 40 and 50
7 and t2.id1 = t1.id1
8 and t2.ind_pad = t1.ind_pad
9 and t2.id2 = t1.id2
10 and t3.id = t1.id1
11 ;
SMALL_VC SMALL_VC SMALL_VC
---------- ---------- ----------
0000000041 0000000001 1
0000000042 0000000002 2
0000000043 0000000003 3
0000000044 0000000004 4
0000000045 0000000005 5
0000000046 0000000006 6
...
0000003942 0000000002 2
0000003943 0000000003 3
0000003944 0000000004 4
0000003945 0000000005 5
0000003946 0000000006 6
0000003947 0000000007 7
0000003948 0000000008 8
0000003949 0000000009 9
360 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2290830436
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 6968 | 79 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 52 | 6968 | 79 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 52 | 6188 | 27 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 20 | 1160 | 7 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 484 | 29524 | 20 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T3_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 15 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."ID1"="T1"."ID1" AND "T2"."IND_PAD"="T1"."IND_PAD" AND
"T2"."ID2"="T1"."ID2")
5 - filter("T1"."N1"<=50 AND "T1"."N1">=40)
6 - access("T3"."ID"="T1"."ID1")
Note
-----
- this is an adaptive plan
SQL>
SQL> alter session set events='10053 trace name context off';
Session altered.
The trace file for the 12.1.0.2 session reports the same data as the trace from 11.2.0.4 so that won’t be provided here.
Thinking an index is unused and proving it are two different things, and as can be seen from this example it’s not an easy task without using a 10053 trace to see exactly what statistics the optimizer is using to make its decisions. Given the small volume of data in this example it’s hard to say which is the better plan; larger volumes of data would be required to make that determination. This example does prove, however, that the execution plan alone cannot be relied upon to provide all of the required information to determine if an index is or is not used.
There can be many ways to try to determine if an index is being used or not; execution plans, using invisible indexes (with caution), using a 10053 trace to view the optimizer decision tree although that last option can be a bit tedious depending on the index configuration for the table or tables in question. It is worth the time invested to ensure that what you think is happening is actually happening, so there won’t be any surprises, for the DBA or the users, later.
