forcing Oracle to use hash join for a subquery

Question

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...

• a1ex07: Did you try EXISTS instead of IN ?
• MK01: @a1ex07 I didn't. What is the difference?
• Vincent Malgrat: Can you provide a simple test case with CREATE statements so that we can reproduce your problem?
• MK01: @VincentMalgrat not really. As a matter of fact the same query results in "correct" execution plan on another Oracle instance...

Answer 1

You can use hints with subqueries, after having them qualified with the QB_NAME hint for example.

In this case however a simple USE_HASH hint should be ok. Here's my setup:

create table t0 (id integer primary key, pad char(500));
insert into t0 select rownum, 'x' from dual connect by level <= 2000;
create table t1 (t1_id integer primary key, id integer references t0, pad char(500));
insert into t1 select rownum, round(rownum/2), 'x' from dual connect by level <= 100;
create index i0 on t1(id);
create table t2 (t2_id integer primary key, t1_id integer references t1, pad char(500));
insert into t2 select rownum, round(rownum/2), 'x' from dual connect by level <= 100;
create index i1 on t2(t1_id);
exec dbms_stats.gather_table_stats(user, 'T0', cascade=>true);
exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true);
exec dbms_stats.gather_table_stats(user, 'T2', cascade=>true);

With this setup the following query runs a NESTED LOOP join:

SQL> EXPLAIN PLAN FOR
 2 SELECT * FROM t0
 3 WHERE id IN (SELECT id
 4 FROM t1 JOIN t2 ON t1.t1_id = t2.t1_id
 5 WHERE t2.pad LIKE :x);
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 2585 | 37 |
| 1 | NESTED LOOPS | | 5 | 2585 | 37 |
| 2 | VIEW | VW_NSO_1 | 5 | 65 | 7 |
| 3 | SORT UNIQUE | | 5 | 2550 | |
| 4 | HASH JOIN | | 5 | 2550 | 7 |
| 5 | TABLE ACCESS FULL | T1 | 100 | 600 | 3 |
| 6 | TABLE ACCESS FULL | T2 | 5 | 2520 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID| T0 | 1 | 504 | 1 |
| 8 | INDEX UNIQUE SCAN | SYS_C00746321 | 1 | | |
-------------------------------------------------------------------------------

With the USE_HASH hint:

SQL> EXPLAIN PLAN FOR
 2 SELECT /*+ USE_HASH (t0) */ * FROM t0
 3 WHERE id IN (SELECT id
 4 FROM t1 JOIN t2 ON t1.t1_id = t2.t1_id
 5 WHERE t2.pad LIKE :x);
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display);
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 2585 | 44 |
| 1 | HASH JOIN SEMI | | 5 | 2585 | 44 |
| 2 | TABLE ACCESS FULL | T0 | 2000 | 984K| 23 |
| 3 | VIEW | VW_NSO_1 | 5 | 65 | 7 |
| 4 | HASH JOIN | | 5 | 2550 | 7 |
| 5 | TABLE ACCESS FULL| T1 | 100 | 600 | 3 |
| 6 | TABLE ACCESS FULL| T2 | 5 | 2520 | 3 |
--------------------------------------------------------------------

• MK01: Thanks. QB_NAME sounds interesting. It doesn't work for me, but I suspect now that something is screwed up in the Oracle config because same query on the same tables on another server works "correctly" -- i.e. doesn't go for NL.
• Vincent Malgrat: There are so many things that can change between two instances (and sometimes even in the same instance) with the same logical data that can produce different plans. Don't assume that your install is screwed because two different servers produce different plans. The statistics for example could be just a little bit different and thus pushing one instance to run a nested loop while the other runs a hash join. This is perfectly normal.
• Vincent Malgrat: Most of the time, the optimizer will make the right choice. Sometimes it doesn't and in that case one of the method available is hints. Once again if you provide us with a simple test case I can show you how hints work with or without subqueries.

Answer 2

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