[SQL튜닝] SORT/MERGE/HASH JOIN

[SQL튜닝] SORT/MERGE/HASH JOIN

---

주요내용

• SORT/MERGE JOIN
• SORT/MERGE JOIN의 수행 절차
• SORT/MERGE JOIN이 불리한 경우
• SORT/MERGE JOIN의 장단점
• HASH JOIN
• HASH JOIN의 수행 절차
• HASH JOIN의 장단점

학습목표

• SORT/MERGE/HASH JOIN에 대한 이해를 바탕으로 비효율적으로 수행되는 SQL 문장을 진단할 수 있다.
• SORT/MERGE/HASH JOIN을 활용하여 SQL문장을 성능목표에 부합하도록 개선할 수 있다.
• SORT/MERGE/HASH JOIN 활용을 통해 개선된 SQL 문장이 성능목표에 부합하는지 테스트할 수 있다.

---

SORT/MERGE JOIN

• 연결고리에 인덱스가 전혀 없는 경우
• 대용량의 자료를 조인해야 함으로써 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은 경우
• 각 테이블에 대해 동시에 독립적으로 데이터를 먼저 읽어들임 → 읽혀진 각 테이블의 데이터를 조인을 위한 연결고리에 대하여 정렬을 수행 → 정렬이 모두 끝난 후에 작업이 수행됨
• 튜닝 포인트
  • 각 테이블로부터 데이터를 빨리 읽어들이도록 함
  • 메모리(SORT_AREA_SIZE)를 최적화함

---

SORT/MERGE JOIN의 수행 절차

• 가정 : color만 인덱스임

  SELECT /*+USER_MERGE(a b)*/
  a.color, ..., b.size, ...
  FROM table_a a, table_b b
  WHERE a.joinkey_a = b.joinkey_b
  AND a.color = 'RED'
  AND b.size = 'MED';

---

SORT/MERGE JOIN의 장단점

• 연결고리에 인덱스가 생성되어 있지 않은 경우에 빠른 조인을 위하여 사용됨
• 조인하고자 하는 각 테이블에 대해서 독립적으로 데이터를 읽어 들일 때, 이를 얼마나 빠르게 할 것 인가가 중요함
• 각 테이블로부터 읽혀진 데이터를 연결고리에 대해 정렬을 수행할 때 이를 얼마나 빠르게 할 것인가가 중요함

---

HASH JOIN

• NESTED LOOPS JOIN : 인덱스 사용에 의한 랜덤 액세스의 오버헤드
• SORT/MERGE JOIN : 정렬 작업으로 인한 오버헤드
• SORT/MERGE 조인과 비교해 보면, 각 테이블에 대한 처리를 독립적으로 하는 것은 같지만 HASH JOIN에서는 Driving Table이 있음
• 읽어들인 각 테이블의 데이터를 서로 조인하기 위해 해싱(hashing)을 이용해서 해시값을 만듦 -> 해시값으로 조인을 수행함
• 튜닝 포인트
  • Driving table을 결정함
  • 각 테이블로부터 데이터를 읽어들일 때, 빨리 읽을 수 있도록 함
  • 메모리(HASH_AREA_SIZE)를 최적화함

---

HASH JOIN의 장단점

• Hash Bucket이 조인 집합에 구성되어 해시함수 결과를 저장해야 하는 데 이러한 처리에는 많은 메모리와 CPU자원을 소모하게 됨
• 기본적으로 HASH_AREA_SIZE에 지정된 크기만큼의 메모리가 할당되어 사용됨
  • 조인을 수행하기에 메모리가 부족하다면 가장 큰 순서대로 Hash Bucket이 Temporary Tablespace로 내려가서 구성됨 -> 디스크로 내려간 Hash Bucket에 변경이 일어날 떄마다 디스크 I/O가 발생하게 되어 성능이 현저하게 저하됨
  • 하드웨어 자원이 넉넉한 상황에서는 다른 조인에 비해 보다 효율적인 수행이 가능하지만, 부족한 상황에서는 다른 조인 방법보다 오히려 느려질 수 있음