Forcing Oracle to use hash join for a subquery

join;optimizationoracle

I have a query that looks like

SELECT *
FROM table0
WHERE id IN (SELECT id FROM table1 JOIN table2)

Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and takes hours. I'm trying to figure out whether I can hint it to use HASH instead, but don't understand which hint and where to use. I tried sticking HASH_SJ and HASH_AJ in various places and it didn't help…

Best Answer

I'd try using a WITH clause in combination with the MATERIALIZE hint to force materialization of the subquery first. Something like this:

WITH x as (select /*+ MATERIALIZE */
           from [your subquery join])
SELECT *
FROM table0, x
WHERE table0.id =x.id
Related Question