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".
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.