SEMI JOIN, ANTI JOIN

■ SEMI JOIN
1. 정의

- 서브쿼리를 사용하여 조인을 수행하는 방법 중 하나이다.

- 메인 쿼리(선행 테이블)의 각 행이 서브쿼리(후행 테이블)와 일치하는지 확인하고, 조건에 맞으면 해당 행을 결과에 추가한다. 서브 쿼리의 모든 일치 행을 찾지 않고, 한 번의 일치만 확인하면 되기 때문에 효율적이다.

2. 동작 방식

- 메인 쿼리의 각 행에 대해 서브쿼리를 실행하여 일치 여부를 확인한다.
- 조인 조건에 맞으면 해당 행을 결과에 추가하고, 추가된 행은 더 이상 검색하지 않는다.
- 메인 쿼리의 모든 행에 대해 이러한 작업을 반복한다.

3. 특징

1) 일치 여부 확인: 메인 쿼리의 각 행에 대해 서브쿼리의 일치 여부를 확인

2) 조인 종류: 주로 INNER JOIN과 유사한 방식으로 작동하며, OUTER JOIN과는 달리 후행 테이블의 모든 일치 행을 찾지 않습니다.

3) 범위 스캔: 메인 쿼리의 각 행에 대해 서브쿼리가 범위 스캔을 하지 않는다.

4. 성능 고려 사항

- 서브쿼리의 결과가 크거나, 조인 조건의 일치 행이 적을 때 성능적으로 유리하다.

- 세미 조인 힌트를 사용하여 조인 방식을 명시할 수 있다.

5. 힌트

- nl_sj(네스티드 루프 세미조인), merge_sj(정렬 + 내부적으로 비교, 머지 세미조인), hast_sj (cup 성능이 좋다면+ hash 함수, 해시 세미조인)

---

select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest nl_sj */ 'x' from employees where department_id = d.department_id);

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

 

---

1. unnest 

select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest nl_sj */ 'x' from employees where department_id = d.department_id);

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     11 |00:00:00.01 |      10 |
|   1 |  NESTED LOOPS SEMI |                   |      1 |     10 |     11 |00:00:00.01 |      10 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       7 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |     27 |     41 |     11 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

---

2. unnest merge_sj

select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest merge_sj */ 'x' from employees where department_id = d.department_id);

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                   |      1 |        |     11 |00:00:00.01 |       5 |       |       |    |
|   1 |  MERGE JOIN SEMI             |                   |      1 |     10 |     11 |00:00:00.01 |       5 |       |       |    |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |     27 |     12 |00:00:00.01 |       4 |       |       |    |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |      1 |     27 |     12 |00:00:00.01 |       2 |       |       |    |
|*  4 |   SORT UNIQUE                |                   |     12 |    107 |     11 |00:00:00.01 |       1 |  2048 |  2048 | 2048 (0)|
|   5 |    INDEX FULL SCAN           | EMP_DEPARTMENT_IX |      1 |    107 |    106 |00:00:00.01 |       1 |       |       |    |
----------------------------------------------------------------------------------------------------------------------------------
-----


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("DEPARTMENT_ID"="D"."DEPARTMENT_ID")

---

3. unnest hash_sj

select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest hash_sj */ 'x' from employees where department_id = d.department_id);

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     11 |00:00:00.01 |       8 |       |       |          |
|*  1 |  HASH JOIN SEMI    |                   |      1 |     10 |     11 |00:00:00.01 |       8 |   986K|   986K| 1210K (0)|
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|   3 |   INDEX FULL SCAN  | EMP_DEPARTMENT_IX |      1 |    107 |    106 |00:00:00.01 |       2 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")

select /*+ gather_plan_statistics */ *
from departments d
where not exists (select /*+ no_unnest  */ 'x' from employees where department_id = d.department_id);

 

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     16 |00:00:00.01 |      34 |
|*  1 |  FILTER            |                   |      1 |        |     16 |00:00:00.01 |      34 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       7 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |     27 |      2 |     11 |00:00:00.01 |      27 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NULL)
   3 - access("DEPARTMENT_ID"=:B1)

4. not exists + no_unnest

select /*+ gather_plan_statistics */ *
from departments d
where not exists (select /*+ no_unnest */ 'x' from employees where department_id = d.department_id);

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-- 필터 방식으로 풀겠다.
-- 소속사원이 없는 부서를 출력하는 sql문

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     16 |00:00:00.01 |      34 |
|*  1 |  FILTER            |                   |      1 |        |     16 |00:00:00.01 |      34 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       7 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |     27 |      2 |     11 |00:00:00.01 |      27 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NULL)
   3 - access("DEPARTMENT_ID"=:B1)

-- 27개 부서 중 index에서 찾은 결과는 11건 하지만 우리가 찾는건 그 외 16개
-- 후보행 값이 sub쿼리절에 존재하지 않는 것이 우리가 찾는 데이터 / 있으면 버려

5. not exists  (no_unnest 제외)

select /*+ gather_plan_statistics */ *
from departments d
where not exists (select'x' from employees where department_id = d.department_id);

 

#) no_unnest 힌트 빼고 실행

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

 

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     16 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS ANTI |                   |      1 |     17 |     16 |00:00:00.01 |      11 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       7 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |     27 |     41 |     11 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

-- NESTED LOOPS ANTI 란? join + not exists 가 혼합된 케이스 (semi 조인 + not 은 anti)
departments에 있는 값이 index에 join으로 성립되지 않는 값만 뽑아.

■ ANTI JOIN(=: SEMI JOIN의 반대 )
1. 정의

- 서브쿼리를 사용하여 조인을 수행하고, 메인 쿼리의 값이 서브쿼리에 존재하지 않는 행을 찾아내는 방법이다. 즉, 메인 쿼리의 결과에서 서브쿼리와 일치하지 않는 데이터를 반환한다.

2. 동작 방식

- 메인 쿼리의 각 행에 대해 서브쿼리를 실행하여 일치 여부를 확인합니다.
- 조인 조건에 맞지 않는 경우 해당 행을 결과에 추가하고, 추가된 행은 더 이상 검색하지 않습니다.
- 메인 쿼리의 모든 행에 대해 이러한 작업을 반복합니다.

3. 특징

- 일치 여부 확인: 메인 쿼리의 각 행에 대해 서브쿼리의 일치 여부를 확인한다.

- 조인 종류: 주로 OUTER JOIN과 유사한 방식으로 작동하며, INNER JOIN과는 달리 일치하지 않는 데이터를 반환한다.

- 범위 스캔: 메인 쿼리의 각 행에 대해 서브쿼리가 범위 스캔을 하지 않는다.

4. 성능 고려 사항

- 안티 조인은 서브쿼리의 결과가 크거나, 조인 조건의 일치하지 않는 행이 적을 때 성능적으로 유리합니다.
- 안티 조인 힌트를 사용하여 조인 방식을 명시할 수 있습니다.

5. 힌트 

- nl_aj(네스티드 루프 안티조인), merge_aj(머지 안티조인), hast_aj (해시 안티조인)

---

select /*+ gather_plan_statistics */ *
from departments d
where not exists (select /*+ unnest nl_aj */  'x' from employees where department_id = d.department_id);

메인 1 : m 서브
드라이빙테이블 = 메인

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     16 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS ANTI |                   |      1 |     17 |     16 |00:00:00.01 |      11 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       7 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |     27 |     41 |     11 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")