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@xxxxxxxxxxxx> 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



Other related posts: