Why Isn’t My Index Getting Used?-Indexes-2

Case 4
We have indexed a character column. This column contains only numeric data. We query using the following syntax:
SQL> select * from t where indexed_column = 5

Note that the number 5 in the query is the constant number 5 (not a character string). The index on INDEXED_COLUMN is not used. This is because the preceding query is the same as the following:
SQL> select * from t where to_number(indexed_column) = 5

We have implicitly applied a function to the column, and, as noted in case 3, this will preclude the use of the index. This is very easy to see with a small example. In this example, we’re going to use the built-in package DBMS_XPLAN:
$ sqlplus eoda/foo@PDB1
SQL > create table t ( x char(1) constraint t_pk primary key, y date ); Table created.

As you can see, it full scanned the table. And even if we were to hint the following query, it uses the index, but not for a UNIQUE SCAN as we might expect—it is FULL SCANNING this index:
SQL> explain plan for select /*+ INDEX(t t_pk) */ * from t where x = 5; Explained.
SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT

The reason lies in the last line of output there: filter(TO_NUMBER(“X”)=5). There is an implicit function being applied to the database column. The character string stored in X must be converted to a number prior to comparing to the value 5. We cannot convert 5 to a string, since our NLS settings control what 5 might look like in a string (it is not deterministic), so we convert the string into a number, and that precludes the use of the index to rapidly find this row. If we simply compare strings to stringswe get the expected INDEX UNIQUE SCAN, and we can see the function is not being applied. You should always avoid implicit conversions anyway. Always compare apples to apples and oranges to oranges. Another case where this comes up frequently is with dates. We try to query
— find all records for today
select * from t where trunc(date_col) = trunc(sysdate);and discover that the index on DATE_COL will not be used. We can either index the TRUNC(DATE_COL) or, perhaps more easily, query using range comparison operators. The following demonstrates the use of greater than and less than on a date. Once we realize that the condition
TRUNC(DATE_COL) = TRUNC(SYSDATE)is the same as the condition
SQL> select *from twhere date_col >=trunc(sysdate)and date_col < trunc(sysdate+1)

this moves all of the functions to the right-hand side of the equation, allowing us to use the index on DATE_COL (and provides the same result as WHERE TRUNC(DATE_COL) = TRUNC(SYSDATE)).

If possible, you should always remove the functions from database columns when they are in the predicate. Not only will doing so allow for more indexes to be considered for use, but it will also reduce the amount of processing the database needs to do. In the preceding case, when we used
where date_col >=trunc(sysdate)and date_col < trunc(sysdate+1)
the TRUNC values are computed once for the query, and then an index could be used to find just the qualifying values. When we used TRUNC(DATE_COL) = TRUNC(SYSDATE), the TRUNC(DATE_COL) had to be evaluated once per row for every row in the entire table (no indexes).

Case 5
The index, if used, would actually be slower. I see this often—people assume that, of course, an index will always make a query go faster. So, they set up a small table, analyze it, and find that the optimizer doesn’t use the index. The optimizer is doing exactly the right thing in this case. Oracle (under the CBO) will use an index only when it makes sense to do so. Consider this example:
$ sqlplus eoda/foo@PDB1
SQL> create table t(x int);Table created.
SQL> insert into t select rownum from dual connect by level < 1000000; 999999 rows created. SQL> create index ti on t(x);Index created.

it will happily use the index; however, we’ll find that when the estimated number of rows to be retrieved via the index crosses a threshold (which varies depending on various optimizer settings, physical statistics, version, and so on), we’ll start to observe a full table scan:
SQL> select count() from t where x < 1000000;COUNT()

This example shows the optimizer won’t always use an index, and, in fact, it makes the right choice in skipping indexes. While tuning your queries, if you discover that an index isn’t used when you think it ought to be, don’t just force it to be used—test and prove first that the index is indeed faster (via elapsed and I/O counts) before overruling the CBO. Reason it out.

Case 6
There aren’t fresh statistics for tables. The tables used to be small, but now when we look at them, they have grown quite large. An index will now make sense, whereas it didn’t originally. If we generate statistics for the table, it will use the index.

Without correct statistics, the CBO cannot make the correct decisions.