The use of hint first_rows(n) (if optimizer didn't already use it) would also help in solution proposed by Stephane
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
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 220.127.116.11. > > Any suggestions would be greatfully received! > > Thank you > Charlotte > >