oracle oracle中选择查询的默认行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/899514/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Default row ordering for select query in oracle
提问by sengs
In Oracle, what is the the default ordering of rows for a select query if no "order by" clause is specified.
在 Oracle 中，如果未指定“order by”子句，则选择查询的默认行顺序是什么。
Is it
是吗
- the order in which the rows were inserted
- there is no default ordering at all
- none of the above.
- 插入行的顺序
- 根本没有默认排序
- 以上都不是。
采纳答案by DCookie
According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'."
根据 Tom Kyte 的说法：“除非您在查询中添加“order by”，否则您不能对返回的行的顺序发表任何看法。好吧，除了“您不能依赖返回的行的顺序”。
See this questionat asktom.com.
在 asktom.com 上看到这个问题。
As for ROWNUM, it doesn't physically exist, so it can't be "freed". ROWNUM is assigned after a record is retrieved from a table, which is why "WHERE ROWNUM = 5" will always fail to select any records.
至于ROWNUM，它在物理上并不存在，所以它不能被“释放”。从表中检索记录后分配 ROWNUM，这就是为什么“WHERE ROWNUM = 5”将始终无法选择任何记录的原因。
@ammoQ: you might want to read this AskTom articleon GROUP BY ordering. In short:
@ammoQ：您可能想阅读这篇关于 GROUP BY 排序的AskTom 文章。简而言之：
Does a Group By clause in an Query gaurantee that the output data will be sorted on the Group By columns in order, even if there is NO Order By clause?
and we said...
ABSOLUTELY NOT,
It never has, it never did, it never will.
Query 中的 Group By 子句是否保证输出数据将在 Group By 列上按顺序排序，即使没有 Order By 子句？
我们说...
绝对不，
它从来没有，从来没有，永远不会。
回答by Erich Kitzmueller
There is no explicit default ordering. For obvious reasons, if you create a new table, insert a few rows and do a "select *" without a "where" clause, it will (very likely) return the rows in the order they were inserted.
没有明确的默认排序。出于显而易见的原因，如果您创建一个新表，插入几行并执行不带“where”子句的“select *”，它将（很可能）按照插入的顺序返回行。
But you should never ever rely on a default order happening. If you need a specific order, use an "order by" clause. For example, in Oracle versions up to 9i, doing a "group by" also caused the rows to be sorted by the group expression. In 10g, this behaviour does no longer exist! Upgrading Oracle installations has caused me some work because of this.
但是您永远不应该依赖默认订单的发生。如果您需要特定订单，请使用“order by”子句。例如，在 9i 之前的 Oracle 版本中，执行“group by”也会导致行按组表达式排序。在 10g 中，这种行为不再存在！因此，升级 Oracle 安装给我带来了一些工作。
回答by Rob van Wijk
It has already been said that Oracle is allowed to give you the rows in any order it wants, when you don't specify an ORDER BY clause. Speculating what the order will be when you don't specify the ORDER BY clause is pointless. And relying on it in your code, is a "career limiting move".
已经说过，当您不指定 ORDER BY 子句时，Oracle 可以按照它想要的任何顺序为您提供行。推测当您不指定 ORDER BY 子句时的顺序是没有意义的。在你的代码中依赖它，是一个“职业限制之举”。
A simple example:
一个简单的例子：
SQL> create table t as select level id from dual connect by level <= 10
2 /
Tabel is aangemaakt.
SQL> select id from t
2 /
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rijen zijn geselecteerd.
SQL> delete t where id = 6
2 /
1 rij is verwijderd.
SQL> insert into t values (6)
2 /
1 rij is aangemaakt.
SQL> select id from t
2 /
ID
----------
1
2
3
4
5
7
8
9
10
6
10 rijen zijn geselecteerd.
And this is only after a simple delete+insert. And there are numerous other situations thinkable. Parallel execution, partitions, index organised tables to name just a few.
这只是在简单的删除+插入之后。还有许多其他情况是可以想象的。并行执行、分区、索引组织表等等。
Bottom line, as already very well said by ammoQ: if you need the rows sorted, use an ORDER BY clause.
底线，正如 ammoQ 已经说得很好：如果您需要对行进行排序，请使用 ORDER BY 子句。
回答by Kelvin
You absolutely, positively cannot rely on any ordering unless you specify order by. For Oracle in particular, I've actually seen the exact same query (without joins), run twice within a few seconds of each other, on a table that didn't change in the interim, return a wildly different order. This seems to be more likely when the result set is large.
除非您指定，否则您绝对不能依赖任何排序order by。特别是对于 Oracle，我实际上看到了完全相同的查询（没有连接），在几秒钟内彼此运行两次，在此期间没有更改的表上，返回完全不同的顺序。当结果集很大时，这似乎更有可能。
The parallel execution mentioned by Rob van Wijk probably explains this. See also Oracle's Using Parallel Executiondoc.
Rob van Wijk 提到的并行执行可能解释了这一点。另请参阅 Oracle 的使用并行执行文档。
回答by Amir Buzo
It is impacted by index , if there is index ,it will return a ascending order , if there is not any index ,it will return the order inserted .
受索引影响，如果有索引，则返回升序，如果没有索引，则返回插入的顺序。
回答by d0dulk0
You can modify the order in which data is stored into the table by INSERT with the ORGANIZATION clause of the CREATE TABLE statement
您可以使用 CREATE TABLE 语句的 ORGANIZATION 子句通过 INSERT 修改数据存储到表中的顺序
回答by Ricardo Villamil
Although, it should be rownnum (your #2), it really isn't guaranteed and you shouldn't trust it 100%.
虽然它应该是 rownnum（你的 #2），但它真的不能保证，你不应该 100% 信任它。
回答by Zenshai
I believe it uses Oracle's hidden Rownum attribute.
我相信它使用 Oracle 的隐藏 Rownum 属性。
So your #1 is probably right assuming there were no deletes done that might have freed rownums for later use.
因此，假设没有删除可能已释放 rownum 供以后使用的删除操作，那么您的 #1 可能是正确的。
EDIT:As others have said, you really shouldn't rely on this, ever. Besides deletes theres a lot of different conditions that can affect the default sorting behavior.
编辑：正如其他人所说，你真的不应该依赖这个，永远。除了删除之外，还有许多不同的条件会影响默认排序行为。
