How can I choose different hints for different joins for a single table in a query hint?

Posted by RenderIn on Stack Overflow See other posts from Stack Overflow or by RenderIn
Published on 2010-03-26T15:08:06Z Indexed on 2010/03/26 15:53 UTC
Read the original article Hit count: 371

Suppose I have the following query:

select * from A, B, C, D
where A.x = B.x
and B.y = C.y
and A.z = D.z

I have indexes on A.x and B.x and B.y and C.y and D.z

There is no index on A.z.

How can I give a hint to this query to use an INDEX hint on A.x but a USE_HASH hint on A.z? It seems like hints only take the table name, not the specific join, so when using a single table with multiple joins I can only specify a single strategy for all of them.

Alternative, suppose I'm using a LEADING or ORDERED hint on the above query. Both of these hints only take a table name as well, so how can I ensure that the A.x = B.x join takes place before the A.z = D.z one? I realize in this case I could list D first, but imagine D subsequently joins to E and that the D-E join is the last one I want in the entire query.

A third configuration -- Suppose I want the A.x join to be the first of the entire query, and I want the A.z join to be the last one. How can I use a hint to have a single join from A to take place, followed by the B-C join, and the A-D join last?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about optimizer-hints