Plea for Query Tuning Help

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



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: