Hi Did you tried SELECT * FROM NASTY_VIEW a,(SELECT id FROM my_table WHERE ROWNUM=1) b WHERE a.ID = b.id
There is an extreme and very rarely necessary trick, is to move the query you get from nasty_view to a temporary table and then do the other query. I do this when the optimizer start to get confused and the query is toooooo complex to analyze it, and I don-t have too much time to analyze complex queries :).
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 22.214.171.124.
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 -- //www.freelists.org/webpage/oracle-l
-- ---------------------------------------- http://www.oracleboliviaspecialists.com/
Oracle Certified Profesional 9i 10g Orace Certified Professional Developer 6i