Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a problem with something I can't understand about indexes in Oracle 11g.

We can create test data with:

create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 varchar2(100)); 

create index test2_idx1 on test2(upper(field1)); 

create index test2_idx1b on test2(field1); 

create index test2_idx2 on test2(field3); 

  j NUMBER :=1;
  FOR i IN 1..500000
    INTO test2 
    (field1,field2, field3, field4)
    ('field1='||i,'a', j, '‌​i' );

    IF (i mod 1000)=0 THEN
       j := j+1;
    END IF;


Then I make some explain plans which result I can't understand

Query 1:

SELECT * FROM test2 WHERE field3=1;

Explain plan:

Explain plan for query 01

Everything OK here. Index is used.

Query 2:

SELECT * FROM test2 WHERE upper(field1)='FIELD1=1';

Explain plan

Explain plan for query 2

Everything OK again. Index is used.

Query 3:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE field1='FIELD1=1' OR field3=1;

Explain plan

Explain plan for query 3

Indenxes are used in concatenation. No problem again.

Query 4:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1;

Explain plan

Explain plan for query 4

Here my problem arises. Why is test2_idx1 not being used? Is it because it is a function index? Is there any workaround in this cases?

Thanks a lot in advance.

share|improve this question
up vote 5 down vote accepted

This seems to be a limitation of the optimizer (or perhaps a bug).

The plan for this query:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1
union all
SELECT * FROM test2 WHERE upper(field1)='FIELD1=1'
union all
SELECT * FROM test2 WHERE field3=1


shows that it is not the fault of bad stats, and also demonstrates a workaround using union all (but note you need something like upper(field1)='FIELD1=1 and (field3 is null or field3<>1) for an exactly equivalent query)

(My test was on 10.2)

share|improve this answer
It did use the index in 11gR2 (tested on the database behind Guess it was an enhancement. – Gary Jun 2 '11 at 23:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.