UNION with WHERE clause

Question

In my experience, Oracle is very good at pushing simple predicates around. The following test was made on Oracle 11.2. I’m fairly certain it produces the same execution plan on all releases of 10g as well.

(Please people, feel free to leave a comment if you run an earlier version and tried the following)

create table table1(a number, b number);
create table table2(a number, b number);

explain plan for
select *
  from (select a,b from table1
        union 
        select a,b from table2
       )
 where a > 1;

select * 
  from table(dbms_xplan.display(format=>'basic +predicate'));

PLAN_TABLE_OUTPUT
---------------------------------------
| Id  | Operation            | Name   |
---------------------------------------
|   0 | SELECT STATEMENT     |        |
|   1 |  VIEW                |        |
|   2 |   SORT UNIQUE        |        |
|   3 |    UNION-ALL         |        |
|*  4 |     TABLE ACCESS FULL| TABLE1 |
|*  5 |     TABLE ACCESS FULL| TABLE2 |
---------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------    
   4 - filter("A">1)
   5 - filter("A">1)

As you can see at steps (4,5), the predicate is pushed down and applied before the sort (union).

I couldn’t get the optimizer to push down an entire sub query such as

 where a = (select max(a) from empty_table)

or a join. With proper PK/FK constraints in place it might be possible, but clearly there are limitations 🙂

Leave a Comment Cancel reply