Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is using views always slower than using base tables?

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

From: <yong321_at_yahoo.com>
Date: 2000/07/07
Message-ID: <8k5i28$7hv$1@nnrp1.deja.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US