Why Oracle Is Not Using Your Index

Six common reasons Oracle does a full scan despite an index, each shown in a real plan with the change that makes the index work.

An index exists on the column, yet the execution plan shows TABLE ACCESS FULL. Usually the optimizer is right: the way the query is written makes the index unusable, or reading the rows through the index would cost more than scanning the table. The plan itself tells you which, through its predicates and row estimates. This guide reproduces the six most common causes on one table, shows the plan for each, and the change that lets the index work, or the reason it should not.

Sample Table

50,000 customers with an index on EMAIL, PHONE, STATUS, REFERRER_ID, and a composite index on LAST_NAME and FIRST_NAME:

Example:

create table demo_customers (
  customer_id number constraint demo_customers_pk primary key,
  email       varchar2(60) not null,
  phone       varchar2(15) not null,
  last_name   varchar2(30) not null,
  first_name  varchar2(30) not null,
  status      varchar2(10) not null,
  referrer_id number);

insert into demo_customers
select level,
       'user' || level || '@' || decode(mod(level, 2), 0, 'example.com', 'example.org'),
       to_char(5550000000 + level),
       'Name' || mod(level, 2000),
       'First' || mod(level, 997),
       case when mod(level, 10) = 0 then 'CLOSED' else 'ACTIVE' end,
       case when mod(level, 5000) = 0 then null else mod(level, 500) + 1 end
from   dual connect by level <= 50000;
commit;

create index demo_cust_email_ix    on demo_customers (email);
create index demo_cust_phone_ix    on demo_customers (phone);
create index demo_cust_name_ix     on demo_customers (last_name, first_name);
create index demo_cust_status_ix   on demo_customers (status);
create index demo_cust_referrer_ix on demo_customers (referrer_id);
exec dbms_stats.gather_table_stats(user, 'DEMO_CUSTOMERS')

Output:

Table DEMO_CUSTOMERS created.

50,000 rows inserted.

Commit complete.

Index DEMO_CUST_EMAIL_IX created.

Index DEMO_CUST_PHONE_IX created.

Index DEMO_CUST_NAME_IX created.

Index DEMO_CUST_STATUS_IX created.

Index DEMO_CUST_REFERRER_IX created.

PL/SQL procedure successfully completed.

1. A Function on the Indexed Column

Example:

explain plan for
select * from demo_customers where upper(email) = 'USER42@EXAMPLE.COM';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
____________________________________________________________________
Plan hash value: 811847660

-----------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   500 |   136   (0)|
|*  1 |  TABLE ACCESS FULL| DEMO_CUSTOMERS |   500 |   136   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("EMAIL")='USER42@EXAMPLE.COM')

13 rows selected.

The index holds EMAIL values, but the query asks for UPPER(EMAIL), which the index does not contain. Oracle has to compute UPPER for every row, so it scans the table. A function-based index stores the computed values:

Example:

create index demo_cust_email_upper_ix on demo_customers (upper(email));

explain plan for
select * from demo_customers where upper(email) = 'USER42@EXAMPLE.COM';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Index DEMO_CUST_EMAIL_UPPER_IX created.

Explained.

PLAN_TABLE_OUTPUT
________________________________________________________________________________________________
Plan hash value: 4098601935

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |     1 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_CUSTOMERS           |     1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | DEMO_CUST_EMAIL_UPPER_IX |     1 |     1   (0)|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("EMAIL")='USER42@EXAMPLE.COM')

14 rows selected.

The cost falls from 136 to 2. The same applies to TRUNC on dates, SUBSTR, arithmetic such as amount * 1.1, and any other expression on the column.

2. Implicit Data Type Conversion

PHONE is a VARCHAR2 column, compared here with a number:

Example:

explain plan for
select * from demo_customers where phone = 5550000042;
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
____________________________________________________________________
Plan hash value: 811847660

-----------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |   136   (0)|
|*  1 |  TABLE ACCESS FULL| DEMO_CUSTOMERS |     1 |   136   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("PHONE")=5550000042)

13 rows selected.

The predicate shows the hidden function: TO_NUMBER("PHONE"). When a string column is compared with a number, Oracle converts the column, not the number, and a converted column cannot use its index. Compare with a value of the column's own type:

Example:

explain plan for
select * from demo_customers where phone = '5550000042';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
__________________________________________________________________________________________
Plan hash value: 892898470

---------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_CUSTOMERS     |     1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | DEMO_CUST_PHONE_IX |     1 |     1   (0)|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PHONE"='5550000042')

14 rows selected.

The same happens with bind variables of the wrong type from an application, which is why it often goes unnoticed: check the predicates for TO_NUMBER, TO_CHAR, or INTERNAL_FUNCTION.

3. LIKE with a Leading Wildcard

Example:

explain plan for
select * from demo_customers where email like '%42@example.com';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

explain plan for
select * from demo_customers where email like 'user42@%';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
____________________________________________________________________
Plan hash value: 811847660

-----------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |  2500 |   136   (0)|
|*  1 |  TABLE ACCESS FULL| DEMO_CUSTOMERS |  2500 |   136   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMAIL" LIKE '%42@example.com')

