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: Display only part of resultset (ORA 8.1.7)

Re: Display only part of resultset (ORA 8.1.7)

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Sat, 27 Dec 2003 13:47:43 +0100
Message-ID: <bsjug1$cpl$1@news4.tilbu1.nb.home.nl>


Ryan Gaffuri wrote:
> Frank van Bortel <fbortel_at_nescape.net> wrote in message news:<bscmc1$q5u$1_at_news3.tilbu1.nb.home.nl>...
>

>>Galen Boyer wrote:
>>
>>
>>>On Tue, 23 Dec 2003, fbortel_at_nescape.net wrote:
>>>
>>>
>>>
>>>>Don't use the view! Use the underlying query instead;
>>>>it'll give Oracle the opportunity to use a better plan.
>>>>
>>>>Rule # 7: do not use views in joins (modified)
>>>
>>>
>>>Care to explain?
>>
>>A view itself has an underlying query. I have seen too much
>>code, based on joins of views leading to, eerrr, sub-optimal
>>performance.
>>
>>Rule #7 of some Basic Performance Tips document stated not to
>>code joins over views, but use the underlying statement, or
>>create a new view.
>>The modification is mine: there's no join here.

>
>
>
> What tips doc is it? Whoever wrote it is wrong and clearly an idiot.
> Its not even close to right. Second off, that is not what the guy is
> doing here anyway. His technique is generic and standard.
>
>
> to the original post. Check your explain plan. Odds are, your query is
> optimized for 'all_rows' so your doing some full table scans. Include
> a 'first_rows' hint or change it at the session level. This should
> force a nested loops join which is useful for what you are trying to
> do.
>

Which can easily break some optimization done elsewhere. Back to the earlier advise: don't use the view; either don't use at all, or create a new one - optimized for THIS query. And that is the full background of the rule (of thumb, I might add): be careful when you use views - they may be doing something not optimized for your query. Review the views, explain plan, etc, to make sure your code performs.
Else - don't use this view; create another, or return to the underlying code, and optimize that for THIS occasion.

I still think it's a sensible thing to do; and the creator surely wasn't and idiot.

> Also, include your rownum <= max_value in your innermost query. This
> way you get the smallest 'result back'. your doing an order by on ALL
> the records in your query. This is expensive and a good chance you are
> writing to the temp tablespace in order to perform the query.
>
> I cant tell unless you give me a trace. 9i 10046 with a tkprof will
> tell you if your writing to the temp tablespace in your wait events.

-- 
A prosperous 2004,
Regards,
Frank van Bortel
Received on Sat Dec 27 2003 - 06:47:43 CST

Original text of this message

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