Re: Display only part of resultset (ORA 8.1.7)

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.

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. Received on Fri Dec 26 2003 - 11:12:18 CST