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

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 onephase  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. Received on Fri Jul 07 2000 - 00:00:00 CDT