What order does Oracle's EXIST condition compare the sub-query results?

Question

I know EXIST works better than IN for larger sub-queries because it checks each sub-query record individually and stops once a comparison is found.

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. Would EXIST actually stick to the order in which the sub-query is returned?

I'm aware this approach is a bit of a double-edged sword, with specifying an order for a large sub-query having a negative impact on performance. I suppose I'm mostly just curious.

Answer 1

I would advise, don't try to out-think the optimizer like this. It doesn't always execute your query how you think it would be done based on how you wrote it. It may be able to take advantage of indexes or alternate join paths. And as mathguy said above, the difference between EXISTS and IN is much better than it used to be.

I would actually expect if you add an ORDER BY clause to your query, it might make it worse, because the entire result set might have to be retrieved, ordered, then checked against the EXISTS clause. But really, there's no way to know without an execution plan.

Just write your query so it retrieves the data you need, test it, then optimize it only if you need to. To quote Donald Knuth, "Premature optimization is the root of all evil."

Answer 2

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.

Answer 3

The optimiser will typically transform both in and exists subqueries into semi-joins rather than following the exact way they are written.

I am not sure what you mean by ordering the subquery to make it more likely for the match to be at the top. You can’t use order by directly in an exists subquery, although it might be embedded within a view, in which case the optimiser will generally ignore it. If you mean that the table being queried in the subquery has the expected data near the top, then it is possible that the matching row will appear earlier in the hash join or whatever the optimiser chooses and that’ll be faster, or not, depending on the execution plan chosen.