If you are thinking of simply adding ORDER BY
into a sub-query or similar then you would be trying to force SQL's set-based nature to behave more like an imperative language - this way negative optimisations lie.
I don't know about Oracle, but many parsers will complain about an ORDER BY
in a sub-query (where it isn't part of window function) and when they do let it pass the query planner will simply ignore it.
If it did listen to the instruction to order the sub-results, it would be forced to collect them all which, if the sub-query could return a lot of data or is otherwise computationally complex (calling heavy user defined functions for instance), could take some time. Then it needs to perform the sort which for a large set of results could imply a costly spool to disk.
You would in effect be explicitly disabling its option to abort that part of the work early if it finds the value so doesn't have to keep looking.
Would EXIST actually stick to the order in which the sub-query is returned?
Yes it would. Being a set based operation it makes no assumptions about the ordering of the data it reviews and therefore doesn't have any other option than taking it as it finds it.
Let's say I can order the sub-query in a way to make it more likely for the match to be at the top.
Having said the above, there are one or two circumstances where you may be able to increase the chance of the sub-query coming out in a particular order.
If you use a window function to add ordinal values, inspect past/future rows, etc. (ROW_NUMBER()
, LAG()
, LEAD()
, and so on, in TSQL & likely the same/similar in Oracle's dialect) then the query plan may have to order the whole resultset anyway to comply with that requirement. In this case you may be able to reverse the logic so that it is ordering in the direction that might allow your EXISTS
check to finish as early as possible - if you are using row_number
to count/differentiate rows in groups then you might not care if they are ascending or descending.
This is only going to be beneficial if the sub-query is already getting everything and sorting it, and then only if the sort is by values that your EXISTS
comparator is checking (or values that coincidentally tend to be in the same order), so it is likely to be rare enough that it is not worth thinking about until it happens in a key query.
You may also be able to coax a particular sort by making the planner use specific indexes instead of having free rein, by using index hints. In most cases again, you risk forcing the query planner to do something that would make the query slower overall instead of improving matters, so it will be rare that this option exists and is actually helpful.
I would posit that in any circumstance where you are considering tricks such as these, that the query in question could benefit far more from a wider refactor/rewrite instead.
Also, if you are considering a trick like this because you are touching the limits of the query planner (or knocking into a bug) be aware that future improvements in the planner may render your trick moot or worse introduce improvements that your "trick" directives preclude it actually being able to use.
EXISTS
can "stop" once only one match was found. To sort all matches, all matches have to be found already. So the sorting shouldn't have any effect. (If the parser accepts it. It can be picky about that.)