UNION with WHERE clause

Question

I'm doing a UNION of two queries on an Oracle database. Both of them have a WHERE clause. Is there a difference in the performance if I do the WHERE after UNIONing the queries compared to performing the UNION after WHERE clause?

For example:

SELECT colA, colB FROM tableA WHERE colA > 1 UNION SELECT colA, colB FROM tableB WHERE colA > 1 

compared to:

SELECT *    FROM (SELECT colA, colB FROM tableA         UNION         SELECT colA, colB FROM tableB)   WHERE colA > 1 

I believe in the second case, it performs a full table scan on both the tables affecting the performance. Is that correct?

Answer 1

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 :)