Re: Can you hint a table from a 'Merged' view...?

<mccmx_at_hotmail.com> wrote in message
news:1156323071.848238.160700_at_75g2000cwc.googlegroups.com...
>> You need to look at leading(), with a full
>> batch of hints. In the example you give
>> something like:
>>
>> select
>> /*+ leading (X, v1.B, v1.a) use_hash(v1.b) use_hash(v1.a) */
>> from
>> X,
>> v1
>> where
>> ....
>
> Do I have to use the LEADING hint for this to work...? I'm not
> interested in the join order of the query, that seems to be OK. The
> optimizer is joining into V1.B at a suitable time, but I just want that
> join to be a HASH join instead of an NL join.
>
> I've played around with the leading hint but it appears that the
> optimizer always ignores any instructions related to order (i.e.
> LEADING or ORDERED hints) unless I use a NO_MERGE hint for the V1 view.
>
> Matt
>

If you put ANY hints into a query, you should expect to have an average of at least one hint per table, possibly getting close to an average of two per table; otherwise you are not fixing an execution path, you may simply happen to get the desired path by accident for a little while.

If you get the order you expect from a leading() hint when you have views in line with NO_MERGE hints, then you are getting the join order by accident, not because of the leading() hint.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html