[SQLP] 조인 수행 원리

대표적 네가지 조인 수행 원리
1. Nested Loop Join 

2. Hash Join

3. Sort Merge Join

4. Cartesian Join

- 조인 수행 원리는 outer join, inner join 등의 개념과는 관계 없다.

 

Nested Loop Join 

특징: 
- 랜덤 액세스, 랜덤 스캔  사용

- 인덱스를 사용할 수 있는 조인

① 인덱스 사용

② 조인키를 이용하여 원하는 테이블을 random access 방식으로 찾음

③ 후행 테이블의 인덱스를 사용

- Random access 
선행테이블의 조인키를 이용해 후행 테이블을 하나하나 대조해서 찾아간다.

선행 테이블의 인덱스로 인해 Random access 를 줄여야 한다. >> random access 로 인해 disk i/o가 증가하는 현상 발생

 

- 시험 포인트
NL조인은 한 레코드씩 순차적으로 진행한다. (부분 범위 처리시 효과적이다.)

인덱스의 구성 전략이 중요하다

소량의 데이터를 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리 시스템(OLTP)에 유리하다.

 

실습)
DEPARTMENT_ID 을 이용해서 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조인한다.
그리고 DEPARTMENT_ID = 60인 정보도 불러오고 실행계획도 출력한다.

explain plan for 
select * from employees e Join departments d 
on d.department_id = e.employee_id
where d.department_id > 60;

Plan hash value: 718553685
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    22 |  1980 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                   |             |    22 |  1980 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    22 |   462 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_ID_PK  |    22 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."DEPARTMENT_ID"="E"."EMPLOYEE_ID")
   3 - access("D"."DEPARTMENT_ID">60)
   4 - filter("E"."EMPLOYEE_ID">60)
   
   
   
  
### "> 60"인 경우에 HASH JOIN을 사용했는데, NL은 단일 값 조회에 효율적이기 때문에 후자같은 다중값 추출은 비효울적이라 판단하여 사용안 한 것이다.

### 즉 HASH JOIN 은 큰데이터 셋에서 대량의 행을 조인할 때 효율적이며, HASH 는 단일 값 조회에 효율적이다.

 

 

Sort Merge Join 

- 특징
일반적으로 정렬작업이 필요하므로 Hash Join 이 유리한 경우가 있다.

비등가 (<,>)조인에서도 사용이 가능하다.    ==> Hash 와의 차이 

정렬하는 작업이 많아서 주로 mem 를 많이 사용한다. mem를 너무 많이 사용할 경우 disk에서도 가져올 수 있기 때문에 성능이 많이 떨어질 수 있다.

 

실습)

explain plan for 
select * from employees e join departments d
on d.department_id=e.department_id
where e.last_name like 'K%';

Plan hash value: 4137328069
 
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     5 |   450 |     5  (20)| 00:00:01 |
|   1 |  MERGE JOIN                   |             |     5 |   450 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS |    27 |   567 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN            | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                   |             |     5 |   345 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     5 |   345 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | EMP_NAME_IX |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   6 - access("E"."LAST_NAME" LIKE 'K%')
       filter("E"."LAST_NAME" LIKE 'K%')
       
##강제로 힌트를 사용하여 NL join 사용
explain plan for 
select /*+use_nl(e d)*/* from employees e join departments d
on d.department_id=e.department_id
where e.last_name like 'K%';

Plan hash value: 129194720
 
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     5 |   450 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     5 |   450 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     5 |   345 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_NAME_IX |     5 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS |     1 |    21 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."LAST_NAME" LIKE 'K%')
       filter("E"."LAST_NAME" LIKE 'K%')
   5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

 

- 시험 포인트

첫번째 정렬 집합을 기준으로 두번째 정령 집합의 데이터 액세스 시에 버퍼 캐시를 탐색하지 않고, PGA sort area를 읽는다.
두번째 정렬 집합은 반드시 PGA에 저장된다. 

첫번째 정렬 집합은 PGA에 저장하거나, 조인 키 컬럼에 인덱스가 있다면 PGA에 저장하지 않고 조인을 시작 가능하다.

