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: SQL Tuning question

Re: SQL Tuning question

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Fri, 17 Jan 2003 19:07:41 GMT
Message-ID: <1wYV9.79$4Z.8215624@newssvr15.news.prodigy.com>


Ryan Gaffuri wrote:
> First off, my explanation is going to be a bit vague since I dont have
> alot of details. However, I hope its enough to get an opinion.
>
> I was talking to a couple of DBA's and on their project they stated
> that they often have to use a hint to force the RBO. They said that
> this happens when querying off of layered views. My best guess on why
> this is so, is that the analyzer will gather statistics off the
> tables, these statistics may not be entirely valid for views made on
> top of views, so when the CBO is used, it is using poor statistics.
>
> I have seen in some books that if this happens you should consider
> re-writing your queries so that they directly access the tables. This
> could be very time consuming.
>
> Just looking for some general comments on this?
No matter how much time it takes to do something right the first time, it will always take even more time to fix something done wrong the first time.

Our developers here suffered from the same thing you're describing. It's easy to forget what lies behind the view. We had several layered views, each of which had joins. But every developer wanted to select from member_bean (maybe because it has such a cool, java-sounding name) instead of the members table. Of course, the member_bean view pulled address information and even the language they spoke from lookup tables. Of course, the app didn't want to display any of that joined info ... just the name and email address ... but by using the view, they did the joins for nothing.

If you can easily overlook what's sitting just one layer down in a view, imagine how difficult it is to remember what's happening in every layered view. Fortunately, Oracle tries to help by unwrapping layered views during the parsing phase ... but Oracle is not a developer mind reader. Received on Fri Jan 17 2003 - 13:07:41 CST

Original text of this message

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