Why my distinct query is not using index?

This question "I am running a distinct query on a column which is having index, but my distinct query is not using the index", was asked by one of my friend. Answer was pretty simple. With this post I am trying to answer it with some examples

Without going in much details lets test this scenario, by creating a table SOME_DATA, adding an INDEX on VAL column and run a DISTINCT query

SQL> create table some_data as
  2  select trunc(dbms_random.value(1,100)) val, a.* from dba_objects a;
Table created.

SQL> desc some_data
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VAL                                                NUMBER
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> create index some_data_val_indx on some_data(val);
Index created.

SQL> exec dbms_stats.gather_table_stats(null, 'SOME_DATA', cascade=>true);
PL/SQL procedure successfully completed.

SQL> set autot trace
SQL> select distinct val from some_data;
99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 443606636

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    99 |   297 |    77   (2)| 00:00:01 |
|   1 |  HASH UNIQUE       |           |    99 |   297 |    77   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| SOME_DATA | 18864 | 56592 |    76   (0)| 00:00:01 |
--------------------------------------------------------------------------------

As we can see that table "SOME_DATA" was FULL TABLE SCANNED to get the results, even if we were having INDEX on the column. Let me try it with INDEX HINT.

SQL> select /*+ index(some_data) */ distinct val from some_data;
99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 443606636

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    99 |   297 |    77   (2)| 00:00:01 |
|   1 |  HASH UNIQUE       |           |    99 |   297 |    77   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| SOME_DATA | 18864 | 56592 |    76   (0)| 00:00:01 |
--------------------------------------------------------------------------------

As you can see that table "SOME_DATA" was FULL TABLE SCANNED to get the results, even if even if we provided the INDEX HINT. Why? Answer is pretty simple, it is because B*Tree INDEX does not store NULL values. So Oracle can't be sure to find distinct values just by scanning an Index.

So, if my column is NOT NULL, will Oracle use INDEX? Lets see

SQL> alter table some_data modify(val not null);
Table altered.

SQL> SQL> select distinct val from some_data;
99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1329759908
--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |    99 |   297 |    48   (8)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT   |                    |    99 |   297 |    48   (8)| 00:00:01 |
|   2 |   INDEX FULL SCAN     | SOME_DATA_VAL_INDX | 18864 | 56592 |    44   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Now as we can see here that after modifying the column NOT NULL, Oracle was able to perform INDEX FULL SCAN. Actually I was hoping that Oracle would do a INDEX FAST FULL SCAN, it usually do by itself in such cases. Just to add here, if we are sure that all the columns of a table in query can be fetched by index only, prefer INDEX FAST FULL SCAN to have multi-block read. Lets try by adding INDEX_FSS hint.

SQL> select /*+ index_ffs(some_data) */ distinct val from some_data;
99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1329759908
--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |    99 |   297 |    13   (8)| 00:00:01 |
|   1 |  HASH UNIQUE          |                    |    99 |   297 |    13   (8)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| SOME_DATA_VAL_INDX | 18864 | 56592 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Great, We can observe huge improvement by reduction of cost when Oracle perfomed INDEX FAST FULL SCAN (multiblock read). Now what if we can not modify our column to NOT NULL and still want to use INDEX, we simply need to add predicate to have NOT NULL in query as

SQL> alter table some_data modify (val null);
Table altered.

SQL> select /*+ index_ffs(some_data) */ distinct val from some_data where val is not null;
99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1329759908
--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |    99 |   297 |    13   (8)| 00:00:01 |
|   1 |  HASH UNIQUE          |                    |    99 |   297 |    13   (8)| 00:00:01 |
|*  2 |   INDEX FAST FULL SCAN| SOME_DATA_VAL_INDX | 18864 | 56592 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Summary:
1) B*Tree INDEX does not store NULLs
2) Distinct query on nullable column can't use INDEX
3) To use INDEX with DISTINCT query on nullable column, add predicate to have NOT NULL values and INDEX HINT if needed
4) Distinct query on NOT NULL column can use INDEX, check plan and add INDEX_FFS HINT if needed
5) If an INDEX have all the columns in query/table, take advantage of multiblock read [INDEX_FFS]

Please do post your feedback.

Related Posts:
- Why Primary Key Foreign Key Relationship and Join Elimination
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Query optimization tips for Oracle
- Avoiding unnecessary function calls to optimize SQL statements
- Why do I prefer COALESCE over NVL
- PL/SQL Tuning: Bulk Collect with Dynamic SQL
- PL/SQL Tuning: Bind Variables and execute immediate
- Reduce database calls by posting Multiple Records from Application to Database