Re: Plea for Query Tuning Help

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: charlottejanehammond@xxxxxxxxx
  • Date: Tue, 12 Sep 2006 20:23:45 +0200

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


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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006