Oracle SQL: Is ORDER BY non unique field deterministic?

Question

Let's say I have a view MYVIEW

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

and I query the following statement

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 (with a value of 100)
2. item3 (with a value of 100)
3. item2 (with a value of 101)

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

Answer 1

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.

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.

EDIT:

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

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.