Let Oracle transform OR-connected predicates into UNION ALL operations

I believe this may have something to do with indexes existing on the columns you use in the OR predicate.

I tested using the following in 11gR2.

create table scott.test as select level l,        decode(mod(level,2), 1, 1, 2) x,        decode(mod(level,2), 1, 2, 1) y,        dbms_random.value(1, 3) z from dual connect by level < 1000;/begin   dbms_stats.gather_table_stats('scott', 'test');end;/

I then explained the following queries in TOAD, (EXPLAIN PLAN FOR)

select x, y, z from scott.test    where (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1)    ;SELECT STATEMENT Optimizer Mode=ALL_ROWS        10          4                                  TABLE ACCESS FULL COS_DM.TEST 10      280     4   select /*+ USE_CONCAT */ x, y, z from scott.testwhere (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1);SELECT STATEMENT Optimizer Mode=ALL_ROWS        10          4                                  TABLE ACCESS FULL COS_DM.TEST 10      280     4                                select x, y, z from test where (floor(z) = 1 and x = 1)union allselect x, y, z from test where (floor(z) = 2 and y = 1);SELECT STATEMENT Optimizer Mode=ALL_ROWS        10          8                                  UNION-ALL                                                  TABLE ACCESS FULL   COS_DM.TEST 5   140     4                                    TABLE ACCESS FULL   COS_DM.TEST 5   140     4                                

So it appears the hint's not working. I then added an index to the x & y columns:

create index test_x on test (x, y);begin   dbms_stats.gather_table_stats('scott', 'test');end;/

Rerunning the queries now:

select x, y, z from scott.test    where (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1)    ;SELECT STATEMENT Optimizer Mode=ALL_ROWS        10          4                                  TABLE ACCESS FULL COS_DM.TEST 10      280     4   select /*+ USE_CONCAT */ x, y, z from scott.testwhere (floor(z) = 1 and x = 1) or (floor(z) = 2 and y = 1);SELECT STATEMENT Optimizer Mode=ALL_ROWS        10          8                                  CONCATENATION                                                  TABLE ACCESS FULL   COS_DM.TEST 5   140     4                                    TABLE ACCESS FULL   COS_DM.TEST 5   140     4                                select x, y, z from test where (floor(z) = 1 and x = 1)union allselect x, y, z from test where (floor(z) = 2 and y = 1);SELECT STATEMENT Optimizer Mode=ALL_ROWS        10          8                                  UNION-ALL                                                  TABLE ACCESS FULL   COS_DM.TEST 5   140     4                                    TABLE ACCESS FULL   COS_DM.TEST 5   140     4                                

It appears that after adding the index (even though it's not being used) the optimizer decided to use the hint after all!

Perhaps you could try this?

In some cases, the optimizer ignore the hints, and you have to write the query in different way. The alternative to rewrite all query using UNION ALL, you can rewrite only the clause:

SELECT * FROM a /* ... you can put here more joins with many tables ... */WHERE a.rowid in (    select innerQry.rowid from a innerQry where /*your first clause of OR*/innerQry.x = 1    union all    select innerQry.rowid from a innerQry where /*your second clause of OR*/innerQry.y = 1)