Oracle hierarchical query execution steps

Question

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:

A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
The CONNECT BY condition is evaluated.
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?

OMG Ponies · Accepted Answer · 2010-08-02 17:05:53Z

2

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

answered Aug 2, 2010 at 17:05

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle hierarchical query execution steps

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related