The view is basically a SQL select command which needs to be tuned like any
other command. Stats need to be up-to-date, indexes need to be set up
correctly, the SQL needs to be correctly defined, etc. Also the where
clauses that may be applied to the view by the app/users also need to be
considered. If the most efficient view ever written is joined badly to a big
table, then the perception is that the view doesn't perform...
Yes developers should work on the base tables as a general rule, but views
are great for fixing specific joins that are used a lot (e.g. joining orders
to order items), and correctly tuned should have negligible overhead over
the base tables. We all know the optimizer can do some strange things at
times, but there is normally an underlying reason for this, be it stats
related, SQL related, bug related, or whatever.
Horses for courses!!
John.
-----Original Message-----
From: Yoav Givon
[mailto:oracledba-ezmlmshield-x59785974.[Email address protected]
Sent: 03 January 2007 20:28
To: LazyDBA Discussion
Subject: RE: Why Views are slow
MV's = snapshots are different breed so they are not relevant to our
discussion. Snapshoot holds the physical data set so fetching the data
will be always faster then running the same SQL on the master table.
As to views in general ..
Its all come to the optimizer . From pure SQL point of view it is always
better create a statement that would relate to the base tables .
Programmers should use only base tables as they should be familiar with
the data structures and as such they can create the efficient SQL .For
convenience and ease of use especially for end users, views are
created with joins and where clauses that would gather the information
from different base tables related to specific business issues.
Running the SAME sql using a view instead of using the base tables will
In many cases result with more execution steps which might end up with
more costly , less efficient plan .
Yoav
-----Original Message-----
From: Brian Imburg
[mailto:oracledba-ezmlmshield-x63487509.[Email address protected]
Sent: Wednesday, January 03, 2007 9:00 PM
To: LazyDBA Discussion
Subject: RE: Why Views are slow
Look into a materialized views. That will be faster because it holds
the
data and doesn't have to gather it all up again. it may slow process
that
feed the base tables down so watch out for that. It will be a trade off
at
any rate.
Brian Imburg
Senior SAP Basis Consultant
(804) 782-8320
(804) 402-4369
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit
written
agreement or government initiative expressly permitting the use of
e-mail
for such purpose.
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------
"Jeff Ferrel "
<oracledba-ezmlms
hield-x10264957.x
To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
01/03/2007 01:51
PM
Subject
RE: Why Views are slow
I have a developer that has run into this as well. I'd like to see more
discussion on this, or if someone has access to any articles on this
topic, please send them my way!
Thanks to all,
Jeffrey C. Ferrel
DMS III
State of Nevada
775.687.9327
[Email address protected]
-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x87208536.[Email address protected]
Sent: Wednesday, January 03, 2007 1:11 AM
To: LazyDBA Discussion
Subject: RE: Why Views are slow
Views are not slow, but the query that constitutes the view may be. Try
running the select statement that makes the view and then select from
the
view itself. Don't forget to apply the usual expected where clauses. The
results should be pretty similar. Run some explain plans to check this.
John.
-----Original Message-----
From: Sadiq Noorulla
[mailto:oracledba-ezmlmshield-x33925902.[Email address protected]
Sent: 03 January 2007 03:21
To: LazyDBA Discussion
Subject: Why Views are slow
Hi Oracle Experts,
I found quires using base table is Fast
same after creation of view is Becoming Slow
Any Reason
Thanks in Advance
DISCLAIMER:
This message contains privileged and confidential information and is
intended only for the individual named. If you are not the intended
recipient you should not disseminate,distribute,store,print, copy or
deliver
this message. Please notify the sender immediately by e-mail if you have
received this e-mail by mistake and delete this e-mail from your system.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page