Re: usage of views

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 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.