Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is using views always slower than using base tables?
In article <3965B01B.2F0A_at_yahoo.com>,
connor_mcdonald_at_yahoo.com wrote:
> ...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
I can think of two extremes. On one extreme, select * from the_view is definitely, absolutely faster than if there's no view created. On the other extreme, for a very complicated view, the_view, this query
select the_view.one_column, a.acol, b.bcol, c.ccol... from the_view, a, b, c... where {a lot of join conditions in which the_view only occurs once}
is sure to run much slower than if I use all base tables, because only one column is used out of the_view.
So the answer is not a simple one. Whether I need to create the 100- line-definition view for my developers depends on how they usually use it. If every time they only use 1 or 2 columns out of it, combined with a lot of out-of-view (ad-hoc) table column selects and where conditions, then they'd better not use this view. But if the view pretty much satifies their query requirement with just a little addition to the select list and to the where clause, then the view is preferred.
-- Yong Huang (yong321_at_yahoo.com) (http://www.stormloader.com/yonghuang/) Sent via Deja.com http://www.deja.com/ Before you buy.Received on Fri Jul 07 2000 - 00:00:00 CDT