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

existsoracleorder-by

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.

Best Answer

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

Related Solutions

How SQL Server Orders Results When Joins Are Used

Since you haven't told SQL Server how to order the results, it is free to do so in whatever is the most efficient. In this way it will depend on what is the cheapest to sort, and the columns you select will drive that because it in turn depends on the cheapest index(es) to use to get at the information requested by the query. This can change from execution to execution not just by changing the query text but also by things like data changes, statistics updates, freeproccache, service packs, hotfixes, etc. This is especially volatile with so many tables involved if the underlying data is changing rapidly, as it tends to do in OLTP applications.

If you want a predictable order, why don't you use ORDER BY on the query? As written, the SQL specifies that any 900 rows are acceptable, in any order.

Even though, in a lot of cases, you see the same order over and over again for the same query, there is no guarantee unless you say ORDER BY <something>.

PostgreSQL ORDER BY – How to Achieve Arbitrary but Reproducible Results

without bothering to list every column in the ORDER BY clause

For just that goal, ORDER BY tablename.* does work.

If there are several tables involved, we can always write:

SELECT * FROM (select ... complex query...) as T order by T.*;

If the resultset has many wide rows making the sort inefficient, sorting a hashed representation instead might be a better idea:

SELECT * FROM (select ... complex query...) as T order by md5(T.*::text);

Best Answer

Related Solutions

How SQL Server Orders Results When Joins Are Used

PostgreSQL ORDER BY – How to Achieve Arbitrary but Reproducible Results

Related Question