如果您做了一个简单的选择--在已经排序的CTE上，您的结果是否保证保持相同的顺序？

Question

想知道Oracle 11g对基于排序CTE的查询的预期/确定性排序输出。

考虑一下这个示例SQL查询(为了使用SQL查询，这个示例非常简化)。同样，请注意CTE如何在其中包含一个ORDER BY子句。

WITH SortedArticles as (
    SELECT.  *
    FROM     Articles
    ORDER BY DatePublished
)
SELECT *
FROM   SortedArticles
WHERE  Author = 'Joe';

是否可以假设输出的行与CTE的顺序相同，还是必须重新排序？

同样，这是一个非常过于简化的例子，但它包含了我所要求的重要部分。他们是..。

1. CTE是排序的
2. ，最后的SELECT语句只针对CTE选择，没有其他的(没有联接等等)，而
3. 最后的SELECT语句只指定了WHERE子句。它纯粹是一个过滤语句。

Answer 1

简短的回答是否定的。保证排序的唯一方法是在外部查询上使用ORDER BY子句。但在这种情况下，没有必要对CTE的结果进行排序。

但是，如果排序表达式很复杂，并且需要在派生的CTE中进行排序(例如，由于使用了OFFSET/FETCH或ROWNUM)，则可以简化后续排序，方法是根据原始CTE的排序条件向原始CTE添加行号字段，然后根据该行号对派生的CTE进行排序。以你为例：

WITH SortedArticles as (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY DatePublished) AS rn
    FROM Articles
)
SELECT *
FROM SortedArticles
WHERE Author = 'Joe'
ORDER BY rn

Answer 2

不，结果不能保证与子查询的顺序相同。从来没有，将来也不会。您可能会观察到某种行为，特别是当CTE物化时，您可以尝试通过优化器提示(如/*+ MATERIALIZE */和/*+ INLINE */ )来影响这种行为。但是，查询优化器的行为也取决于数据量、IO与cpu的速度，最重要的是取决于数据库版本。例如，Oracle 12.2引入了一个名为“内存中的游标持续时间临时表”的特性，它试图加快像您这样的查询，而不保留子查询中的顺序。

我同意@Nick关于在子查询中添加行号字段的建议。