친절한 SQL 튜닝 05

조인 전략

NL 조인(Nested Loop)

- 가장 기본이 되는 전략

- 조인 대상이 되는 두 테이블의 인덱스를 이용한다.

- Outer Table, Inner Table로 나눠서 작업한다.

- 사원 테이블과 고객 테이블이 있다고 가정하자.

- 고객 테이블에는 해당 고객을 담당하는 사원의 ID가 column으로 있다.

- 이 두 테이블을 JOIN 할 때 양쪽 테이블 모두 인덱스를 사용한다.

- Outer Table 쪽은 테이블 사이즈에 따라서 사용하지 않을 수도 있다.(Full Scan이 빠른 경우)

- 하지만 Inner Table 쪽은 반드시 인덱스를 사용해야 한다. 그렇지 않으면 For Loop처럼 Inner Table Full Scan * Outer Table에서 읽은 횟수만큼 Full Scan이 발생하기 때문이다.

 

특징

- 랜덤 액세스 위주의 조인 방식 (인덱스를 사용하니깐)

- 메모리 버퍼에서 아무리 빠르게 읽더라도 비효율적인 부분이 존재할 수 있다.

- 그래서 대량 데이터를 조인할 때 불리하다.

- 한 레코드씩 순차적으로 진행하기 때문에 다르게 생각하면 메모리 공간 여유가 있다.

- 그래서 아무리 큰 테이블이라도 빠른 응답속도를 낼 수 있다.(단 부분 범위 처리를 활용할 수 있다면)

- 굉장히 인덱스 의존적인 조인 방법이고, 인덱스 튜닝이 중요하다.

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

 

Sort Merge 조인(Sort Merge Join)

- 조인 칼럼에 인덱스가 없을 때, 대량 데이터에 대한 조인이어서 인덱스가 효과적이지 않을 때,

- 옵티마이저는 NL 조인 대신 대안을 찾는데 그중 하나가 Sort Merge 조인이다.

- 소트 머지 조인은 PGA(Private Global Area)에서 수행된다. 이 공간은 오라클 서버 프로세스마다 할당된 독립적, 고유적인 메모리 영역이다. 공간이 부족하면 Temp 테이블 스페이스를 사용한다.

- 참고로 SGA(Shared Global Area)는 오라클 DB의 공유 메모리 영역이고 여러 프로세스가 공유할 수 있지만 동시에 접근할 수 없는 critical section이다.

- 그래서 이 곳에는 Latch라고 하는 Locking 메커니즘이 존재한다.

- 그런데 Sort Merge Join은 PGA를 이용하기 때문에 래치 메커니즘이 필요 없고, 따라서 같은 양의 데이터를 읽을 때도 SGA의 버퍼 캐시보다 훨씬 빠르게 읽을 수 있다.

- 조인 대상 테이블 2개를 소팅하고, 정렬한 양쪽 집합을 머지(merge)한다.

- 소트 머지 조인은 Sort Area에 미리 정렬해 둔 자료구조를 이용할 뿐 NL 조인과 프로세스는 같지만 조인 과정에서 액세스 하는 모든 블록을 랜덤 액세스 방식으로 1건마다 DB 버퍼 캐시를 경유해 읽는다.

- 즉 인덱스든 테이블이든 읽는 모든 블록에 대해 래치 획득 및 캐피 버퍼 체인 스캔 과정을 거친다.

- 다시 말해 인덱스를 이용하기 때문에 인덱스 손익분기점의 한계를 보통 넘어버리는 대량 데이터 조인에는 불리하다.

- 소트 머지는 위 래치 과정이 없다. 물론 조인 대상이 되는 테이블을 읽을 때는 DB 버퍼 캐시를 경유하고 인덱스도 사용할 수 있어서 이 과정에서 생기는 버퍼 캐시 탐색 비용과 랜덤 액세스 비용은 피할 수 없다.

- 인덱스에 영향을 받지 않는다는 것도 NL 조인과 비교할 때의 특징이다.

 

- 조인 조건이 등치 조건이 아닌 범위 조건이면서 대량 데이터를 조인할 때

- 조인 조건이 아예 없는 카테시안 혹은 cross join일 때 주로 사용한다.

해시 조인(Hash Join)

- 소트 머지 조인처럼 인덱스를 이용하지 않아 대량의 데이터를 조인할 때 유리하다.

- 거기에 테이블을 정렬할 필요도 없다.

- 작은 쪽 테이블을 읽어서 해시 테이블을 만들고, 큰 쪽 테이블을 읽어서 해시 테이블을 탐색하면서 조인한다.

- 해시 테이블을 생성하고 그것 또한 PGA영역에서 발생하기 때문에 래치 프로세스가 없어서 빠르다.

- 소트 머지 조인과의 차이점이라고 한다면 PGA 공간이 그렇게 크지 않다는 점, 이로 인해 메모리 공간이 부족하면 Temp 테이블 스페이스, 즉 디스크에 쓰는 작업을 수반한다.

- 다시 말해서 소트 머지 조인은 테이블이 크면 두 테이블을 소팅한 결과를 메모리에 올려야 하고, 부족하면 디스크 I/O가 일어나지만, 해시 조인은 작은 테이블의 해시 테이블 하나만 올리면 돼서 유리한 점이 있다.

- 일반적으로 조인 기법 중 가장 빠르다.(설령 Temp 테이블 스페이스를 사용해야 하더라도)

 

 

결론

소량 데이터 조인 -> NL 조인

대량 데이터 조인 -> 해시 조인

대량 데이터 조인 + 해시 조인으로 처리할 수 없는 쿼리(조인 조건이 등치 조건이 아니거나 조인 조건이 없을 때) -> 소트 머지 조인

 

(최적화된) NL 조인과 해시 조인의 성능이 같으면 -> NL 조인

해시 조인이 아주 약간 빨라도 -> NL 조인

해시 조인이 매우 빠르면 -> 해시 조인

 

수행 빈도가 낮고, 쿼리 수행 시간이 오래 걸리고, 대량 데이터 조인 시 -> 해시 조인

 

해시 조인에서 생성되는 테이블은 영속적이지 않다. 작업 후 PGA 영역에서 소멸된다.

반면에 NL 조인에 사용되는 인덱스는 영구 유지되면서 다양한 쿼리들의 수행에 사용되고 재사용되는 자료구조다.

그래서 수행 시간도 짧고, 수행 빈도도 높은 쿼리를 해시 조인을 사용하면 CPU, 메모리 자원 소모가 굉장히 크다.

또 해시 테이블을 만드는 과정에서 다양한 경합이 발생할 수 있다.

 

그리고 0.1초를 0.01초로 만들어야 할 정도로 최적화를 해야 한다면 그것 또한 NL 조인으로 인덱스 튜닝해서 사용하는 게 좋다.