当不使用“order by”子句时，Oracle SQL 以任意方式返回行

Oracle SQL returns rows in arbitrary fashion when no "order by" clause is used

提问by jgunnink

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.

也许有人可以向我解释这一点，但是当从 Oracle 查询数据表时，其中一个键（例如客户 ID）存在多个记录，如果没有隐式“order by”，首先出现的该客户的记录可能会有所不同通过说替代字段（例如事务类型）强制执行订单的语句。因此，在同一张表上运行相同的查询可能会产生与 10 分钟前不同的记录排序。

E.g., one run could yield:

例如，一次运行可能会产生：

Cust_ID, Transaction_Type
123 A
123 B

客户 ID，交易类型
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:

除非使用“order by Transaction_Type”子句，否则下次运行查询时，Oracle 可以任意返回以下结果：

Cust_ID, Transaction_Type
123 B
123 A

客户 ID，交易类型
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.

我想我的印象是 Oracle 中有一个数据库默认的行排序（可能）反映了磁盘介质上的物理排序。换句话说，任意顺序是不可变的，并且在重新运行查询时保证相同的结果。

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.

当然，从编程的角度来看，最佳实践是强制执行所需的任何排序，我只是对这种行为感到有点不安。

回答by Adam Musch

The order of rows returned to the application from a SELECTstatement is COMPLETELY ARBITRARYunless 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.

除非另有说明，否则从SELECT语句返回到应用程序的行顺序是完全任意的。如果您希望、需要或期望行以特定顺序返回，则用户有责任指定这样的顺序。

(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.)

（警告：如果使用某些操作，例如DISTINCT, UNION, MINUS, INTERSECT, 或，某些版本的 Oracle 会隐式地按升序对数据进行排序GROUP BY。但是，由于 Oracle 已经实现了哈希排序，因此数据排序的性质可能会有所不同，并且很多 SQL 依赖于那个功能坏了。）

回答by egrunin

There is no default ordering, ever. If you don't specify ORDER BY, you can get the same result the first 10000 times, then it can change.

从来没有默认排序。如果不指定ORDER BY，前 10000 次可以获得相同的结果，然后它可以改变。

Note that this is also true even with ORDER BYfor equal values. For example:

请注意，即使ORDER BY对于相等的值，这也是正确的。例如：

Col1 Col2
1    1
2    1
3    2
4    2

If you use ORDER BY Col2, you still don't know if row 1 or 2 will come first.

如果您使用ORDER BY Col2，您仍然不知道第 1 行还是第 2 行会先出现。

回答by a_horse_with_no_name

Just image the rows in a table like balls in a basket. Do the balls have an order?

只需将表格中的行想象成篮子里的球。球有顺序吗？

I dont't think there is any DBMS that guaranteesan order if ORDER BY is not specified.

如果未指定 ORDER BY ，我认为没有任何 DBMS 可以保证订单。

Some mightalways return the rows in the order they were inserted, but that is an implementation side effect.

有些可能总是按照插入的顺序返回行，但这是实现的副作用。

Some execution plans mightcause the result set to be ordered even without an ORDER BY, but again this is an implementation side-effect that you should not rely on.

某些执行计划可能会导致即使没有 ORDER BY 也对结果集进行排序，但这也是您不应该依赖的实现副作用。

回答by Bob Jarvis - Reinstate Monica

If an ORDER BY clause is not present the database (not just Oracle - any relational database) is free to return rows in whatever order it happens to find them. This will vary depending on the query plan chosen by the optimizer.

如果 ORDER BY 子句不存在，则数据库（不仅仅是 Oracle - 任何关系数据库）可以随意以任何顺序返回行以找到它们。这将根据优化器选择的查询计划而有所不同。

If the order in which the rows are returned matters you must use an ORDER BY clause. You may sometimes get lucky and the rows will come back in the order you want them to be even without an ORDER BY, but there is no guarantee that A) you will get lucky on other queries, and B) the order in which the rows are returned tomorrow will be the same as the order in which they're returned today.

如果返回行的顺序很重要，则必须使用 ORDER BY 子句。有时您可能会很幸运，即使没有 ORDER BY，行也会按照您希望的顺序返回，但不能保证 A) 在其他查询中您会很幸运，以及 B) 行的顺序明天返回的顺序与今天返回的顺序相同。

