# 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)`