Re: Plea for Query Tuning Help

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 12 Sep 2006 20:23:45 +0200
Message-ID: <4506FB31.9020003@roughsea.com>

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

Received on Tue Sep 12 2006 - 13:23:45 CDT