서브쿼리 HASH_SJ vs FILTER

1. HASH_SJ 으로 실행계획 제어

 

select /*+ gather_plan_statistics test_20261026 */ deptno, dname, loc
from dept d
where exists ( select /*+ hash_sj */1
               from emp e
               where e.deptno = d.deptno);

select sql_id, plan_hash_value, last_load_time, sql_text
from v$sql
where sql_text like '%select /*+ gather_plan_statistics test_20261026 */%';

select * from dbms_xplan.display_cursor('1h42rah3s8nux', null, 'advanced allstats last');			   
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |      3 |00:00:00.01 |      12 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      3 |    69 |     6   (0)| 00:00:01 |      3 |00:00:00.01 |      12 |  1399K|  1399K| 1098K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |    42 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

 

 

 

2. FILTER 방식으로 실행계획 제어

-- no_unnest 힌트 줘야 필터로 풀림
-- opt_param('_always_semi_join','off')는 단순히 semi_join만 끄는 힌트이나 hash_sj 힌트 사용하면 hasj semi join 으로 풀림

select /*+ gather_plan_statistics opt_param('_always_semi_join','off') test_20261026 */ deptno, dname, loc
from dept d
where exists ( select /*+ no_unnest */1
               from emp e
               where e.deptno = d.deptno);
			   
select sql_id, plan_hash_value, last_load_time, sql_text
from v$sql
where sql_text like 'select /*+ gather_plan_statistics opt_param%';            

select * from dbms_xplan.display_cursor('gch5u4p29nj3d', null, 'advanced allstats last');	
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     9 (100)|          |      3 |00:00:00.01 |      31 |
|*  1 |  FILTER            |      |      1 |        |       |            |          |      3 |00:00:00.01 |      31 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      2 |     6 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |      24 |
---------------------------------------------------------------------------------------------------------------------