Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!e3g2000cwe.googlegroups.com!not-for-mail
From: "Charles Hooper" <hooperc2000@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: SELECT within the FROM clause?
Date: 9 Sep 2006 15:39:40 -0700
Organization: http://groups.google.com
Lines: 39
Message-ID: <1157841580.339801.286040@e3g2000cwe.googlegroups.com>
References: <voFMg.3661$MF1.3024@newssvr25.news.prodigy.net>
   <45032690.363890@news.hetnet.nl>
   <HIGMg.8098$tU.6558@newssvr21.news.prodigy.com>
NNTP-Posting-Host: 65.118.7.2
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1157841585 32098 127.0.0.1 (9 Sep 2006 22:39:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 9 Sep 2006 22:39:45 +0000 (UTC)
In-Reply-To: <HIGMg.8098$tU.6558@newssvr21.news.prodigy.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: e3g2000cwe.googlegroups.com; posting-host=65.118.7.2;
   posting-account=ytcoAwwAAADhCs0M3G1mFO5tqSfx4ge9
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:274781

Dereck L. Dietz wrote:
> "Jaap W. van Dijk" <j.w.vandijk.removethis@hetnet.nl> wrote in message
> news:45032690.363890@news.hetnet.nl...
> > On Sat, 09 Sep 2006 20:22:51 GMT, "Dereck L. Dietz"
> > <dietzdl@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.