JOIN 종류와 수행 원리(2)

- JOIN의 종류

Inner Join, Outer Join, Cross Join, Natural Join 외에도

Nested Loop Join, Sort Merge Join, Hash Join이 있다.

 

1. Nested Loop Join

- 중첩 for문과 같은 원리이다.

ex) IDOL_GROUP, IDOL_MEMBER 테이블을 조인시키려 하고, 조인을 할 수 있는 연결고리는 GROUP_NAME이라고 할 때, 소녀시대 데이터(IDOL_GROUP) 데이터가 멤버 테이블(IDOL_MEMBER) 테이블로 가서 멤버들을 찾는다.

IDOL_GROUP : Outer Table

IDOL_MEMBER : Inner Table 이 되는 것이다.

 

* 유의해야 할 점 : Inner Table에서 Join 컬럼이 인덱스에 걸려 있지 않으면 굉장한 비효율이 발생한다. 그 이유는 한 건 한 건 갈 때마다 full scan을 해야하기 때문이다.

따라서 inner table의 구성 전략에 매우 중요한 요소가 된다.

 

- 대량의 테이블을 Join하는 방식으로는 적절하지 않고, OLTP 쿼리에 적절하다.

GROUP TABLE과 MEMBER TABLE은 1:M의 관계인데

여기에서 1에 해당하는 소량의 데이터를 가진 테이블이 Outer Table로 설정되는 것이 성능에 유리하다.

 

2. Sort Merge Join

마찬가지로 중첩 for문과 유사한 방식이다.

- Nested Loop Join과 다른 점은

테이블과 테이블을 우선 Join 컬럼을 기준으로 정렬을 시키고 나서, join 시킨다.

INNER TABLE 쪽에 적절한 인덱스가 없어 Nested Loop Join을 쓰기에 비효율적인 경우 사용한다.

 

- equal Join이 아니라 범위로 Join을 하는 경우 적절한 수행 원리이다.

 

- table random access가 일어나지 않고, sorting 작업이 PGA 영역에서 수행되기 때문에 경합이 발생하지 않아서 성능에 유리하다.

 

3. Hash Join

- 배치 시스템에서 적절한 수행원리이다.

 

- 대용량 테이블을 JOIN할 때 HASH JOIN을 사용하면 좋다.

 

- 수행 원리 : MEMBER 테이블이 굉장한 대용량 테이블일 때, GROUP 테이블을 Build Input으로 삼아서 Hash 영역에 저장 해놓는다. Hash 영역은 Sort Merge Join과 마찬가지로 Sort Area처럼 PGA영역에 있기 때문에 처리 속도가 매우 빠르다.

따라서 IDOL_GROUP 테이블이 Hash 영역으로 올라가고, IDOL_MEMBER 테이블이 읽히면서 Join이 되는 원리이다.

 

- Hash 영역으로 올라갈 때는 Join 컬럼을 기준으로 Hash Function이 적용되기 때문에 이 Key 컬럼에 중복값이 없을수록 성능에 유리하다.

 

- EQUAL 조인만 가능하다. Sort Merge Join처럼 Random Access 부하가 없다.

 

* 유의할 점 : Hash 영역에 들어가는 테이블의 크기가 충분히 작아야 성능에 유리하다. 

(Hash 영역의 사이즈가 정해져 있는데 테이블의 용량이 그것을 초과해서 벗어나게 되면, 오히려 디스크 영역을 사용하게 되기 때문에 성능에 매우 불리해진다.)

또, 수행 빈도가 높은 OLTP에서 Hash Join으로 풀리게 되면 오히려 cpu나 메모리의 사용량이 늘어서 성능이 안 좋아질 수 있다.

따라서, 실행 계획을 확인(I/0나 실행시간)하고, ORACLE hint를 써서 다른 수행 방식으로 풀릴 수 있도록 유도를 하는 게 효과적인 튜닝 방법이다.

 

출처 : 유튜브 SQL 전문가 정미나님 강의