Re: usage of views

Hi,

I remember reading somewhere that the way a reference to a view is handled is that the view is first computed. For instance if I define a view that selects all the sales for every single region and then reference it in my query to restrict the data for a particular region, the execution first gets all the data for all the regions (thereby taking more time/resources) and then restricts the data returned by the condition I put in.

While I remember reading it somewhere, I cannot quote the source. I will try to get it.

Meanwhile thanks for your replies and thoughts.

Ajay
  "Parvinder Singh Arora" <parora_at_questone.com> wrote in message news:38CF836E.9AA06BB4_at_questone.com...     

  Atta ur-Rehman wrote:

    hi Parvinder,
    i still am convinced that there are no two quereis when you do a select

    from a view. by defition, if you want to restrict some records off a

    view, the restricting where clause could always be merged with the view

    definition to get a *SINGLE* sql statement. below i'd quote an excerpt

    from Oracle Concept Manual, Chapter 8, Schema Objects:

    <quote>
    The Mechanics of Views

    Oracle stores a view's definition in the data dictionary as the text of

    the query that defines the view. When you reference a view 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. Oracle parses a statement that

  so doesnt it takes time in parsing the whole query ???? the only advantage is in terms of memory for SQL area coz it doesnt parses that in a new SQL area but still its gonna take some amount of time to parse the new query (after mering the query on the view and the view's "Stored Query")           

    references a view in a new shared SQL area only if no existing shared

    SQL area contains an identical statement. Therefore, you obtain the     benefit of reduced memory usage associated with shared SQL when you use

    views
    </qoute>
    hope that clears my stand point.

    regards,

    :) ATTA     In article <38CF316F.84EA0E2A_at_questone.com>,     parora_at_questone.com wrote:
> This is a multi-part message in MIME format.
> --------------5A2CFFB92D26F8BD3BABEA58
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Atta ur-Rehman wrote:
>
> > hi ajay,
> >
> > no, using a view instead of a plain sql statement doesn't have
to

    run
> > any slower. in fact, because the query definition of a view is
 stored

> > in a parsed and compiled form, it would run faster 'cas you're
 saving

> > the parsing time here. well, of course, if your view doesn't
need

    to be
> > recompiled for any reason, that is.
> >
> > i look at a view as a 'stored query', a convinient of way of
 storing

>
> okay fine a view is a stored query so when i do something like
this

    select
> * from xyz where b.2 = 'some value'; (where xyz is a query)
 .....then

> obviously the stored query of view will be executed first and then
on

    the
> resultant set a query will be fired ...view is a filter and from
 that

> filter more records are being filtered out ...obviously its gonna
be

    slow
>
> ~Parvinder
>
> >
> > long, multi-table queries that need to be done pretty
frequently.

    or a
> > way of restrictng veiw of a table, or multiple joined tables,
 either

> > horizantolly, vertically or both ways. the execution speed in
this

    case
> > takes the secondry importance, if at all.
> >
> > regards,
> >
> > :) ATTA
> >
> > In article <8an3fj$4j8s$1_at_newssvr03-int.news.prodigy.com>,
> > "Madhuri Bannai" <bannai_at_prodigy.net> wrote:
> > >
> > > Hello,
> > >
> > > Will the usage of a view to substitute for a query make it run
 any
 faster ?

> > > If so, why ?
> > >
> > > For instance if I do a select
> > >
> > > select a.1, a.2, b.3, b.4
> > > from a, b
> > > where a.1 = b.1
> > > and a.2 = b.2;
> > >
> > > Instead I define a view
> > >
> > > create view xyz as select
> > > a.1, a.2, b.2, b.3, b.4
> > > from a, b
> > > where a.1 = b.1;
> > >
> > > and then do a select * from xyz where b.2 = 'some value';
> > >
> > > Does the second scenario make it run any faster ? My
 contention is
 that it

> > > should be slower. Any views, pointers welcome.
> > >
> > > TIA
> > >
> > > ajay
> > >
> > >
> > --
> > getting the meanin' of data...
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> --------------5A2CFFB92D26F8BD3BABEA58
> Content-Type: text/x-vcard; charset=us-ascii;
> name="parora.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Parvinder Singh
> Content-Disposition: attachment;
> filename="parora.vcf"
>
> begin:vcard
> n:Arora;Parvinder Singh
> tel;fax:91-020-5678813
> tel;home:91-020-6052855
> tel;work:91-020-5678832/33
> x-mozilla-html:TRUE
> url:www.questone.com
> org:Technologic;DBACESS
> adr:;;425/B Rasta Peth PUNE - 1;PUNE;Maharashtra;411004;INDIA
> version:2.1
> email;internet:parora_at_questone.com
> fn:Parvinder Singh Arora
> end:vcard
>
> --------------5A2CFFB92D26F8BD3BABEA58--
>
>

    --
    getting the meanin' of data...

    Sent via Deja.com http://www.deja.com/     Before you buy.

--