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

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

Re: A technical question about VIEWS

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/03/27
Message-ID: <333ADA60.43FE@iol.ie>#1/1

This question has been asked before. To recap: If the Where clause applied to (extrinsic to) the view can be combined with the Where clause used in its definition, (intrinsic) then the resultant execution will be identical to a single statement written with a combined Where clause: no degradation.

This is not possible if the view contains <distinct>, <group by> or <having> clauses or if the elements referred to in the extrinsic Where clause are not simple mappings to columns of the underlying tables. In such a case the entire row set defined by the view will be retrieved to be qualified by the extrinsic where clause. This may result in severe degradation as compared to a simple query, but *such a query is not simple*.

In such cases, it *may* be better to use some form of multi-cursor (PL/SQL?) processing, but not necessarily.

Hope this helps.

Chrysalis.

NNOOR wrote:
>
> 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)
  Received on Thu Mar 27 1997 - 00:00:00 CST

Original text of this message

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