Does Oracle re-hash the driving table for each join on the same table columns?

Question

Say you've got the following query on 9i:

SELECT /*+ USE_HASH(t2 t3) */
* FROM
table1 t1    -- this has lots of rows
LEFT JOIN table2 t2 ON t1.col1 = t2.col1
    AND t1.col2 = t2.col2
LEFT JOIN table3 t3 ON t1.col1 = t3.col1
    AND t1.col2 = t3.col2

Due to 9i not having RIGHT OUTER HASH JOIN, it needs to hash table1 for both joins. Does it re-hash table1 between joining t2 and t3 (even though it's using the same join columns), or does it keep the same hash information for both joins?

Answer 1

It would need to rehash since the second hash would be table3 against the join of table1/table2 rather than against table1. Or vice versa.

For example, say TABLE1 had 100 rows, table2 had 50 and table3 had 10. Joining table1 to table2 may give 500 rows. It then joins that result set to table3 to give (perhaps) 700 rows.

It won't do a join of table1 to table2, then a join of table1 to table3, then a join of those two intermediate results.