CODEWITHSUNDEEP
oracle-databasesubqueryquery-performance
bish
bish

Reputation: 3419

Are non-relating subselects in Oracle calculated before outer statement?

After long time of search google and SO I still didn't find anything about the calculation of non-related subselects in Oracle.

I have the situation where I need to to find differences and I was thinking about using a query like this, where the subselect is independend from the outer one:

select pk
from A
where pk is not in 
  (select from complex_query_with_many_joins )   -- non related quite complex query with many joins

I read that in MySQL up to 5.5 (they change with 5.6) the subselect is calcuclated for each row of the outer even if they are non-realated.

How does oracle calculates the subselect? Would it be better to use MINUS instead of subselect? [My tables are quite huge, meaning 100Ks rows]

Upvotes: 0

Views: 28

Answers (3)

Alex Poole
Alex Poole

Reputation: 191360

It depends on what the subquery is doing and whether the optimiser can unnest it. From the documentation:

Subqueries are nested when they appear in the WHERE clause of the parent statement. When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.

Which suggests that it will execute the subquery multiple times, but goes on to say:

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions.
...
Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:
- Uncorrelated IN subqueries

As @ThorstenKettner your example looks fine, but you can always check what the execution plan shows, or trace the query to get into the details of what it is doing under the hood.

Upvotes: 1

Husqvik
Husqvik

Reputation: 5809

Obvious answer is - it depends.

Always see the execution plan and then the actual cursor (DBMS_XPLAN.DISPLAY_CURSOR) and you see what happens and in what order. If you want to see even more make 10053 trace and see all the considerations optimizer makes. Oracle has myriads of optimizations when it comes to its optimizer.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 95029

Oracle has a very good optimizer. It will decide whether to select the subquery data first or on-the-fly. Internally it can completely re-write your query and choose from many ways to get to the data.

Sometimes it seems obvious to us the DBMS should load the data first and then use this list as a look-up, which is what we would do given this task. However, for the DBMS another way can be much better.

Your IN clause is fine. It is very readable and I see no reason to change it. But if you find MINUS more readable use that instead. Make your queries as simple and readable as possible. Rely on Oracle's optimizer to find the best execution plan. Only when running into performance problems should you think of re-writing the query - so as to show the optimizer another option :-)

Upvotes: 2

Related Questions