Function Based indexes not used in query with 'OR' condition (Doc ID 281483.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.2 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Symptoms

Function Based indexes are not used in query with 'OR' condition
Same query uses the index when the index is conventional.

Testcase
============
create table tab1 (id number , name varchar2(10)) ;
create index ix_normal on tab1(name) ;

create table tab2 (id number , name varchar2(10)) ;
create index ix_func on tab2(upper(name)) ;

insert into tab1 values (1, 'A') ;
insert into tab2 values (1, 'A') ;

analyze table tab1 compute statistics ;
analyze index ix_normal compute statistics ;
analyze table tab2 compute statistics ;
analyze index ix_func compute statistics ;

select /*+ USE_CONCAT INDEX(tab1 ix_normal)*/ * from tab1 where name = 'A' or name = 'B';

Explain Plan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6 | 4 |
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 3 | 2 |
|* 3 | INDEX RANGE SCAN | IX_NORMAL | 1 | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 3 | 2 |
|* 5 | INDEX RANGE SCAN | IX_NORMAL | 1 | | 1 |
----------------------------------------------------------------------------

select * from tab2 where upper(name) = 'A' ;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 3 | 2 |
|* 2 | INDEX RANGE SCAN | IX_FUNC | 1 | | 1 |
---------------------------------------------------------------------------


select /*+ USE_CONCAT INDEX(tab2 ix_func)*/ * from tab2 where upper(name) = 'A' or upper(name) = 'B';

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6 | 4 |
| 1 | CONCATENATION | | | | |
|* 2 | TABLE ACCESS FULL | TAB2 | 1 | 3 | 2 |
|* 3 | TABLE ACCESS FULL | TAB2 | 1 | 3 | 2 |
--------------------------------------------------------------------

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms