From: Connor McDonald Subject: Re: Is using views always slower than using base tables? Date: 2000/07/07 Message-ID: <3965B01B.2F0A@yahoo.com>#1/1 Content-Transfer-Encoding: 7bit References: <8k2p93$eqv$1@nnrp1.deja.com> <20000707.5320500@p200.nodomain> <8k4mor$lr2$1@nnrp1.deja.com> To: yong321@yahoo.com Content-Type: text/plain; charset=us-ascii X-Complaints-To: newsabuse@remarq.com X-Trace: 962990764 LGTBT6QCL3625D501C uk21.supernews.com Organization: RemarQ http://www.remarQ.com Mime-Version: 1.0 Reply-To: connor_mcdonald@yahoo.com Newsgroups: comp.databases.oracle.server yong321@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@yahoo.com) > (http://www.stormloader.com/yonghuang/) > > In article <20000707.5320500@p200.nodomain>, > Jerry Gitomer 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@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@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