UNION 中的排序
在oracle中，使用UNION连接两个表是否隐式对数据进行排序？看起来确实如此，因为如解释计划窗口所示，它显示“SORT UNIQUE”。
我的问题只是：默认情况下，它按如何或哪一列排序？
In oracle, does joining two table using UNION implicitly sort data? It appears it does, because 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
它确实需要强制唯一性，因此通常需要至少进行部分排序。
理论上，如果它可以通过另一种机制保证唯一性（例如，从分区键保证不重叠的指定分区中进行选择），则可能不会，但我还没有看到这样的示例。
然而，它不需要产生排序的输出，因为它可以使用哈希排序。也就是说，它将所有“A”单词粘贴在一个桶中，将所有“B”单词粘贴在下一个桶中，依此类推。各个存储桶的内容将被排序，但查询可能会在“A”存储桶之前返回“B”存储桶的结果。
在 9i 和 10g 之间，Oracle 修改了 GROUP BY，以便它经常执行哈希排序。结果，许多理所当然地认为 GROUP BY 意味着排序输出的人都被抓住了。
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.
优化器在执行
union、group by和distinct等操作时有多种方法来确保唯一性。随着时间的推移，随着 Oracle 改进优化器，新的方法可能并且有望被引入。有时这是一种。但不能保证它永远是一种排序，即使它今天使用排序，明天也可能不会。如果您需要对输出进行排序，保证这一点的唯一方法是使用
order by子句。如果没有order by，Oracle（以及任何 SQL 数据库）可以自由地以任何顺序返回行。The optimizer has a various ways of insuring uniqueness when doing things like
union,group byanddistinct. 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 byclause. Without theorder byOracle (and for that matter any SQL database) is free to return the rows in any order.是的，排序是隐式的，以便删除重复项，因为
UNION不包含像UNION ALL这样的重复项，但是如果您想要有保证的排序，请使用ORDER BY> 在所有联合之后确保您获得所需的排序yes the sort is implicit in order to remove duplicates since
UNIONdoes not include duplicates likeUNION ALL, however if you want a guaranteed sort useORDER BYafter all unions to make sure you get the desired sorting