Re: views on views on views

From: Carol Dacko <dackoc_at_gmail.com>
Date: Thu, 26 Mar 2009 11:49:11 -0400
Message-ID: <d65520ea0903260849n1cfe4f6ch329359734e51fc84_at_mail.gmail.com>



Dear Chris,
We feel your pain! It is pervasive in our environment. Security views I can understand, but views, on views, on views, is just a nightmare.

Dan Tow just did a great talk at the HOTSOS Symposium, "*Safe Use of Views? Just Say "No"?* ". Dan Tow also has a featured article on his website called Views, From Forty-Thousand Feet. The link is here:

http://www.singingsql.com/newsletter07.htm

The first paragraph hits the nail on the head:

"

From my perspective of as a SQL tuning consultant, I have to give three cheers for views. Iíd estimate that 75% of my time spent tuning really tricky, non-routine SQL tuning problems is consumed dealing with complex SQL built on complex views Ė these are the problems that can consume hours, even days, of the best experts in the business, assuring continued, lucrative employment for professionals like myself. Of course, if you are on the employerís side, looking for efficiency in a development organization, or if you are an overworked developer on a fixed, non-hourly salary, your perspective may be a little different!
"

Bottom line for any SQL tuning is be succinct. Try to educate the developers, buy hair dye to cover up the gray hair!

Good Luck!
Carol

On Thu, Mar 26, 2009 at 11:27 AM, Stephens, Chris < chris_stephens_at_admworld.com> wrote:

> 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.
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 26 2009 - 10:49:11 CDT

Original text of this message