Re: Plea for Query Tuning Help

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 :).

On 9/12/06, Charlotte Hammond <charlottejanehammond_at_yahoo.com> 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
>
>
>
> __________________________________________________
> 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
>
>
>

-- 
----------------------------------------
http://www.oracleboliviaspecialists.com/

Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i

10 years of experience from Oracle 7 to Oracle10g and developer 6i

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