Re: SELECT within the FROM clause?

Dereck L. Dietz wrote:
> "Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
> news:45032690.363890_at_news.hetnet.nl...
> > On Sat, 09 Sep 2006 20:22:51 GMT, "Dereck L. Dietz"
> > <dietzdl_at_ameritech.net> wrote:
> >
> > >
> > It's called an inline view, and it serves the same purpose as a normal
> > view, but now coded inside the complete statement itself. It's been
> > around since Oracle 8 if my memory serves me right.
> >
> > From Oracle 9 on, you can even put a SELECT in the SELECT list, like
> >
> > SELECT (SELECT 1 from dual) from dual;
> >
> > This is called a scalar select. It is allowed to yield only one
> > value, so
> >
> > select (select 1,2 from dual) from dual;
> >
> > is illegal, as is
> >
> > select (select 1 from dual union select 2 from dual) from dual;
> >
> > Jaap.
>
> Okay thanks. Would you happen to know of the top of your head if it is as
> efficient as creating an actual view to put in the query or are they about
> the same?

You will likely find that an inline view will perform more efficiently than a normal, statically defined view when the view is not used as is (ex: joined to other tables/views, additional WHERE clauses). Details can be found in Jonathan Lewis' "Cost-Based Oracle Fundamentals" book.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Sep 09 2006 - 17:39:40 CDT