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

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

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