oracle “选择”是否总是按主键排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/824855/
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
Does 'Select' always order by primary key?
提问by Thierry-Dimitri Roy
A basic simple question for all of you DBA.
一个针对所有 DBA 的基本简单问题。
When I do a select, is it always guaranteed that my result will be ordered by the primary key, or should I specify it with an 'order by'?
当我进行选择时，是否总是保证我的结果将按主键排序，还是应该使用“order by”来指定它？
I'm using Oracle as my DB.
我使用 Oracle 作为我的数据库。
回答by BobbyShaftoe
No, if you do not use "order by" you are not guaranteed any ordering whatsoever. In fact, you are not guaranteed that the ordering from one query to the next will be the same. Remember that SQL is dealing with data in a set based fashion. Now, one database implementation or another may happen to provide orderings in a certain way but you should never rely on that.
不，如果您不使用“order by”，则无法保证任何订购。事实上，您不能保证从一个查询到下一个查询的顺序是相同的。请记住，SQL 以基于集合的方式处理数据。现在，一个或另一个数据库实现可能会以某种方式提供排序，但您永远不应该依赖它。
回答by Quassnoi
When I do a select, is it always guaranteed that my result will be ordered by the primary key, or should I specify it with an 'order by'?
当我进行选择时，是否总是保证我的结果将按主键排序，还是应该使用“order by”来指定它？
No, it's by far not guaranteed.
不，到目前为止还不能保证。
SELECT *
FROM table
most probably will use TABLE SCANwhich does not use primary key at all.
很可能会使用TABLE SCANwhich 根本不使用主键。
You can use a hint:
您可以使用提示：
SELECT /*+ INDEX(pk_index_name) */
*
FROM table
, but even in this case the ordering is not guaranteed: if you use Enterprise Edition, the query may be parallelized.
，但即使在这种情况下，也不能保证排序：如果使用Enterprise Edition，则查询可能会并行化。
This is a problem, since ORDER BYcannot be used in a SELECTclause subquery and you cannot write something like this:
这是一个问题，因为ORDER BY不能在SELECT子句子查询中使用，并且您不能编写如下内容：
SELECT (
SELECT column
FROM table
WHERE rownum = 1
ORDER BY
other_column
)
FROM other_table
回答by Dave Costa
No, ordering is never guaranteed unless you use an ORDER BY.
不，除非您使用 ORDER BY，否则无法保证订购。
The order that rows are fetched is dependent on the access method (e.g. full table scan, index scan), the physical attributes of the table, the logical location of each row within the table, and other factors. These can all change even if you don't change your query, so in order to guarantee a consistent ordering in your result set, ORDER BY is necessary.
获取行的顺序取决于访问方法（例如全表扫描、索引扫描）、表的物理属性、表中每一行的逻辑位置以及其他因素。即使您不更改查询，这些都可能会更改，因此为了保证结果集中的顺序一致，ORDER BY 是必要的。
回答by Roman
It depends on your DB and also it depends on indexed fields.
这取决于您的数据库，也取决于索引字段。
For example, in my table Users every user has unique varchar(20) field - login, and primary key - id.
例如，在我的表用户中，每个用户都有唯一的 varchar(20) 字段 - 登录名和主键 - id。
And "Select * from users" returns rowset ordered by login.
并且“从用户中选择 *”返回按登录排序的行集。
回答by pmcilreavy
If you desire specific ordering then declare it specifically using ORDER BY.
如果您需要特定的排序，则使用 ORDER BY 专门声明它。
What if the table doesn't have primary key?
如果表没有主键怎么办？
回答by Eppz
If you want your results in a specific order, always specify an order by
如果您希望按特定顺序显示结果，请始终按以下方式指定顺序
