Hi All, Thanks to all who have responded both on and off the list. A couple of people have asked me to post the plan but it is huge (due to NASTY_VIEW) so I'd rather not (at least yet!) as it'd probably be more off-putting than anything else :-) Essentially I just want the WHERE id = on NASTY_VIEW to treat the right value the same if it's a bind or if it's coming from a sub-query. Many good suggestions but no luck yet. I thought I'd post the results to see if anyone can spot anything from them... Here's an even simpler example: The Good case again: SELECT * FROM NASTY_VIEW WHERE id = :bind --> < 1 sec, (CBO=410) Any now some attempts to use a value from a SELECT. 1. Simple select: SELECT * FROM NASTY_VIEW WHERE id = (SELECT :bind FROM dual) --> long time (CBO=17378) 2. Try with a join instead: SELECT * FROM NASTY_VIEW nv, (SELECT :bind id FROM dual) b WHERE nv.id = b.id --> long time (CBO=32731) 3. Try putting in a row number limitation / pseudo-column SELECT * FROM NASTY_VIEW nv, (SELECT :bind id, rownum rn FROM dual WHERE rownum=1) b WHERE nv.id = b.id --> long time (CBO=17388) 4. Try NO_MERGE hint SELECT * FROM NASTY_VIEW WHERE id = (SELECT /*+ NO_MERGE */ :bind FROM dual) --> long time (CBO=17378) (Same plan as 1) (Same result if NO_MERGE hint in the outer SELECT) 5. Try FIRST_ROWS(n) hint SELECT /*+ FIRST_ROWS(1) */ FROM NASTY_VIEW WHERE id = (SELECT :bind FROM dual) --> long time (CBO=6030) (Best yet but still very slow compared to good case; other values of n were the same or worse) 6. Try hidden parameter tweaking ALTER SESSION SET "_UNNEST_SUBQUERY"=FALSE; --> same as 1 ALTER SESSION SET "_COMPLEX_VIEW_MERGING"=FALSE --> same as 1 Many thanks again for all your time. Charlotte > On 9/12/06, Charlotte Hammond > <charlottejanehammond@xxxxxxxxx> wrote: > > > > 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. > > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- //www.freelists.org/webpage/oracle-l