Let Oracle transform OR-connected predicates into UNION ALL operations

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

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?