Fw: Re: views on views on views
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 26 Mar 2009 16:22:08 -0700 (PDT)
Apologies, my original post that did not go through as an option that others
have pointed out as well...
Kellyn
--- On Thu, 3/26/09, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:
From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
Subject: Re: views on views on views
To: oracle-l@xxxxxxxxxxxxx, chris_stephens@xxxxxxxxxxxx
Date: Thursday, March 26, 2009, 9:51 AM
As I'm a newly acquired DBA to a company that has survived a long time without
any DBA at all- I am having this same battle. The developers here just
followed the poor model that was in place by the vendor where view called view
upon view with the similar results that you have described below.
My take has been to educate my developers, who do have an honest desire to do
the right thing and to create a quality product that will offer a long-term
solution. They are open to learning new ways to providing the customer the
data they need without turning to views.
Just showing them the elapsed time, the optimizer cost and buffer gets from
V$SQL is usually enough to turn them off to views calling views...
As for the views that are already in place? Many of them I've been able to
gather the signoffs from the users that they are utilized for reporting and
could be turned into materialized views with a synonym pointing towards the
original view name, dropping the original view. This ensured that the
optimizer was not hindered by the challenges of views calling views, creating
aggregated data that met the needs of several views in one call and that was
refreshed by rowid or primary key vs. running the statement 25-100 times per
day by individual users.
The developers have been sold on this concept, but know that the word view is a
four letter word in my vocabulary, literally and figuratively... :)
Kellyn
--- On Thu, 3/26/09, Stephens, Chris <chris_stephens@xxxxxxxxxxxx> wrote:
From: Stephens, Chris <chris_stephens@xxxxxxxxxxxx>
Subject: views on views on views
To: oracle-l@xxxxxxxxxxxxx
Date: Thursday, March 26, 2009, 9:27 AM
I find myself constantly struggling with developers constructing views on top
of views on top of still other views. I understand this makes their job easier
in the short run but whenever problems arise that involve this kind of thing,
my blood pressure goes way up trying to untangle the web. I’ve raised concerns
in the past but that has gotten no traction and the practice continues.
I’ve raised the following concerns:
· Potentially cripples the optimizer
· When there is a problem with the set returned by the end-view, it is
a nightmare trying to figure out where the problems is happening
· Makes it more likely that redundant filters are in place
There are probably more but those three are enough for me.
Do other people on the list have to contend with this issue?
Have you gotten the developers to agree to limiting their use of views in this
way?
Have any of you successfully dealt with the issue and if so, what arguments did
you make that allowed for that?
Thanks!
chris
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is
addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of
this message is not the intended recipient or the employee or agent responsible
for delivering this message to the intended recipient, you are hereby notified
that any dissemination, distribution or copying of this communication is
strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply.
Other related posts:
