Question Details

No question body available.

Tags

sql oracle-database oracle11g oracle10g

Answers (1)

January 23, 2026 Score: 2 Rep: 12,817 Quality: Medium Completeness: 60%

If your outer query does nothing more than select * from my_view then yes, Oracle will honor the order by clause in the view. But if your outer query does anything else - a join, for example - then no, you cannot be sure of the order of the data. So you are correct in your observation.

The reason is that ordering is strictly a presentation layer effect - which makes no sense if more work is to be done before the data is presented (fetched by the client). Internally Oracle will often order your data - intentionally or incidentally - simply as an effect of the optimizer's choice of data access (index vs. full scan vs. parallel query vs. partititioned, etc..), join order, and join method (hash join vs. merge join vs. nested loops).

Even a simple additional filter (where age > sysdate - 365) can change the order of rows because it can change data access method (using an index where otherwise it would have used a full table scan), it can cause partition pruning, it can offload a predicate to storage in Exadata where parallel processes gather it and filter with zone indexes, etc... even potentially how a simple filter works internally (perhaps in the future). Lots of variations could/might occur due to a simple predicate addition.

So, all these operations potentially impact (unpredictably) the order in which rows are emitted to the next step. That's why any further instructions above the view level effectively invalidates the order by request in the view - carrying out those instructions would undo whatever ordering was already done. Oracle knows this, so instead of wasting resources on an order by that might get undone later, it simply skips it. In older versions of Oracle, it wasn't even permitted to have an order by in an inner block because it simply doesn't make sense anywhere but in the outermost block. Now it is permitted, but in those positions you cannot rely on it doing what you expect, or anything at all.

The one exception is when you use ROWNUM in filtering in an outer block - then it is forced to do the inner order by (if specified) in order to deterministically compute ROWNUM.

For this reason, never rely on order by within views - if the order matters, always put an order by on the outermost (parent) query coming from the user - the last thing to be done before rows are fetched to the client.