Semi Join (반조인)

• 선행 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")