Let Oracle transform OR-connected predicates into UNION ALL operations

optimization, oracle, sql, union, union-all

Solution

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?

Problem

`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.

Original source