CODEWITHSUNDEEP
sqloracle-databaseoracle11gcommon-table-expression
Mark A. Donohoe
Mark A. Donohoe

Reputation: 30458

If you do a simple SELECT-WHERE on a CTE that is already sorted, are your results guaranteed to still be in that same order, just filtered?

Wondering about expected/deterministic ordering output from Oracle 11g for queries based on sorted CTEs.

Consider this (extremely-oversimplified for the sake of the) example SQL query. Again, note how the CTE has an ORDER BY clause in it.

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

Can it be assumed that the outputted rows are guaranteed to be in the same order as the CTE, or do I have to re-sort them a second time?

Again, this is an extremely over-simplified example but it contains the important parts of what I'm asking. They are...

  1. The CTE is sorted
  2. The final SELECT statement selects only against the CTE, nothing else (no joins, etc.), and
  3. The final SELECT statement only specifies a WHERE clause. It is purely a filtering statement.

Upvotes: 1

Views: 490

Answers (2)

Nick
Nick

Reputation: 147216

The short answer is no. The only way to guarantee ordering is with an ORDER BY clause on your outer query. But there is no need to sort the results in the CTE in that situation.

However, if the sort expression is complex, and you need sorting in the derived CTEs (e.g. because of using OFFSET/FETCH or ROWNUM), you could simplify the subsequent sorting by adding a row number field to the original CTE based on its sort criteria and then just sorting the derived CTEs by that row number. For your example:

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

Upvotes: 2

wolφi
wolφi

Reputation: 8361

No, the results are not guaranteed to be in the same order as in the subquery. Never was, never will be. You may observe a certain behaviour, especially if the CTE is materialized, which you can try to influence with optimizer hints like /*+ MATERIALIZE */ and /*+ INLINE */. However, the behaviour of the query optimizer depends also on data volume, IO v cpu speed, and most importantly on the database version. For instance, Oracle 12.2 introduces a feature called "In-Memory Cursor Duration Temp Table" that tries to speed up queries like yours, without preserving the order in the subquery.

I'd go along with @Nick's suggestion of adding a row number field in the subquery.

Upvotes: 0

Related Questions