Re: Plea for Query Tuning Help

The use of hint first_rows(n) (if optimizer didn't already use it) would also help in solution proposed by Stephane

On 9/12/06, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
>
> Charlotte,
>
> My guess is that since you have no true condition in your subquery
> (you have indeed a condition on rownum - but rownum is computed on the
> fly, and if I may draw an analogy with triggers it belongs to the 'post'
> rather than 'pre' set), Oracle expects something like a full scan and
> then gets messed up. I presume that it is going for a hash join where
> you would like a nested loop.
>
> I believe that my first try would be something such as:
>
> SELECT NV.*
> FROM NASTY_VIEW NV,
> (SELECT id, rownum rn -- added to prevent any temptation to merge
> FROM my_table
> WHERE ROWNUM=1) X
> WHERE NV.ID = X.id
>
>
> HTH
>
> Stéphane Faroult
>
> Charlotte Hammond wrote:
> > Hi all,
> >
> > I have a very complicated view, NASTY_VIEW.
> >
> > SELECT *
> > FROM NASTY_VIEW
> > WHERE ID = :bind
> >
> > This is fast (< 1 second). CBO cost = 400.
> > Fantastic.
> >
> > However if I try
> >
> > SELECT *
> > FROM NASTY_VIEW
> > WHERE ID = (SELECT id FROM my_table WHERE ROWNUM=1)
> >
> > This takes "forever" (well >> hours). my_table is a
> > simple table and id is the primary key. CBO cost =
> > 22000. Not fantastic. (SELECT id FROM my_table WHERE
> > ROWNUM=1) happens in the blink of an eye if run alone.
> >
> > The execution plans are very different and it looks as
> > if Oracle has attempted to merge the subquery into the
> > top-level select. Similar things happen if I use a
> > join instead of a sub-query.
> >
> > How can I prevent this happening so that it "looks"
> > like the first "fantastic" query? I've tried messing
> > with hints (NO_MERGE and PUSH_SUBQ) but I don't know
> > enough to do so effectively. Statistics are full and
> > up to date. This is Oracle 9.2.0.6.
> >
> > Any suggestions would be greatfully received!
> >
> > Thank you
> > Charlotte
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l