问题描述
我对Oracle中的查询感到困惑，该查询以看似随机的顺序返回.
I'm bewildered by a query in Oracle which is returning in a seemingly random order.
SELECT
Date,
Amount
FROM MyTable
WHERE Date = '26-OCT-2010'
ORDER BY Date
这将返回以下数据:
| Date | Amount
--------------------------
1 | 26-OCT-10 | 85
2 | 26-OCT-10 | 9
3 | 26-OCT-10 | 100
我无法理解为什么数据库以这种特定顺序返回数据，或者为什么，因为原始表将以这种方式返回数据.
I cannot fathom why the database returns the data in this specific order, or why, since the original table would return the data this way.
将Date铸造为TIMESTAMP确认所有Date值都是相同的值-26-OCT-10 00.00.00.000000000，因此，我可以排除这些值之间的差异.但是，当我这样做时，行将按1、3、2的顺序返回.
Casting Date to TIMESTAMP confirms that all Date values are the same value - 26-OCT-10 00.00.00.000000000, therefore, I can rule out that there is a difference in the values. However, when I do this, the rows return in the order of 1, 3, 2.
这让我发疯了，所以如果有人可以提供关于这是为什么的解释，那真的会帮助我舒缓.
This is driving me mad so it would really help soothe me if someone could provide an explanation as to why this is.
我希望每次运行查询时，它返回的顺序都将不同，因为每行的条件条件都是相同的(因此将顺序留给纯机会).
I would expect this to return in a different order every time the query is run, given that the order conditional is identical on every row (thus leaving the ordering to pure chance).
非常感谢.
推荐答案
除非您将查询修改为:
SELECT
Date,
Amount
FROM MyTable
WHERE Date = '26-OCT-2010'
ORDER BY Date, DBMS_RANDOM.VALUE;
顺序是任意的". Oracle不会用掷骰子"来决定任意顺序(这会招致一些不必要的费用)，而是按照遇到的顺序返回数据-短期来看，运行之间可能是相同的. (从长远来看，环境可能会有所变化，以使顺序有所不同-但仍然是任意的.)
The ordering is "arbitrary". Rather than "throw dice" to decide the arbitrary order (which would incur some unnecessary cost), Oracle just returns the data in the order it encountered it - which is likely to be the same from run to run in the short term. (In the long term, something may change in the environment to make the ordering different - but still arbitrary).
这篇关于如果"orderby"值相同，为什么Oracle返回特定序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！