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

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

    ....

Possibly with a swap_join_inputs() or no_swap_join_inputs() to control which data sets become the build and probe sets on the hash joins.

-- 
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


<mccmx_at_hotmail.com> wrote in message 
news:1156230628.338372.117350_at_h48g2000cwc.googlegroups.com...


>> >

>> Look up 'Specifying global table hints' in the Performance Tuning

>> Guide.

>>

>> Jaap.

>

> Thanks for the feedback - I've been playing around with global

> hints....

>

> I can get a very basic example working but what I want is subtley

> different.

>

> Global hints allow me to influence a join method for a table inside a

> view, but what I want to do is to influence the optimizer to use a HASH

> join when joining to a table which is merged into the top level query.

>

> e.g. the view joins table A and table B, and the calling (outer) query

> joins table X and the view.  Table B is merged into the outer query so

> I want to get the optimizer to join table X to table B via a HASH join.

> I can't seem to get this to work via a global hint.

>

> Do you know if this is achievable...?

>

> Matt

>