선행 Table의 Row가 수행 Table의 Row와 Match되기만 하면 즉각 Join 조건이 만족된 것으로 간주하고 해당 Row에
대해서는 더 이상의 탐색을 진행하지 않는다. 따라서 보다 효율적이다.
Exists와 In Operation의 효율적인 처리를 위한 고안된 Join 방식이다.
주로 Hash Join(Hash Semi Join)의 형태나 Nested Loops Join(Nested Loops Semi Join)의 형태로 구현된다.
Sort Merge Semi Join 또한 이론적으로는 발생 가능하다.
select count(*)
from
(
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select t2.c1 from t2)
)
;
@stat
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.03 | 31 | 1 | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 999 | 10 (10)| 1000 |00:00:00.04 | 31 | 1 | 1517K| 1517K| 1493K (0)|
| 3 | INDEX FAST FULL SCAN| T2_N1 | 1 | 1000 | 2 (0)| 1000 |00:00:00.03 | 7 | 1 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.04 | 24 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
Filter Operation이 사용된 경우 일량이 31 Block에서 20,038 Block으로 극단적으로 증가한 것을 확인할 수 있다.
Column t1.c1의 높은 Distinct Count 때문에 Filter Optimizationi의 효과가 아무런 힘을 발휘하지 못한 결과이다.
select count(*)
from
(
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select /*+ no_unnest */ t2.c1 from t2)
)
;
@stat
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.19 | 20038 |
|* 2 | FILTER | | 1 | | | 1000 |00:00:00.03 | 20038 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.04 | 38 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 10000 | 1 | 1 (0)| 1000 |00:00:00.13 | 20000 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
4 - access("T2"."C1"=:B1)
Cost는 19로 Hash Semi Join의 15에 비해 큰 차이는 없지만 실제 일량은 31:20,038 Block으로 큰 차이를 보인다.(Cost 책과 다름. 책 참조.)
Oracle은 기본적으로 Filter Operation의 Cost를 제대로 계산하지 못한다. Filter를 적용했을 때 얼마나 될지 예측할 수 없다는 의미이다.
Hash Semi Join방식은 대량의 Data를 처리할 때는 유리하지만, TOP-N Query와 같이 소량의 Data를 처리할 경우에는 불리하다.
select * from (
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select t2.c1 from t2)
)
where rownum <= 1;
@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 1 |00:00:00.01 | 12 | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 999 | 5 (20)| 1 |00:00:00.01 | 12 | 1517K| 1517K| 1439K (0)|
| 3 | INDEX FAST FULL SCAN| T2_N1 | 1 | 1000 | 2 (0)| 1000 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 10000 | 2 (0)| 316 |00:00:00.01 | 5 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
2 - access("T1"."C1"="T2"."C1")
==> 테스트에서는 Hash Join (책과 다름. 책 참조.)
Filter Operation이 사용된 경우
select * from (
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select /*+ no_unnest */ t2.c1 from t2)
)
where rownum <= 1;
@stat
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 1 |00:00:00.01 | 637 |
|* 2 | FILTER | | 1 | | | 1 |00:00:00.01 | 637 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 10000 | 11 (0)| 316 |00:00:00.01 | 5 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 316 | 1 | 1 (0)| 1 |00:00:00.01 | 632 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
2 - filter( IS NOT NULL)
4 - access("T2"."C1"=:B1)
==> 책과 결과 틀림. 책 참조
다음과 같은 Query에서도 Oracle은 성공적으로 Nested Loops Semi Join을 선택한다.
t1.c1 = 1 조건에 의해 Nested Loops Join이 훨씬 Cost가 저렴할 것으로 판단했기 때문이다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select t2.c1 from t2) and
t1.c1 = 1
;
@stat
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS SEMI | | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"=1)
4 - access("T2"."C1"=1)
filter("T1"."C1"="T2"."C1")
Subquery가 사용된 Query에서 Subquery Unnesting이 성공적으로 이루어지는지 항상 확인해야 한다.