From: yong321@yahoo.com Subject: Re: Is using views always slower than using base tables? Date: 2000/07/07 Message-ID: <8k4mor$lr2$1@nnrp1.deja.com>#1/1 References: <8k2p93$eqv$1@nnrp1.deja.com> <20000707.5320500@p200.nodomain> X-Http-Proxy: 1.1 x72.deja.com:80 (Squid/1.1.22) for client 163.188.121.35 Organization: Deja.com - Before you buy. X-Article-Creation-Date: Fri Jul 07 13:42:52 2000 GMT X-MyDeja-Info: XMYDJUIDy0h8797 Newsgroups: comp.databases.oracle.server X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt) 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.