Menu
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?
315
Using the Where Clause With the UNION OperatorWe can use the WHERE clause in either one or both of the SELECT statements to filter out the rows being combined. We can also use the WHERE clause in only one of the SELECT statements in the UNION.
If you want to apply the WHERE clause to the result of the UNION, then you have to embed the UNION in the FROM clause: SELECT * FROM (SELECT * FROM TableA UNION SELECT * FROM TableB ) AS U WHERE U.
The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use this UNION clause, each SELECT statement must have. The same number of columns selected. The same number of column expressions.
In SQL, the UNION operator selects rows from two or more tables. If rows of tables are the same, those rows are only included once in the result set. For example, SELECT age FROM Teachers UNION SELECT age FROM Students; Run Code.
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 :)
180
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
