Oracle concatenation of queries and function index usage

Question

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:

Everything OK here. Index is used.

Query 2:

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

Explain plan

Everything OK again. Index is used.

Query 3:

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

Explain plan

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

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.

Answer 1

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)