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 |
--------------------------------------------------------------------
EXISTS
instead ofIN
?