select * from test a where object_id in (select department_id
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp));
SQL> select count(*) from test;
COUNT(*)
----------
2319328
SQL> select count(*) from dept_1;
COUNT(*)
----------
4194304
SQL> select count(*) from employees_1;
COUNT(*)
----------
3506176
SQL> (select department_id
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp)) 2 3
4 ;
DEPARTMENT_ID
-------------
10
20
SQL> set linesize 200
SQL> set pagesize 200
SQL> explain plan for select * from test a where object_id in (select department_id
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp)); 2 3
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
Plan hash value: 717021958
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 9747 (7)| 00:01:57 |
| 1 | VIEW | VM_NWVW_2 | 1 | 207 | 9747 (7)| 00:01:57 |
| 2 | HASH UNIQUE | | 1 | 226 | 9747 (7)| 00:01:57 |
|* 3 | HASH JOIN | | 14M| 3134M| 9264 (2)| 00:01:52 |
|* 4 | HASH JOIN | | 46 | 10212 | 7400 (1)| 00:01:29 |
| 5 | TABLE ACCESS FULL | DEPT_1 | 2 | 6 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST | 2511K| 524M| 7389 (1)| 00:01:29 |
| 7 | INDEX FAST FULL SCAN| EMPLOYEES_1_IDX1 | 3508K| 13M| 1819 (1)| 00:00:22 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
4 - access("OBJECT_ID"="DEPARTMENT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.
此时子查询被展开,直接dept_1和test进行了HASH JOIN 两表记录数分别为4194304和2319328 SQL根本跑不出结果
利用NO_UNNEST,阻止子查询展开:
SQL> select * from test a where object_id in (select department_id
from hr.dept_1 dept
where department_id IN (select /*+ NO_UNNEST */ department_id from hr.employees_1 emp));
2 3
64 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1506439343
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 5060 | 7403 (1)| 00:01:29 |
|* 1 | HASH JOIN RIGHT SEMI| | 23 | 5060 | 7403 (1)| 00:01:29 |
| 2 | VIEW | VW_NSO_1 | 1 | 13 | 6 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| DEPT_1 | 2 | 6 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMPLOYEES_1_IDX1 | 2 | 8 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST | 2511K| 495M| 7389 (1)| 00:01:29 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="DEPARTMENT_ID")
3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."EMPLOYEES_1" "EMP"
WHERE "DEPARTMENT_ID"=:B1))
5 - access("DEPARTMENT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
80269 consistent gets
48802 physical reads
0 redo size
4119 bytes sent via SQL*Net to client
463 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64 rows processed
此时SQL很快跑完
