Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 UNION ing 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?
                Get the explain plan and prove your belief. And after that run a test and time them in your environment and see which one wins.
– Kuberchaun
                Mar 25, 2011 at 20:06
                For such a simple query, there's likely no difference because Oracle would likely push the predicate (WHERE clause) into the derived table/inline view.
– OMG Ponies
                Mar 26, 2011 at 0:08
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 :)
NOTE: While my advice was true many years ago, Oracle's optimizer has improved so that the location of the where definitely no longer matters here.  However preferring UNION ALL vs UNION will always be true, and portable SQL should avoid depending on optimizations that may not be in all databases.
Short answer, you want the WHERE before the UNION and you want to use UNION ALL if at all possible.  If you are using UNION ALL then check the EXPLAIN output, Oracle might be smart enough to optimize the WHERE condition if it is left after.
The reason is the following.  The definition of a UNION says that if there are duplicates in the two data sets, they have to be removed.  Therefore there is an implicit GROUP BY in that operation, which tends to be slow.  Worse yet, Oracle's optimizer (at least as of 3 years ago, and I don't think it has changed) doesn't try to push conditions through a GROUP BY (implicit or explicit).  Therefore Oracle has to construct larger data sets than necessary, group them, and only then gets to filter.  Thus prefiltering wherever possible is officially a Good Idea.  (This is, incidentally, why it is important to put conditions in the WHERE whenever possible instead of leaving them in a HAVING clause.)
Furthermore if you happen to know that there won't be duplicates between the two data sets, then use UNION ALL.  That is like UNION in that it concatenates datasets, but it doesn't try to deduplicate data.  This saves an expensive grouping operation.  In my experience it is quite common to be able to take advantage of this operation.
Since UNION ALL does not have an implicit GROUP BY in it, it is possible that Oracle's optimizer knows how to push conditions through it.  I don't have Oracle sitting around to test, so you will need to test that yourself.
                @JonHeller You were replying in 2016 to a post written in 2011 that was clearly marked as based on my experience several years before that.  A lot could have changed, and a succinct statement of what is much more helpful than a blanket, "that's wrong".
– btilly
                Mar 20, 2017 at 16:41
                The example in 11.2 Performance Tuning Guide show this scenario and how it works even with the WHERE after the UNION.  I don't see the same in the 10g manual, so either it was new in 11g or not documented in 10g.  It might be unfair to judge answers based on new information but the answer is no longer helpful to people.  I know an undeserved downvote sucks but it's less worse than giving misleading advice to tens of thousands of people.
– Jon Heller
                Mar 20, 2017 at 20:49
                @JonHeller I know for a fact that it wasn't there in 8, and I'm pretty sure it wasn't there in 10.  However if you're going to different databases, it isn't behavior to rely on.  And preferring UNION ALL over UNION will be forever true based on the SQL Standard.  But I'll update the answer.
– btilly
                Mar 20, 2017 at 22:33
SELECT colA, colB FROM tableA WHERE colA > 1
UNION
SELECT colX, colA FROM tableB WHERE colA > 1
compared to:
SELECT * 
  FROM (SELECT colA, colB FROM tableA
        UNION
        SELECT colX, colA FROM tableB) 
 WHERE colA > 1
Then in the second query, the colA in the where clause will actually have the colX from tableB, making it a very different query. If columns are being aliased in this way, it can get confusing.
You need to look at the explain plans, but unless there is an INDEX or PARTITION on COL_A, you are looking at a FULL TABLE SCAN on both tables.
With that in mind, your first example is throwing out some of the data as it does the FULL TABLE SCAN.  That result is being sorted by the UNION, then duplicate data is dropped. This gives you your result set.
In the second example, you are pulling the full contents of both tables.  That result is likely to be larger.  So the UNION is sorting more data, then dropping the duplicate stuff.  Then the filter is being applied to give you the result set you are after.
As a general rule, the earlier you filter away data, the smaller the data set, and the faster you will get your results.   As always, your milage may vary.
SELECT * FROM (SELECT colA, colB FROM tableA UNION SELECT colA, colB FROM tableB) as tableC WHERE tableC.colA > 1
If we're using a union that contains the same field name in 2 tables, then we need to give a name to the sub query as tableC(in above query). Finally, the WHERE condition should be WHERE tableC.colA > 1
                Hi, welcome to Stack Overflow. Consider adding a little explanation to your answer. You can do so by clicking 'edit'
– Olivier De Meulder
                Feb 17, 2016 at 12:55
                I didn't downvote, but it might be a reaction to the pervasive "add an index to solve any performance problem" mentality.
– Jeffrey Kemp
                Mar 31, 2011 at 1:11
                While the SQL doesn't provide a solution to the original question, you should always consider providing supporting information to an answer.
– Bonez024
                May 10, 2019 at 16:15
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.