In addition, updates to the database product may change the behavior of queries. We had to scramble a bit when doing a major version upgrade last year when we found that Oracle 10 returned GROUP BY results in a different order than did Oracle 9. Reason - no ORDER BY clause.

此外，对数据库产品的更新可能会改变查询的行为。去年我们在进行主要版本升级时，发现 Oracle 10 返回 GROUP BY 结果的顺序与 Oracle 9 的顺序不同，因此我们不得不争先恐后。原因 - 没有 ORDER BY 子句。

ORDER BY - when the order of the returned data really matters.

ORDER BY - 当返回数据的顺序真的很重要时。

回答by Matthew Flynn

The simple answer is that the SQL standard says that there is no default order for queries that do not have an ORDER BY statement, so you should never assume one.

简单的答案是 SQL 标准说没有 ORDER BY 语句的查询没有默认顺序，所以你永远不应该假设一个。

The real reason would probably relate to the hashes assigned to each row as it is pulled into the record set. There is no reason to assume consistent hashing.

真正的原因可能与分配给每一行的哈希值有关，因为它被拉入记录集。没有理由假设哈希一致。

回答by Florin Ghita

if you don't use ORDER BY, the order is arbitrary; however, dependent on phisical storage and memory aspects. so, if you repeat the same query hundreds of times in 10 minutes, you will get almost the same order everytime, because probably nothing changes.

如果不使用 ORDER BY，则顺序是任意的；但是，取决于物理存储和内存方面。所以，如果你在 10 分钟内重复同一个查询数百次，你每次都会得到几乎相同的顺序，因为可能没有任何变化。

Things that could change the "noorder order" are:

可以改变“无序顺序”的事情是：

• the executing plan - if is changed(you have pointed that)
• inserts and deletes on the tables involved in the query.
• other things like presence in memory of the rows.(other querys on other tables could influence that)

• 执行计划 - 如果更改（您已指出）
• 在查询中涉及的表上插入和删除。
• 其他事情，比如在行的内存中的存在。（其他表上的其他查询可能会影响）

回答by user2441339

When you get into parallel data retrieval I/O isn't it possible to get different sequences on different runs, even with no change to the stored data?

当您进入并行数据检索 I/O 时，即使不更改存储的数据，是否也可能在不同的运行中获得不同的序列？

That is, in a multiprocessing environment the order of completion of parallel threads is undefined and can vary with what else is happening on the same shared processor.

也就是说，在多处理环境中，并行线程的完成顺序是不确定的，并且会随着同一共享处理器上发生的其他事情而变化。

回答by Mohamed Saif

As I'm new to Oracle database engine, I noticed this behavior in my SELECTstatements that has no ORDER BY.

由于我是 Oracle 数据库引擎的新手，我注意到我的SELECT语句中没有ORDER BY.

I've been using Microsoft SQL Server for years now. SQL Server Engine always will retrieve data ordered by the table's "Clustered Index" which is basically the Primary Key Index. SQL Server will always insert new data in a sequential order based on the clustered index.

我已经使用 Microsoft SQL Server 多年了。SQL Server 引擎总是会检索按表的“聚集索引”排序的数据，它基本上是主键索引。SQL Server 将始终根据聚集索引按顺序插入新数据。

So when you perform a select on a table without order by in SQL Server, it will always retrieve data ordered by primary key value.

所以当你在 SQL Server 中对一个没有 order by 的表执行 select 时，它总是会检索按主键值排序的数据。

ORDER BYcan cause serious performance overhead, that's why you do not want to use it unless you are not happy with inconsistent results order.

ORDER BY可能会导致严重的性能开销，这就是为什么您不想使用它，除非您对不一致的结果顺序不满意。

I ended up with a conclusion that in ALL my Oracle queries I must use ORDER BYor I will end up with unpredicted order which will greatly effect my end-user reports.

我最终得出的结论是，在我必须使用的所有 Oracle 查询中，ORDER BY否则我将以不可预测的顺序结束，这将极大地影响我的最终用户报告。