13 rows selected.

Explained.

PLAN_TABLE_OUTPUT
__________________________________________________________________________________________
Plan hash value: 408745217

---------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_CUSTOMERS     |     1 |     3   (0)|
|*  2 |   INDEX RANGE SCAN                  | DEMO_CUST_EMAIL_IX |     1 |     2   (0)|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMAIL" LIKE 'user42@%')
       filter("EMAIL" LIKE 'user42@%')

15 rows selected.

An index is sorted from the first character on, so it can find values that start with 'user42@', but not values that end with something. A pattern that starts with % needs a full scan. For searches inside text, Oracle Text indexes are the right tool.

4. The Leading Column of a Composite Index Is Missing

Example:

explain plan for
select * from demo_customers where first_name = 'First42';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

explain plan for
select * from demo_customers where last_name = 'Name42' and first_name = 'First42';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
____________________________________________________________________
Plan hash value: 811847660

-----------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |    50 |   136   (0)|
|*  1 |  TABLE ACCESS FULL| DEMO_CUSTOMERS |    50 |   136   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FIRST_NAME"='First42')

13 rows selected.

Explained.

PLAN_TABLE_OUTPUT
_________________________________________________________________________________________
Plan hash value: 2997405403

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_CUSTOMERS    |     1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | DEMO_CUST_NAME_IX |     1 |     1   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_NAME"='Name42' AND "FIRST_NAME"='First42')

14 rows selected.

DEMO_CUST_NAME_IX is sorted by LAST_NAME first. A search on FIRST_NAME alone would have to look under each of the 2,000 last names; a skip scan does that only when the leading column has few distinct values, so here the optimizer scans the table. With LAST_NAME in the query, the index works. Otherwise, create an index that starts with FIRST_NAME, or change the column order to match the most common queries.

5. The Query Returns Too Many Rows

Example:

explain plan for
select * from demo_customers where status = 'ACTIVE';
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
____________________________________________________________________
Plan hash value: 811847660

-----------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                | 25000 |   136   (0)|
|*  1 |  TABLE ACCESS FULL| DEMO_CUSTOMERS | 25000 |   136   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("STATUS"='ACTIVE')

13 rows selected.

STATUS has two values, so the optimizer expects half of the table, 25,000 rows; in fact nine in ten customers are ACTIVE. Reading that many rows one by one through the index would cost far more than one full scan, so the index is ignored on purpose. An index on a column with few values helps only for the rare value, and only when the optimizer knows it is rare, which needs a histogram.

6. Searching for NULL

Example:

explain plan for
select * from demo_customers where referrer_id is null;
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Explained.

PLAN_TABLE_OUTPUT
____________________________________________________________________
Plan hash value: 811847660

-----------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |    10 |   136   (0)|
|*  1 |  TABLE ACCESS FULL| DEMO_CUSTOMERS |    10 |   136   (0)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("REFERRER_ID" IS NULL)

13 rows selected.

Only 10 customers have no referrer, and the optimizer knows it, yet it scans the table. A B-tree index does not store entries whose indexed columns are all NULL, so DEMO_CUST_REFERRER_IX has no entry for these rows. Adding a constant as a second column makes every row appear in the index:

Example:

create index demo_cust_referrer_null_ix on demo_customers (referrer_id, 0);

explain plan for
select * from demo_customers where referrer_id is null;
select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');

Output:

Index DEMO_CUST_REFERRER_NULL_IX created.

Explained.

PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________
Plan hash value: 316965343

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |    10 |    12   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_CUSTOMERS             |    10 |    12   (0)|
|*  2 |   INDEX RANGE SCAN                  | DEMO_CUST_REFERRER_NULL_IX |    10 |     2   (0)|
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("REFERRER_ID" IS NULL)

14 rows selected.

The entry (NULL, 0) is not all NULL, so it is indexed, and IS NULL becomes an access predicate with a cost of 12 instead of 136.

Things to Know

  • Read the Predicate Information first: a filter on the indexed column, or a function wrapped around it, explains most cases.
  • Check the estimated rows: when they are a large share of the table, the full scan is the better plan, and forcing the index makes things worse.
  • Missing or stale statistics lead to wrong estimates and wrong choices; gather them before blaming the index.
  • An index made invisible is ignored by the optimizer unless the parameter OPTIMIZER_USE_INVISIBLE_INDEXES is TRUE.

Related Guides

Conclusion

Oracle skips an index when the query hides the column inside a function or a type conversion, starts a LIKE pattern with %, omits the leading column of a composite index, looks for NULL in a single-column index, or returns too many rows. The plan's predicates and row estimates show which case you have; fix the query or the index for the first four, and accept the full scan for the last.

Tags
Vinish Kapoor
Vinish Kapoor

An Oracle ACE, author of four books on Oracle APEX, SQL and PL/SQL, and Oracle Forms, and a software developer building Oracle database applications since 2001.

guest

0 Comments
Oldest
Newest Most Voted