Oracle Performance and Engineering

Another prevalent myth among SQL coders is "Use UNION ALL and don't really use UNION". The reason (they believe) since UNION ALL does not sort the data it's gotta be faster. Some people even go to the extent of firing up some kinda standard doing

SELECT DISTINCT (values)
from
(select values from A
UNION ALL
select values from B);

Rather than writing

select values from A
UNION
select values from B;

Just a few days back one developer actually rushed to me when he found that in one query he was debugging UNION ALL happened to be faster. He has this - "OK - finally I found a bug in Oracle kernel" look! Thankfully I was doing my daily dosage of Dilbert - and retained my sanity.

Let's just take this somewhat detailed description from a someone amazed at the fact that UNION was indeed "faster" in his case.

In this particular case, however, the difference in time can probably be attributed to the fact that data was physically partitioned in this Data Warehouse (look at the 'UNION-ALL (PARTITION) " in the plan). Also note "partition_view_enabled = true" in the parameter settings. In the faster plan, the partition was eliminated.

For those still thinking about UNIONs can be replaced by UNION ALL and vice versa, a small example -

-- UNION (duplicates removed):
select * from dual
union
select * from dual;
-----------------
X

-- UNION ALL (duplicates not removed)
select * from dual
union all
select * from dual;
------------------
X
X

Here's an example for Oracle8 where UNION ALL was even slower than the sum of execution of the component queries.

Here's an example from Metalink (needs login). Someone actually was anguished and demanded to know WHY UNION is faster than UNION ALL. Very interesting case with Oracle 8i. Anyone cares to illustrate why? I have an opinion on that.

I've done my own tests and found that upto Oracle 10g, in many different cases UNION performance can be significantly better than UNION ALL.

Let's say, that both are different. If one could have been replaced by the other better-performing one, then why would there be two operators? Language components are not like dress shoes.