Oracle concatenation of queries and function index usage
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&lt;&gt;1) for an exactly equivalent query)

(My test was on 10.2)