Why Query is not using the Index?

This post answers the following specific question:

                Why “distinct query” is not using the index (in some cases)?

Let us try the following test case:

Create sample table “t_distinct” and add an index to the “ID” column and collect statistics for table and index:

1

1_1

2

13

Let us run the following basic query:

3

It is clear that optimizer selected a Full Table Scan to retrieve the results, although there is an index on the ID column.

If we repeat the same query with the “Index” hint:

4

Again, the optimizer is still using full table scan to access the table and ignored the Hint!

What is the reason behind the optimizer choice of Full Table Scan?

The reason is that the B*Tree Index by nature doesn’t store the NULL values, so, Oracle Optimizer assumes that not all distinct values could be retrieved from scanning the index only.

So, what if the “ID” column is NOT NULL, will the optimized change his mind? Let us try:

5

6

Now, the Oracle Optimizer is able to use the index.

If you omit the Index hint:

7

Note that “Index FAST FULL SCAN” is using multi-block read instead of single-block sequential read, which is much faster.

Note the big difference in the cost (and consistent gets) between the three cases:

Access Method Cost Consistent Gets
TABLE FULL ACCESS 1736 481
INDEX FULL SCAN 242 323
INDEX FAST FULL SCAN 63 230

 

If you cannot add a not null constraint to the table, you may achieve the same result by adding a not null predicate to the query, as follows:

alter table t_distinct modify(id null);

Now if you tried to select from the table, you will get the old full table scan:

8

But, when you add a not null query predicate:

9

Note that for all the following queries, the optimizer will choose FULL TABLE SCAN, although there is an index and the id column is not null:

101112

This is ok, as the index  doesn’t contain all columns, and the query references more than 5% of the table rows, so full table scan will be more efficient.

Reference:

http://nimishgarg.blogspot.com.eg/2016/05/why-my-distinct-query-is-not-using-index.html

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s