Charlotte,
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
HTH
Stéphane Faroult
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 9.2.0.6.
>
> Any suggestions would be greatfully received!
>
> Thank you
> Charlotte
>
>
--
//www.freelists.org/webpage/oracle-l
