Re: Is using views always slower than using base tables?

yong321_at_yahoo.com wrote:
>
> Thanks, Jerry. But it's not that simple. Indeed the SQL for the view is
> parsed. But how often do you only select * from the_view? Rarely.
> Almost every time you use a view, there's some other condition you add
> to the query (select a.some_col, b.view_col from other_table a,
> the_view b where ...) When Oracle executes this query, it finds the
> view in it, so it fetches the parsed form of the_view, then parses your
> query, combining it with the_view. Therefore, whether using complicated
> views is slower depends on whether it's faster to parse your ad-hoc
> part of the query and combine with the already parsed view than one-
> phase parse of the entire query. Knowledge from only the Oracle Concept
> manual is probably not enough to answer this question. I asked this
> question on an Oracle DBA forum. Got an answer like yours, which I
> believe is a common misconception.
>
> Regards,
>
> Yong Huang
>
> (yong321_at_yahoo.com)
> (http://www.stormloader.com/yonghuang/)
>
> In article <20000707.5320500_at_p200.nodomain>,
> Jerry Gitomer <jgitomer_at_erols.com> wrote:
> >
> > I would think that in an OLTP environment views might be faster since
> > they have already been parsed and are stored in the database.
> >
> > In an OLAP environment it probably won't make any difference since
> > execution time far outweighs parse time.
> >
> > >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> >
> > On 7/6/00, 3:13:33 PM, yong321_at_yahoo.com wrote regarding Is using=20
> > views always slower than using base tables?:
> >
> > > Is using views always slower than using base tables, at least
> > > theoretically? My developers ask me to create views for them. I told
> > > them views are used for two purposes: for convenience so that you=20
 don't
> > > have to remember or type in all the base tables and their column=20
 names;
> > > for security so as to hide some column or table names. My developers
> > > are not end users. They don't need the convenience and they should
 be
> > > allowed full select privilege on base tables. For performance
 reason,
> > > views are always slower than or indistinguishably the same as using
> > > base tables. Is this correct?
 

> > > Yong Huang
> > > yong321_at_yahoo.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

...but one of the key aspects of a quick database is not having to re-parse SQL's which are the SAME. Whilst a little more recursive SQL will be required for new SQL's involving the view, once they're in the shared area, they'll run just as fast as those against a base table.

If you're developers are running countless different varieties of SQL's (eg not using bind variables) then whether you use tables or views, your database is in for a rough ride. If they are re-using SQL's well, then having a view won't be an overhead.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse