[개념정리] 조인튜닝 - 소트머지조인, 해시 조인

 

소트머지조인과 해시조인은 PGA (process/program/private global area - 프로세스에 종속적인 고유 데이터를 저장하는 용도)를 사용해 SGA 버퍼캐시에서 데이터를 읽을 때 보다 훨씬 빠르게 대량의 데이터 조인을 처리 할 수 있다.

SGA 에 캐싱된 데이터는 여러 프로세스가 공유 할 수 있지만, 동시 엑세스가 안된다. 프로세스간 엑세스를 래치(lock)로 직렬화 하고, 나아가 블록 읽을 때 버퍼 lock이 실행 되기 때문에 PGA에 비해 느릴 수 밖에 없다.

 

 

 

소트 머지 조인 :

1. 조인 컬럼에 인덱스가 없거나
2. 대량 데이터 조인이여서 인덱스가 효과적이지 않거나
   1. NL은 대량 데이터 조인 시 성능이 매우 느림 - 조인 과정에서 액세스 하는 모든 블록을 랜덤 액세스 방식으로 '건건이' DB 버퍼캐시를 경유해서 읽는다. - 읽는 모드 블록에 래치 획득 및 캐시 버퍼 체인 스캔 과정을 거치고, 버퍼 캐시에서 찾지 못한 블록은 '건건이' 디스크에서 읽어들인다.
3. 해시조인을 사용 할 수 없는 경우 일 때 사용 - 해시 조인은 조인 조건식이 등치(=) 조건이 아닐 때 사용 할 수 없다. 
4. 조인 조건식이 없는 조인(Cross Join, 카테시안 곱)

해시 조인 :

 

소트 머지 조인

• 소트 단계(양쪽 집합을 조인 컬럼 기준 정렬) -> 머지 단계(정렬한 양쪽 집합을 서로 머지) 순서로 진행한다.
  1. Leading(ordered) 테이블을 조인 컬럼 기준으로 정렬해 정렬 결과 집합을 PGA의 Sort Area에 저장.(Sort Area에 담을 수 없을 정도로 크면 Temp테이블 스페이스에 저장)
  2. 조인 테이블(use_merge지정) 을 조인 컬럼 순으로 정렬, 결과집합을 Sort Area 에 저장(크면 Temp테이블 스페이스에 저장)
• Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 조인 컬럼에 인덱스 지정이 없어도 사용 할 수 있는 방식.
• 빠른 이유 
  • 양쪽 테이블로 부터 조인 대상 집합을 일괄적으로 읽어 PGA에저장 후 조인 - 래치 획득 과정 없음
  • 양쪽 테이블로 부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시 이용 - 인덱스를 이용하는 것 - 이 과정의 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 소트 머지 조인도 가진다.
  • 소트 부하만 감수 한다면 건건이 버퍼캐시를 경유하는 NL조인보다 빠르다.

 

해시 조인

• Build단계(작은 쪽 테이블을 읽어 해시 맵을 생성) -> Probe단계(큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인)
  1. Ordered / leading 테이블 (Build Input)을 읽어 해시 맵을 생성 하는데 조인 컬럼을 읽어 해시 테이블 키 값으로 사용한다. 조인 컬럼의 값을 해시 함수에 넣어 반환된 값으로 해시 체인을 찾고 해시 체인에 데이터를 연결한다. 해시 테이블은 PGA의 Hash Area에저장(해시 테이블이 너무 커 Hash Area에 저장 할 수 없으면 Temp 테이블스페이스에 저장)
  2. 조인 테이블(use_hash지정)을 조건절로 필터링 한 결과 집합에 대해 앞서 생성한 해시 테이블을 탐색 - 조인 컬럼 값을 해시 함수에 넣어 반환 값으로 해시 체인을 찾고 그 해시 체인을 스캔해서 값이 같은 값을 찾아 조인
• NL조인, 소트 머지 조인은 조인 기준 테이블(leading 테이블, ordered 첫 번째 테이블) 데이터에서 바깥 loop를 돌며 조인 테이블을 inner loop 로 실행 하지만, 해시조인은 조인 테이블(use_hash 테이블) 데이터를 바깥 loop로 돌리며 해시 맵을 안쪽 loop 로 실행한다.
• 소트 머지 조인과 비교해 조금 더 빠른 이유는 소트 머지 조인이 조인 전 '양쪽' 집합을 모두 정렬하는 대신, 해시 조인은 '한쪽'을 읽어 해시 맵을 만들기 때문이다. 대량의 데이터에 조인을 적용 할 때 소트 머지 조인은 양쪽 집합 중 어느 하나가 중대형 이상이면 Temp테이블 스페이스(디스크) 에 쓰는 작업이 수반 되지만, 해시 조인은 작은 쪽 테이블을 Build Input으로 선택하므로 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면 Temp테이블스페이스에 쓰는 작업은 일어나지 않는다.
• 대용량 Build Input 처리 
  • 양 쪽 테이블 모두 대용량 테이블 이여서 인메모리 해시 조인(Hash Area 사용)이 불가능할 경우 파티션을 사용
    1. 파티션 단계 : 조인 하는 양쪽 집합의 조인 컬럼에 (조인 절 외 조건을 만족하는 레코드 집합) 해시 함수 적용, 반환된 해시 값에 따라 동적 파티셔닝(독립적으로 처리할 수 있는 여러개의 작은 서브 집합으로 분할) - 양쪽 집합을 Temp공간(디스크)에 저장 해야 하므로 인메모리 해시 조인보다 성능이 많이 떨어진다.
    2. 조인 단계 : 파티션 짝에 대해 하나씩 조인 수행, 파티션 진행 전 어느 쪽이 작은 테이블이였는지에 상관없이 파티션 된 pair중 작은 쪽을 Build Input으로 선택하고 해시 테이블 생성. 해시 테이블 반대쪽 파티션 로우를 하나씩 읽으면서 해시테이블 탐색
• 힌트
  • Use_hash(a b) : a, b테이블을 해시 조인. 옵티마이저가 a, b 둘 중 카디널리티가 작은 쪽을 build input으로선택
  • Leading, Ordered 힌트로 Build Input 을 정하거나, swap_join_inputs(a) 처럼 build input을 명시할 수도 있다.

 

조인 메소드 선택

• 소량 데이터 -> NL조인
  • 수행빈도가 높을 경우, 해시조인과 NL조인 성능이 같거나, 해시조인이 조금 더 성능이 좋은 경우 까지도 NL조인선택 할 것
  • 인덱스는 영구적, 재사용 가능한 자료 구조, But 해시 테이블은 하나의 쿼리를 위해 생성, 조인 끝나면 소멸하는 자료구조라서 CPU와 메모리 사용률이 크게 증가. 더불어 해시 맵을 만들때 Index Access는 래치 경합.
• 대량 데이터 -> 해시 조인
  • NL조인을 최적화 했는데도, 랜덤 액세스가 많아 만족할 만한 성능을 못 낸 다면 대량 데이터 조인에 해당
  • 수행 빈도 적고, 쿼리 수행시간이 오래 걸리는 대량 데이터 조인일 경우 해시 조인 사용(DW, OLAP, 배치)
• 대량 데이터 & 등치조건절 X -> 소트 머지 조인