Search code examples
sqloraclehierarchical-query

Oracle hierarchical query execution steps

Here's the documentation: http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm

Here's the execution order it describes:

  1. A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
  2. The CONNECT BY condition is evaluated.
  3. Any remaining WHERE clause predicates are evaluated.

My question is, does part 1. from above apply to nested queries. For example, if I have:

select * from foo
where exists (select 'x' from bar
              where bar.foo_id = foo.foo_id 
              start with bar.id is null
              connect by prior bar.id = bar.parent_id)

Does the join in the where clause get executed immediately, or, since foo.foo_id in this context is effectively a single value, is it executed in part 3 where the rest of the single-value conditional restrictions are executed?

Solution

  • What you posted is a correlated subquery.

    Because there is no JOIN - steps 2 & 3 will be performed:

    2) The CONNECT BY condition is evaluated.
    3) Any remaining WHERE clause predicates are evaluated