Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A technical question about VIEWS

Re: A technical question about VIEWS

From: Clark Mason <cmason_at_aai.arco.com>
Date: 1997/04/15
Message-ID: <33533B1E.4FAC@aai.arco.com>#1/1

Olen wrote:
>
> NNOOR <Nnoor_at_cris.com> wrote in article
> <5hco8f$f85_at_chronicle.concentric.net>...
> >
> > I know what views are. I want to know how views handle queries which
> > run against them. Is the view first built and then user's query is
> > satisfied from the result set (ofcourse, when NOT running SELECT *), or
> > the view is built only for the records which also satisfy user's
> > query--i.e. dynamically changing it's query. Following example will
> > explain the question I am trying to ask...
> >
> > Lets say a view is built by joining x tables. And if the Select * is
> > run for the view then, say, it returns 10,000 rows. Now if a query is
> > run against the view with a selection criteria which will result in, say,
> > only 10 rows. How will Oracle handle this situation. Will it first create
> > the view with 10,000 rows and then run the user's query on the result
> > set or it will dynamically add the selection criteria to the view's
> > query so that a result set with only 10 records is built?
> >
> > I am trying to establish that how "expensive" the view operation is
> > for internal application development purposes...
> >
> > Thanks very much for your help!
> >
> > Regards,
> > Nasir (nnoor_at_cris.com)
>
> -----BEGIN PGP SIGNED MESSAGE-----
>
> The view's query is not dynamic, you are building an intermediate table. A
> view reacts like a subquery. It always
> returns the same solution set regardless of what else you specify in the
> query. So the answer is that Oracle will first create the view will 10,000
> rows. Then process the rest of the query against it.
>
> That's how I understand it.
> -----BEGIN PGP SIGNATURE-----
> Version: 2.6.3a
> Charset: cp850
>
> iQCVAwUBM1EUosHuiwAtJoldAQF73AQAlSzkGrhAbjZHu+b7+qG2os3pkeY4+EqA
> qRRp9MyQBio/d3GF2ZKAX5L9uuIjXiI+XJsrUxyXmc3dbLTHPSEqiy9fQe2Cypks
> MQYIh96BZaLtaA94F9xk5W6gAy1qNu0uki/n/dGiBGKZQ4mwBElZCAYMIlglJnZJ
> s0KGUBNTb40=
> =O8P0
> -----END PGP SIGNATURE-----

Following is the quote from the ORACLE Concepts Manual in the section entitled "The Mechanics of Views":
When a view is referenced in a SQL statement, ORACLE merges the statement that references the view with the query that defines the view, and then parses the merged statement in a shared SQL area and executes it.

In other words, NOR, it doesn't first create the result of the view and then process the rest of the query against it. Received on Tue Apr 15 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US