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

Look at the plan, it'll tell you the answer.

An example might be something like (I've just made this up):

SELECT
  HASH JOIN
    HASH JOIN
      TABLE FULL SCAN table1
      TABLE FULL SCAN table2
  TABLE FULL SCAN table3

This sample plan involves a scan through table1, hashing its contents as it goes; scans through table2, hashes the results of the join into a second hash, then finally scans table3.

There are other plans it could choose from.

If table1 is the biggest table, and the optimizer knows this (due to stats), it probably won't drive from it though.

Answer 2

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.