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

Question

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?

Answer 1

On SQL Server, you would do a hash joint hint like this

SELECT * FROM table1 t1
INNER hash join table2 t2 ON  t1.id = t2.id

and you can also supply an index hint

select * from table1 t1
inner  join table2 t2 with (index( bla))  on  t1.id = t2.id

Don't know what the syntax looks like in Oracle, BTW why are you using old style joins? Are you still on 8i?

Answer 2

First of all, using such hints should be the last resort, not a normal way of writing queries. Most of the time you should just ensure that optimiser stats are up to date and let the CBO work out the optimum path for itself - that's its job!

The INDEX hint can specify the name of the index you want to use like this:

SELECT /*+ INDEX (A, A_X_IDX) */ *
...

(assuming the index on A.X is called A_X_IDX).

You can't tell Oracle use use the index on A.X and use a hash join to table A in the same statement, that makes no sense. However, you can (if you must) specify the access path for each table something like:

SELECT /*+ INDEX (A, A_X_IDX) INDEX(B, B_Y_IDX) USE_HASH(C) */ *

But to reiterate, it should be rare to need to do this. Oracle have invested millions of dollars and manhours into developing the CBO, so why effectively switch it off?