Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Art.

Asked: November 24, 2020 - 5:34 am UTC

Last updated: November 26, 2020 - 6:13 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom, a fan of your work.

Have a question: are there any operations in Oracle preserving row order?

For example, can I expect that
select * from (select tag from test order by tag)


Will return in sorted order?

Or if a pipelined table function produces a dozen rows in certain order, can I use
"select * from table(f())"
to see them in the same order?

Will a cursor read rows from a pipelined function in the same order they are piped?

Basically, looking for exceptions to the general rule "any operation destroys row order".

and Chris said...

As the docs say:

Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

So it's not that operations destroy the row order, it's more that without an order by, you can't be 100% certain the rows will be in the order you want them.

A simple subquery like this:

select * from (select tag from test order by tag)


Is logically the same as just running the inner query - there's no further processing of the data in the outer query, so there's no reason for the database to change the order.

"Do nothing" outer queries are rare in the real world though. It's almost certain that you have some processing in the outer query such as filtering, grouping, etc. For simple filtering (just a where clause), I can't think of a time when this has changed the initial sort. But like I say, it's not guaranteed to preserve the original order.

A common counter-example that can change the sorting is parallel processing. Before running a query, someone could execute:

alter session force parallel query;


And all the following queries will run in parallel.

While (currently) pipelined table functions only run in parallel in specific circumstances, this may change in the future. And when PTFs do run in parallel they rows no longer return in the order they're generated.

Bottom line, if you want to be 100% certain you get the rows back in the order you want, add an order by to the outer query!

If it's unnecessary, the optimizer will ignore it and do just one sort.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Well worded response

A reader, November 24, 2020 - 2:33 pm UTC

I appreciate chris taking the time to write a full response, reminding me to always rely on "order by" clause in order to guarantee a specific row order.
Hopefully, he will also be the person answering to my follow-up question.

Thank you

A reader, November 24, 2020 - 3:00 pm UTC

Follow-up question:

In your response, you indicated that "do nothing" queries like select * without filters are not likely to change the order of rows.

I am looking for this type of exceptions, with guarantees backed by documentation or relevant SQL standard(s).

To illustrate my point better, I wrote a small livesql here:
https://livesql.oracle.com/apex/livesql/s/kzq0nuvze2leboo0p9l0i9yxm

Chris Saxon
November 24, 2020 - 4:31 pm UTC

My point is there are NO guarantees unless you have an order by. In practice, there may be cases where an outer sort is unnecessary. But adding an order by to the outer query is the only way to be 100% certain you'll fetch rows in the order you want.

In your example query:

with products (sku,price) as ( 
  select 'toothbrush', 2.99 from dual union all 
  select 'bicycle',    149  from dual 
) 
select * from (select sku from products order by price) where rownum=1


There's nothing sorting the data to ensure you'll always get toothbrush as the first row. If you want to ensure this, you need some way of sorting the data that places this first in the result set.

Another great response

Art, November 24, 2020 - 6:52 pm UTC

Chris,

thank you for reminding me about the "belt and suspenders" approach to the ordering. I do practice defensive programming, and would not rely on the input data order in production code.

I am trying to further my understanding of the Oracle concepts and SQL standards, which is why I am being pedantic about the guarantees.

For example, Oracle endorses the following approach for top-N reporting:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;


In this example the inner ORDER BY is effective in controlling the order in which outer SELECT will see the records, and this behavior is guaranteed.

So in this example, the outer select will sequentially process* the 10 records in sorted order, but may still output them in some other random order? If this is the case, then the Top-N recommendation needs to change from
SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;


to

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11
   ORDER BY employee_id;


in order to match the 12.+ FETCH FIRST 10 ROWS ONLY construct.

Or, can Oracle just state that the original Top-N construct will deliver the top N rows in the sorted order?

(*) I say "sequentially process" because presence of rownum pseudo-column assigns strict logical processing order to the rows.

Connor McDonald
November 25, 2020 - 4:53 am UTC

Ultimately, this is why we are moving to the row limited syntax to remove any ambiguity, ie

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/sql-analysis-reporting-data-warehouses.html#GUID-27A245ED-676B-4FC1-827B-B58020E234AA

but simply due to the backward compatibility, you can have a very strong confidence in

select .,..
from 
  ( select .... order by )
where rownum < ....


because enormous parts of existing applications rely on it.

Why

Chuck Jolley, November 24, 2020 - 7:33 pm UTC

My related question is:
Why have people been asking how to get sorted records without an order by for decades?
Connor McDonald
November 25, 2020 - 4:47 am UTC

:-)

Working with sorted recordsets

Art, November 25, 2020 - 4:19 am UTC

Hi Chuck, Florida man here :)

The question "which operations preserve the order of rows in already sorted recordset" should not be confused with "how to create/ensure order without sorting".

In the example below the inner query delivers an ordered recordset, but the sorting column is lost.

with products (sku,price) as ( 
  select 'toothbrush', 2.99 from dual union all 
  select 'floss',    0.99 from dual union all 
  select 'bicycle',    149  from dual 
) 
select * from (select sku from products order by price) where rownum <=2


Through its documentation Oracle tells me that I can pick two cheapest products using select * ... where rownum <= 2 construct, but hesitant to say that the top-N subset will remain ordered.

If that is the case, some tasks would not have a clear solution.
For instance, assuming row mixing behavior of select * makes it difficult to fetch second or third least expensive product without modifying the inner query to propagate the sorting key.

Oracle recognizes and works with ordered recordsets when it comes to top-N queries, cursors and some table functions. It would be helpful to know a list of operations that preserve the row order on such recordset.

For example, can a do-nothing sort

select * from 
  (select * from products order by price)
 order by -3.14f   -- order by const is a no-op


rearrange rows of a previously sorted recordset?

Chuck Jolley, November 25, 2020 - 4:41 pm UTC

These questions are almost always an attempt to force the database to get an answer a certain way. For example, trying to prevent a second sort operation after a group by. But this hasn't been a problem for over 20 years.
The answer is, as it almost always is, statistical functions.
Or for 12 & up FETCH FIRST N... after the order by.

There is no reason to put an order by in an inner query anymore.
Chris Saxon
November 25, 2020 - 5:34 pm UTC

There is no reason to put an order by in an inner query anymore.

I'm sure someone will find a reason ;) But yes, it's highly unlikely you need an inner order by.

A reader, November 25, 2020 - 10:10 pm UTC


That was the response I was looking for

Art, November 26, 2020 - 2:21 pm UTC

To the unnamed smiley guy in black T-shirt - your response clear to me.

I understood that with the fetch keyword (starting in 12.1), the previous Top-N construct is just for legacy support, and ordering in inner selects becomes meaningless.

There is probably someone (or a team) at Oracle keeping track of legacy features, hopefully the "inner sort" is on their list to deprecate unless Oracle runs in compat mode.

The deprecated inner sort should probably also include "order by" statement in the views, because it is still supported for some unknown reason.
Chris Saxon
November 26, 2020 - 6:13 pm UTC

That was Connor ;)

Just realized I didn't look at your Live SQL script closely enough - the inner sort ensures you'll pick the cheapest products first. But the order these top-N are returned by the outer query is - strictly speaking - undefined (if you get more than one)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.