Sorting in UNION

In oracle, does joining two table using UNION implicitly sort data? It appears it does, b开发者_JS百科ecause as shown in the explain plan window, it shows 'SORT UNIQUE'.

My question is just: by default, by how or what column does it sort?

It does need to enforce uniqueness so it does normally need to do at least a partial sort. In theory, if it can guarantee uniqueness by another mechanism (eg selecting from specified partitions where the partition key guarantees no overlap) it might not, but I haven't seen an example of that yet.

However it doesn't need to produce sorted output since it can use a hash sort. That is, it sticks all the 'A' words on one bucket, all the 'B' words in the next and so on. The contents of the individual buckets would be sorted but the query may return the results of the 'B' bucket before the 'A' bucket.

Between 9i and 10g Oracle modified the GROUP BY so that it often did a HASH SORT. As a result a lot of people who had taken it for granted that a GROUP BY would imply sorted output were caught out.

The optimizer has a various ways of insuring uniqueness when doing things like union, group by and distinct. And over time as Oracle improves the optimizer, new methods may and hopefully will be introduced. Sometimes that is a sort. But no guarantees it will always be a sort, even if it uses a sort today, it might not tomorrow.

If you need the output sorted, the only way to guarantee that is with an order by clause. Without the order by Oracle (and for that matter any SQL database) is free to return the rows in any order.

yes the sort is implicit in order to remove duplicates since UNION does not include duplicates like UNION ALL, however if you want a guaranteed sort use ORDER BY after all unions to make sure you get the desired sorting