1

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.
Mark A. Donohoe
  • 28,442
  • 25
  • 137
  • 286
  • 2
    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. – Nick May 02 '20 at 06:33
  • The reason I was asking was because there are several other CTEs that are derived from that first one, all with the exact same sort. I was thinking if I sorted the source, then just filtered each of the subsequent CTEs, I'd be fine. Guess not. :/ – Mark A. Donohoe May 02 '20 at 06:34
  • 1
    You could simplify the subsequent sorting by adding a row number field to the original CTE based on its sort criteria and then just sort by the row number in the derived CTEs... – Nick May 02 '20 at 06:47
  • That's a great idea! But why put that here? Add it to an answer and I can accept it! :) – Mark A. Donohoe May 02 '20 at 06:48
  • Well, if the subsequent CTEs involve expansion of rows (e.g. through a `JOIN`) it wouldn't work... but why do you need to sort the subsequent ones? are you using `FETCH` to limit the number of results? – Nick May 02 '20 at 06:58
  • See my question here (https://stackoverflow.com/q/61546104/168179) for something closer to what I'm after. In short, I'm trying to grab the first row of one select, the first row of another select, then the first three rows of a third select (which is ordered) then display them in that order. I'm solving it by adding a 'MAJOR_SORT' column to each, using '2' for that first select, '1' for the second, then '0' for the third. That way I can first sort on MAJOR_SORT, then apply the order-by from the third select. Just wondering if there was an easier way. – Mark A. Donohoe May 02 '20 at 07:01
  • The thing is throughout the CTE's I'm always sorting by DatePublished DESC so I was wondering if I sorted that up front, would I be able to remove it everywhere else, but it sounds like the answer is no. (BTW, I just updated the code there to be more clear.) – Mark A. Donohoe May 02 '20 at 07:08
  • I posted a solution to your other question which basically works on what we were discussing here. – Nick May 02 '20 at 08:49

2 Answers2

2

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
Nick
  • 138,499
  • 22
  • 57
  • 95
0

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.

wolφi
  • 8,091
  • 2
  • 35
  • 64