"DA Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3E41B770.940B52B9_at_exesolutions.com...
> --CELKO-- wrote:
>
> > <snipped>columns are indexed, it is reasonably fast. <<
> >
> > >> What do you mean by "direct use"? This is SQL, a user is allowed to
> > inline and nest subqueries. <<
> >
> > But you want to avoid them in favor of simple, "flat" joins whenever
> > possible. They hurt performance and maintainability.
> >
>
> Not in Oracle.
>
> The performance impact of inline views, while there must be some, is
> unmeasurable. And, in fact, I can easily demonstrate cases where
> subqueries are substantially faster than flat joins and do so in the
> EXPLAIN PLAN lesson I teach first quarter students.
I would be interested to see those. Evaluating subquery for each row of the
outer query (Tuple Iteration Semantics) is essentially Nested Loops.
Unnesting subquery is a transformation that makes the join explicit.
Combining all joins together into a flat select-project-join query is
beneficial because it opens larger space of join orders (For example, new
join orders might be explored, unavailable in the original query, plus other
join methods -- Merge Join and Hash Join are available as well).
Received on Wed Feb 05 2003 - 20:08:08 CST
