Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Tuning question
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