How can I choose different hints for different joins for a single table in a query hint?
- by RenderIn
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?
