Why my index is not used?

Well, this question keeps popping up now and then. Yesterday, one of my colleagues also came up with this question: "Why is it that Oracle is not using index even though I am selecting less than 10% of data?".

We ran the query with autotrace enabled and the execution plan showed a Full Table Scan. This table contains over 29 Million records and by adding the predicate, result set is reduced to 2.3 Million records, which is 8% of total records.

SQL> set autotrace traceonly exp
SQL> SELECT *
  2      FROM quint_sec_tbl
  3      WHERE quint_type = 'XX06FR';

Execution Plan
----------------------------------------------------------
Plan hash value: 3917650069

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |  3440 |   618K|   454K  (1)| 01:30:55 |
|*  1 |  TABLE ACCESS FULL| QUINT_SEC_TBL      |  3440 |   618K|   454K  (1)| 01:30:55 |
----------------------------------------------------------------------------------------

Cary Millsap's very old (but still valid) article "When to Use an Index" greatly helped me in this regard. This article unveils that Index consideration is based on block selectivity and not on row selectivity.

He also defines what Row Selectivity and Block Selectivity are in the article.

Row Selectivity:

You can define the row selectivity of a given where-clause predicate as the number of rows returned by the predicate (r) divided by the total number of rows in the table (R):

        P(r) =  r / R

Block Selectivity:

You can define the block selectivity of a given where-clause predicate analogously, as the number of data blocks containing at least one row matching the predicate condition (b) divided by the total number of data blocks below the high-water mark (B):

        P(b) = b / B

We can calculate block selectivity and row selectivity using SQL provided in this article. I used this SQL against my query and following are the results:

SQL> @hds
TableOwner : MYPRODUSER
TableName : QUINT_SEC_TBL
ColumnList : QUINT_TYPE
WhereClause: 
PageSize : 100

Table blocks below hwm    Table rows
         (B)                 (R)
---------------------- ----------------
             1,672,704       29,270,757
More: 

       Block selectivity  Block count    Row selectivity     Row count
QUINT_    (pb = b/B)          (b)          (pr = r/R)           (r)
------ ----------------- -------------- ----------------- ----------------
TT34DV            45.03%        753,277            37.99%       11,120,869
FG76SC            44.47%        743,788            13.67%        4,000,205
LH23Q2            42.78%        715,558             9.44%        2,762,284
XX06FR            42.32%        707,894             8.02%        2,346,846
:
:
:

Output of this SQL is sorted in descending order of block selectivity.

Looking at this output, row selectivity is only 8% but to fetch these 8% of rows Oracle has to visit 42% of blocks (block selectivity). That means, nearly half of the table's blocks contain at least one row for which QUINT_TYPE='XX06FR'. Instead of going through the hard path of Index Access, it’s more efficient for the optimizer to do a Full Table Scan.

So, now we know why the index was ignored and a Full Table Scan was preferred.

P.S.: Due to security reasons Username, Table name, column name and column values are modified.