Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted August 11, 2016

Index Sanity in Oracle

By David Fitzjarrell

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.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM