Let Oracle transform OR-connected predicates into

Question

UNION and UNION ALL queries can outperform equivalent queries using OR-connected predicates under certain circumstances. To my knowledge, this is partially because UNION subselects can be executed in parallel and they can thus have their own "sub-plan" specific to each part of the OR-connected predicate, which is probably far more optimal due to simpler applicable query transformations.

But writing OR-connected predicates is usually much more readable and concise, even if subquery factoring were applied to a UNION ALL solution. My question is: Is there a way to indicate to Oracle, that a single, costly OR-connected predicate should be transformed into a UNION ALL operation? If there is such a hint/method, under what circumstances can it be applied (e.g. do any constraints need to be present on the columns involved in the predicates, etc)? An example:

CREATE TABLE a AS
  SELECT 1 x, 2 y FROM DUAL UNION ALL
  SELECT 2 x, 1 y FROM DUAL;

-- This query...
SELECT * FROM a
WHERE x = 1 OR y = 1

-- Is sometimes outperformed by this one, for more complex table sources...
-- Note: in my case, I can safely apply UNION ALL. I know the two predicates to
-- be mutually exclusive.
SELECT * FROM a
WHERE x = 1
UNION ALL
SELECT * FROM a
WHERE y = 1

Note, I'm aware of the /*+ USE_CONCAT */ hint:

SELECT /*+ USE_CONCAT */ * FROM a
WHERE x = 1 OR y = 1

But it doesn't seem to produce what I need (no forced UNION ALL operation in the execution plan):

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| A    |      2 |
-------------------------------------------

Maybe, there is some restriction to this hint? I have Oracle 11g2 available for this.

Answer 1

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
)

Answer 2

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.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 x, y, z from test where (floor(z) = 1 and x = 1)
union all
select 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.test
where (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 all
select 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?