不带Order By子句的SQL Select语句的顺序 [英] The order of a SQL Select statement without Order By clause
问题描述
不带order by子句的select语句应被认为没有特定的顺序.但是实际上，在SQL Server和Oracle中(我已经在这两个平台上进行了测试)，如果我多次从没有order by子句的表中查询，我总是会以相同的顺序获得结果.可以依靠这种行为吗?有人可以帮忙解释一下吗?
不，不能依靠这种行为.顺序由查询计划者决定构建结果集的方式确定.像select * from foo_table这样的简单查询很可能会以它们在磁盘上存储的顺序返回，该顺序可能是主键顺序，创建顺序或其他随机顺序.取而代之的是，更复杂的查询(例如select * from foo where bar < 10)可以基于索引读取或按表顺序以表扫描的不同列的顺序返回.带有更多where条件，group by子句，union s的更加精细的查询，将以计划者认为最有效的顺序生成.
两个相同查询之间的顺序甚至可能会更改，因为这些查询之间的数据已更改.一个查询中的索引扫描可能满足"where"子句的要求，但以后的插入可能会使条件的选择性降低，计划者可以决定使用表扫描来执行后续查询.
要明确一点. RDBMS系统的任务是尽可能高效地准确为您提供所需的内容.这种效率可以采取多种形式，包括最小化IO(到磁盘以及通过网络将数据发送给您的IO)，最小化CPU并使其工作集变小(使用需要最少临时存储的方法).
没有ORDER BY子句，您将不会确切地询问 特定的顺序，因此RDBMS将以某种顺序为您提供那些行，这些行与(也许)对应于某行的巧合.查询，基于RDBMS期望以最快的速度生成数据的算法.
如果您关心效率而不是顺序，请跳过ORDER BY子句.如果您只关心顺序而不是效率，请使用ORDER BY子句.
因为您实际上关心 BOTH ，所以请使用ORDER BY，然后仔细调整查询和数据库，以使其高效.
As I know, from the relational database theory, a select statement without an order by clause should be considered to have no particular order. But actually in SQL Server and Oracle (I've tested on those 2 platforms), if I query from a table without an order by clause multiple times, I always get the results in the same order. Does this behavior can be relied on? Anyone can help to explain a little?
No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10 may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where conditions, group by clauses, unions, will be in whatever order the planner decides is most efficient to generate.
The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.
To put a finer point on it. RDBMS systems have the mandate to give you exactly what you asked for, as efficiently as possible. That efficiency can take many forms, including minimizing IO (both to disk as well as over the network to send data to you), minimizing CPU and keeping the size of its working set small (using methods that require minimal temporary storage).
without an ORDER BY clause, you will have not asked exactly for a particular order, and so the RDBMS will give you those rows in some order that (maybe) corresponds with some coincidental aspect of the query, based on whichever algorithm the RDBMS expects to produce the data the fastest.
If you care about efficiency, but not order, skip the ORDER BY clause. If you care about the order but not efficiency, use the ORDER BY clause.
Since you actually care about BOTH use ORDER BY and then carefully tune your query and database so that it is efficient.
这篇关于不带Order By子句的SQL Select语句的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
