[Tibero] SQL Tuning (3)

3. 조인 메소드

한 테이블에 원하는 데이터가 없을 시, 두 개의 테이블을 묶어 하나의 결과로 만들어 내는 것이

조인 입니다. 본 문서에서는 물리적인 조인 방법인 Nested Loop join, Sort merge join, Hash

join 에 대해 안내합니다.

 

​

3.1. Nested Loop Join (중첩반복 조인)

선행테이블에서 상대방 테이블에 순차적으로 접근해 추출한 값으로 결과를 만드는 조인 방식

입니다. 순차적인 처리로 fetch의 운반단위(array size)마다 결과를 리턴 받을 수 있습니다.

​

SELECT /*+ ORDERED USE_NL(d e) */ e.ename, d.dname, … FROM dept d, emp e WHERE d.deptno = e.deptno AND d.deptno = 10 AN e.ename = ‘KING’

 

그림5. Nested Loop Join 절차

 

 

그림6. Nested Loop Join 연결고리 인덱스

 

​

특징

• 첫번째 Row를 받는 시간은 빠르나 전체적인 결과를 받는 데까지 시간이 오래 걸립니다.
• 조인으로 연결되는 테이블 중 첫번째로 액세스되는 테이블인, 드라이빙 테이블은 옵티마이저가 결정합니다.
• 드라이빙 테이블에서 많은 Row들이 필터링되어 내부 테이블로 찾아 들어가는 부분을 줄여야 하므로 드라이빙의 순서가 중요합니다.
• 내부 테이블은 드라이빙 테이블의 리턴되는 모든 Row들에 대해 반복 실행하므로 액세스 효율이 좋아야 합니다.
• 인덱스 위주의 단일 블록에 대해 랜덤 I/O 위주이므로 OLTP에서 적은 데이터 범위 처리에 주로 사용 됩니다.

​

​

​

3.2. Sort Merge Join (정렬병합 조인)

양쪽 테이블을 각자 액세스해 동시에 차례대로 스캔하면서 합치듯이 결과를 만드는 조인 방식

입니다.

​

SELECT /*+ ORDERED USE_MERGE(d e) */ e.ename, d.dname, … FROM dept d, emp e WHERE d.deptno = e.deptno AND e.ename = ‘KING’

 

그림7. Merge Join 절차

 

​

특징

• 전체 Row를 빠르게 리턴받습니다.
• 드라이빙 테이블과 내부 테이블의 액세스 패턴에 의해 액세스 효율이 좌우되지 않으며 조인 테이블 간에 자신의 처리 범위로만 처리량을 결정하므로 독립적입니다.
• 추가적인 정렬을 위함 메모리(sort_area_size)가 필요합니다. 메모리가 부족하면 TEMP 테이블스페이스에 정렬의 중간단계(sort run)을 기록하게 되어 I/O가 발생할 수 있습니다.
• 정렬 메모리에 위치하는 대상은 조인 키 뿐 아니라 select list로 포함하므로 불필요한 select list는 제거합니다.
• 정렬 작업의 CPU 사용에 대한 오버헤드가 있습니다.
• 디스크 정렬만 발생하지 않는다면 넒은 범위 처리에 유리합니다.

​

​

​

3.3. Hash Join

Nested Loop join과 Sort merge join의 취약점을 보완할 수 있는 조인으로, Hash 함수의

결과를 사용해 테이블의 값을 조인하는 방식 입니다.

​

 

그림8. Hash Join 절차

​

​

특징

• 조인 테이블 중 하나의 테이블을 통해 메모리에 해시 테이블을 만든 후 순차적으로 처리합니다.
• 액세스의 효율이 드라이빙 테이블의 리턴되는 Row수와 내부 테이블의 액세스 패턴에 의해 좌우되지 않으며 조인 테이블 간에 자신의 처리 범위로만 처리량을 결정하므로 독립적 입니다.
• 조인되어야 할 집합이 클 경우 Hash 조인은 비효율적인 Nested Loop 조인의 단점을 극복하고, Sort Merge 조인에서 필요한 정렬을 수행하지 않고도 조인을 수행해 우수한 성능을 보여줍니다.

​

​

​

Join 별 특징 비교

구 분	Nested Loop	Sort Marge	Hash
옵티 마이저 힌트	USE_NL (table / Alias)	USE_MERGE (table / Alias)	USE_Hash (table / Alias)
조인 조건	Any Join	Any Join (주로 Equi-Join)	Equi-Join Only
성능 포인트	CPU, Disk I/O	Temporary Segment	Memory, Temporary egment
장점	드라이빙 테이블의 ROW 수가 적거나, 조인의 연결 고리에 적절한 액세스 경로가 있는 경우 효율적 ​ SORT MERGE 또는 HASH 조인에 비해 FIRST_ROWS 방식에 효율적	조인 연결고리 인덱스가 없거나 조인집합을 구성하는 검색조건이 조인 범위를 줄여주지 못하는 경우 효율적 ​ 제한된 메모리로도 실행가능 ​ ​	조인 연결고리 인덱스 가 없거나 조인집합을 구성하는 검색조건이 조인 범위를 줄여주지 못하는 경우 효율적 ​ 일반적으로 SORT MERGE 조인보다 뛰어난 수행
단점	조인 연결고리 인덱스가 없거나 조인집합을 구성 하는 검색조건이 조인 범위를 줄여주지 못하는 경우 비효율적 ​	조인이 되는 두 집합 모두 정렬 필요 ​ FIRST_ROWS 방식보다는 ALL_ROWS 방식을 위한 구조	HASH 테이블을 위한 많은 메모리 필요 ​ FIRST_ROW를 늘 빠르 게 출력하는 것은 아님 ​

 

​

Join 방법

• 인덱스를 사용하면 효과적으로 조인의 횟수를 줄일 수 있습니다.
• 조인의 순서는 처리 범위가 적은 테이블에서 많은 쪽으로 수행합니다.
• 조인 조건 이외의 조건 절에 의하여 조인의 순서가 결정됩니다.
• 어느 쪽으로 조인하든 결과는 같습니다.
• OLTP의 경우 부분 범위 처리를 하도록 유도합니다.
• 배치 트랜잭션은 Hash 조인을 사용해 업무량을 줄일 수 있습니다.

​

​​

테이블 상황별 Join 규칙

• 양쪽에 인덱스가 있는 경우 : 대상 건수가 적은 테이블을 드라이빙 테이블로 적용
• 한쪽만 인덱스가 있는 경우 : 인덱스가 없는 테이블을 드라이빙 테이블로 적용
• 양쪽에 인덱스가 없는 경우 : MERGE 또는 HASH 조인 적용
• Outer 조인인 경우 : 조인시 포함하려는 데이터가 있는 테이블을 드라이빙으로 적용

​

​

 

 

☞[Tibero] SQL Tuning (4)에서 계속됩니다.​

​

​