Take the 2-minute tour ×
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. It's 100% free, no registration required.

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); 

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

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


EXEC DBMS_STATS.GATHER_TABLE_STATS ('system', 'test2');

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

1 Answer 1

up vote 4 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
);

explain

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 apex.oracle.com). Guess it was an enhancement. –  Gary Jun 2 '11 at 23:55

Your Answer

 
discard

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.