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