forcing Oracle to use hash join for a subquery 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...
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