[SOLVED] What is the difference between the UNION and CONCATENATION operators in terms of performance?

What is the difference between the UNION and CONCATENATION operators in terms of performance?

Sometimes, it can be seen CONCATENATION step in the Explain Plan.

I wonder what is the difference between union and concatenation operators in terms of performance tuning?

Solution

First up, UNION and CONCATENATION are subtly different.

CONCATENATION is equivalent to UNION-ALL. This combines the input tables and returns all the rows.

UNION combines the input tables. Then returns the distinct rows.

So UNION has an extra sort/distinct operation compared to CONCATENATION. How big this effect is depends on your data set.

You'll see CONCATENATION when the optimizer does an OR expansion. But note that from Oracle Database 12.2, this has changed:

CONCATENATION is replaced with UNION-ALL.

Each UNION-ALL branch can be subject to further query transformations, if applicable. This is not possible with CONCATENATION.

Parallel queries can execute UNION-ALL branches concurrently. Again, this is not possible with CONCATENATION.

So UNION-ALL can come up with better plans for each operation below it. And run these at the same (if using parallel). So in many cases this will be faster than CONCATENATION.