Oracle SQL:ORDER BY 非唯一字段是否具有确定性? [英] Oracle SQL: Is ORDER BY non unique field deterministic?

问题描述

假设我有一个视图 MYVIEW

COL1[CARCHAR2]    SORTINGCOL[NUMBER]
"itm1"            100
"itm2"            101
"itm3"            100

然后我查询以下语句

SELECT *
FROM MYVIEW
ORDER BY SORTINGCOL;

是否保证(=我可以信赖)返回的订单始终相同?让我们说

Is it guaranteed (=can I rely on) that the returned order is always the same? let's say

1. item1(值为 100)
2. item3(值为 100)
3. item2(值为 101)

1. item1 (with a value of 100)
2. item3 (with a value of 100)
3. item2 (with a value of 101)

很明显，item2 总是最后一个，但是 item1 和 item3 的 SORTINGCOL 值?

It's clear that item2 will always end up being the last, but what about item1 and item3 with the same SORTINGCOL value?

推荐答案

没有.一般的 SQL 和特别是 Oracle 不能保证稳定的排序.也就是说，您可以两次运行相同的查询并获得不同的排序——当键有关系时.

No. SQL in general and Oracle in particular do not guarantee stable sorts. That is, you can run the same query twice and get different ordering -- when the keys have ties.

这是因为 SQL 表(和结果集)代表无序集.因此，没有可以依靠的自然"排序.通常，在 order by 中包含额外的键是一个好主意，以使排序稳定.

This is because SQL tables (and result sets) represent unordered sets. Hence, there is no "natural" ordering to fall back on. In general, it is a good idea to include additional keys in the order by to make the sort stable.

我想再补充一点.您的示例用于查询中的整体排序，其中问题有点抽象——也就是说，任何给定的查询运行看起来都是正确的.窗口函数成为一个更大的问题.所以，有可能:

I want to add one more thought. Your example is for overall sorting in a query, where the issue is a bit abstract -- that is, any given run of the query is going to look correct. It becomes a bigger issue with window functions. So, it is possible that:

select v.*, row_number() over (order by sortingcol) as col1,
       row_number() over (order by sortingcol desc) as col2
from myview v

会产生不一致的结果.在正常情况下，我们希望 col1 + col2 是常量.但是，对于关系，情况往往并非如此.这会影响 row_number() 和 keep.它不会影响一致处理关系的 rank() 或 dense_rank().

would yield inconsistent results. Under normal circumstances, we would expect col1 + col2 to be constant. However, with ties, this is often not going to be the case. This affects row_number() and keep. It does not affect rank() or dense_rank() which handle ties consistently.

这篇关于Oracle SQL:ORDER BY 非唯一字段是否具有确定性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！