대부분 해시조인인 보다 느린 성능을 보이나, 아래와 같은 상황에서는 소트머지 조인이 유용하다.

• First테이블에 소트연산을 대체할 인덱스가 있을 때
• 조인할 First 집합이 이미 정렬되어 있을 때
• 조인 조건식이 등치(=)조건이 아닐 때
  

두 결과 집합의 크기가 많이 차이나는 경우에는 SORT MERGE JOIN이 비효율적이다.

• 어느 한 쪽이라도 정렬 작업이 종료되지 않으면 조인이 시작될 수 없으므로 두 테이블 조인 집합의 크기가 많이 차이가 난다면 한쪽에 '대기' 상태가 발생하여 비효율적으로 처리가 된다. 이렇게 크기가 비슷하지 않은 집합의 조인을 위해서 HASH 조인을 사용할 수 있다.
  (출처: https://hyeyul-k.tistory.com/15)

 

Hash Join 

 

특징: 

cpu 위주로 데이터 처리를 한다. 이는 nl 과 sort 의 문제점을 해결하는 방안으로 등장했다.

'해쉬 테이블' 을 메모리에 생성할 때 메모리의 적재할 수 있는 크기보다 더 커지면 임시 영역인 '디스크'에 저장하게 된다.

이때문에 hash join 시 결과 행의 수가 적은 테이블을 선행 테이블로 사용해야 한다. 이는 선행 테이블의 결과를 완전히 메모리에 저장할 수 있다면, 임시 영역에 저장하는 작업이 발생하지 않고 cpu 연산을 덜 수행할 수 있다. 

 

실습)

explain plan for 
select * from employees e join departments d
on d.department_id = e.employee_id
where d.manager_id=110;

Plan hash value: 2731514911
 
----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    90 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |               |       |       |            |          |
|   2 |   NESTED LOOPS               |               |     1 |    90 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS   |     1 |    21 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("D"."MANAGER_ID"=110)
   4 - access("D"."DEPARTMENT_ID"="E"."EMPLOYEE_ID")
   
   
   
 ##hash 조인을 강제로 했을 경우에는?
 explain plan for 
select /*+use_hash(e d)*/* from employees e join departments d
on d.department_id = e.employee_id
where d.manager_id=110;

Plan hash value: 2052257371
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    90 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |             |     1 |    90 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |     1 |    21 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."DEPARTMENT_ID"="E"."EMPLOYEE_ID")
   2 - filter("D"."MANAGER_ID"=110)
   
   
   
   
 ###NESTED LOOPS 조인이 hash join 보다 더 좋은 경우는 "선택도가 낮음" 으로 인한 선행 테이블에서 빠르게 끝날 경우다.

 NESTED LOOPS 조인이 hash join 보다 더 좋은 경우는 "선택도가 낮음" 으로 인한 선행 테이블에서 빠르게 끝날 경우다.

 

- 시험 포인트

해시 조인은 작은 쪽의 집합만을 읽어서 PGA에서 해시 맵을 저장한다.

해시 조인은 작은 테이블의 1) 해시 맵 생성, 큰 테이블의 2) 해시 맵 탐색 순으로 진행된다.

해시 맵을 이용하므로 조이 컬럼에 인덱스가 없어도 상관 없다.

해시 조인은 수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대량 데이터 조인 시에만 사용한다.

모든 조건이 '=' 이면 좋다.

 

- 위의 셋 NL, Sort Merge, Hash join의 공통점

조인에 성공하면 결과를 추출 버퍼에 넣는다.

 

추출 버퍼:  해시 조인의 과정 중에서 데이터가 일시적으로 저장되는 메모리 공간

추출 버퍼의 역할:

• 임시 저장소: 추출 버퍼는 Build 단계에서 생성된 데이터가 일시적으로 저장되는 메모리 공간. 이 공간은 나중에 Probe 단계에서 사용.
• 효율성: 추출 버퍼를 사용하면, 해시 조인은 큰 데이터를 효율적으로 처리할 수 있다. 모든 데이터를 메모리에 올리지 않고도 조인을 수행할 수 있게 해준다.

 

https://hoon93.tistory.com/46 << 여기 잘정리되어있다.. 한번 꼭 읽어보자