当没有“order by"时，Oracle SQL 以任意方式返回行子句被使用 [英] Oracle SQL returns rows in arbitrary fashion when no "order by" clause is used
问题描述
也许有人可以向我解释这一点，但是当从 Oracle 查询数据表时，其中一个键(例如客户 ID)存在多个记录，如果没有隐式"order by"语句通过替代字段(例如交易类型)强制执行订单.因此，在同一张表上运行相同的查询可能会产生与 10 分钟前不同的记录排序.
例如，一次运行可能会产生:
客户 ID、交易类型
第123话第123话
除非使用order by Transaction_Type"子句，否则下次运行查询时，Oracle 可以任意返回以下结果:
客户 ID、交易类型
第123话第123话
我想我的印象是 Oracle 中有一个数据库默认的行排序(可能)反映了磁盘介质上的物理排序.换句话说，任意顺序是不可变的，并且在重新运行查询时保证相同的结果.
这是否与优化器以及它如何决定最有效地检索数据的位置有关?
当然，从编程的角度来看，最佳实践是强制要求进行任何排序，我只是对这种行为感到有点不安.
从 SELECT 语句返回到应用程序的行顺序是 COMPLETELY ARBITRARY，除非另有说明.如果您希望、需要或期望行以特定顺序返回，则用户有责任指定这样的顺序.
(警告:如果使用了某些操作，例如 DISTINCT、UNION、MINUS、INTERSECT 或 GROUP BY，某些版本的 Oracle 会隐式地按升序对数据进行排序. 但是，由于 Oracle 已经实现了哈希排序，因此数据排序的性质可能会有所不同，并且大量依赖该功能的 SQL 已失效.)
Maybe someone can explain this to me, but when querying a data table from Oracle, where multiple records exist for a key (say a customer ID), the record that appears first for that customer can vary if there is no implicit "order by" statement enforcing the order by say an alternate field such as a transaction type. So running the same query on the same table could yield a different record ordering than from 10 minutes ago.
E.g., one run could yield:
Cust_ID, Transaction_Type
123 A
123 B
Unless an "order by Transaction_Type" clause is used, Oracle could arbitrarily return the following result the next time the query is run:
Cust_ID, Transaction_Type
123 B
123 A
I guess I was under the impression that there was a database default ordering of rows in Oracle which (perhaps) reflected the physical ordering on the disk medium. In other words, an arbitrary order that is immutable and would guarantee the same result when a query is rerun.
Does this have to do with the optimizer and how it decides where to most efficiently retrieve the data?
Of course the best practice from a programming perspective is to force whatever ordering is required, I was just a little unsettled by this behavior.
The order of rows returned to the application from a SELECT statement is COMPLETELY ARBITRARY unless otherwise specified. If you want, need, or expect rows to return in a certain order, it is the user's responsibility to specify such an order.
(Caveat: Some versions of Oracle would implicitly sort data in ascending order if certain operations were used, such as DISTINCT, UNION, MINUS, INTERSECT, or GROUP BY. However, as Oracle has implemented hash sorting, the nature of the sort of the data can vary, and lots of SQL relying on that feature broke.)
这篇关于当没有“order by"时，Oracle SQL 以任意方式返回行子句被使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
