Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Display only part of resultset (ORA 8.1.7)
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.
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 BortelReceived on Sat Dec 27 2003 - 06:47:43 CST