SQL BOOSTER - 조인의 내부적인 처리 방식
조인의 3가지 처리 방식
조인에는 이너 조인(조인 조건 참), 아우터 조인(기준 조건 무조건 참), 카테시안 조인 3가지가 있다.
오라클 내부적으로 조인이 처리되는 방식을 살펴보자
두 개의 데이터 집합을 연결하려면 조인 조건만 작성해주면 된다. SQL을 작성하는 입장에서는 매우 간단하다.
하지만, 내부는 복잡하며, 성능 개선 작업에 큰 도움이 된다.
조인의 내부적인 처리 방식은 아래 3가지가 존재한다.
- NESTED LOOPS JOIN
- MERGE JOIN
- HASH JOIN
이너 조인을 NESTED LOOPS JOIN 방식 혹은 MERGE, HASH 방식으로 처리할 수도 있다.
아우터 조인도 마찬가지다 (카테시안은 HASH JOIN 불가능)
조인 처리 방식은 결과에는 영향이 없다
먼저 각자의 처리방식을 살펴보자
1.NL JOIN (NESTED LOOPS JOIN)
중첩된 반복문 형태로 데이터를 연결하는 방식이며, 가장 오래된 조인 방식이다.
M_CUS 테이블과 T_ORD 테이블에서 CUS_ID가 같은 데이터 끼리 NL조인 한다고 가정한다면 처리 과정은 다음과 같다.
1. M_CUS의 첫 번째 로우를 읽는다.
2. 1번 단계의 CUS_ID와 같은 CUS_ID를 가진 데이터를 T_ORD에서 검색한다. (T_ORD 전체 읽음)
3. M_CUS의 두번째 로우를 읽는다.
4. 3번과 마찬가지로 CUS_ID와 같은 CUS_ID를 가진 데이터를 T_ORD에서 검색한다 (T_ORD 전체 읽음)
위 과정에서 M_CUS 테이블을 읽는 것이 중첩된 반복문과 같다.
NL 조인에서 선행 집합(선행테이블)과 후행 집합(후행 테이블)의 정의가 매우 중요하다.
조인을 위해 먼저 접근하는 쪽이 선행집합, 뒤에 접근하는 쪽을 후행 집합이라고 한다.
선행 집합이 바깥 루프가 되고, 후행 집합이 안쪽 루프가 된다.
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) */
T1.RGN_ID, T1.CUS_ID, T1.CUS_NM, T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM M_CUS T1, T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID;
위 SQL은 M_CUS가 선행, T_ORD가 후행이다.
실행계획은 위와 같다.
1번 단계를 보면 NESTED LOOPS를 볼 수 있다.
1번 단계는 자신의 자식인 2번과 3번 단계를 NL로 처리하고 있다.
형제간에는 위에 있는 단계가 먼저 처리된다. 따라서 2번 단계가 선행 집합이고, 아래가 후행 집합이다.
후행 집합의 Starts가 2이다. NL 조인에서 후행 집합에 접근한 횟수를 의미한다.
NL조인은 선행 집합의 건수만큼 후행 집합을 반복 접근한다. 선행 집합인 M_CUS에는 90명의 고객이 존재한다.
그러므로 후행 집합인 T_ORD에는 90번의 접근이 발생해야 한다. 하지만 Starts는 2로 이는 부분 범위 처리 때문에 발생한 것이다.
위 표는 전체 데이터를 ACCESS했을 때 실행계획이다. T_ORD테이블을 90번이나 FULL SCAN하므로, 성능 문제가 있다.
NL 조인은 관계형 데이터베이스에서 가장 많이 사용하는 내부 조인 처리 방식이므로, 이를 잘 이해하고, 필요한 부분에 인덱스를 정확하게 만들어 준다면 가장 적인 비용으로 빠르게 조인 결과를 얻을 수 있다.
* 다만 NL 조인 방식은 많은 양의 데이터를 조인하기에는 한계가 있다.
2. MERGE JOIN
머지 조인은 두 데이터 집합을 연결 조건 값으로 정렬한 후 조인을 처리하는 방식이다.
정렬된 데이터를 차례대로 읽어가면서 조인을 수행한다.
연결 조건 기준으로 정렬되어 있어야만 조인이 가능하므로, 소트 머지 조인, 소트 조인이라 부르기도 한다.
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_MERGE(T2) */
T1.RGN_ID, T1.CUS_ID, T1.CUS_NM, T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM M_CUS T1, T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID;
위 SQL의 실행계획은 아래와 같다.
실행계획의 1단계에 MERGE JOIN이 있고, MERGE JOIN의 자식 단계 중에 4번 단계에는 SORT JOIN이라는 오퍼레이션이 있다.
자식의 결과 (TABLE ACCESS FULL - T_ORD) SORT JOIN을 위해 정렬하는 작업을 진행하고, 조인 조건 컬럼인 CUS_ID 기준으로 정렬한다.
M_CUS 테이블은 소트 작업을 하지 않는 대신, PK_M_CUS 인덱스를 INDEX FULL SCAN하고 있다.
인덱스 리프 블록은 인덱스 키값으로 정렬되어 있기 때문에 PK_M_CUS를 INDEX FULL SCAN하는 것은 CUS_ID를 정렬한 것과 같다.
NL 조인은 고객 테이블의 데이터 건수만큼 주문 테이블을 반복 접근한다.
반면에 머지 조인은 주문 테이블을 반복해서 접근할 필요가 없다.
단, 머지 조인을 위해서는 조인할 데이터를 먼저 정렬해야한다. 정렬의 속도가 성능 향상의 주요 포인트이다.
3. HASH JOIN
조인 성능 문제 대부분이 해시 조인으로 해결되는 경우가 많다. (그렇다고 무조건 사용하는 것은 좋지 않다 해시 조인은 더 많은 CPU와 메모리 자원을 사용하기 때문이다.)
만약 NL으로 충분히 처리할 수 있는 SQL을 해시 조인으로 처리하면, 시스템 전반적 성능 문제가 발생할 수 있다.
OLTP 시스템에서 자주 사용되는 핵심 SQL은 NL 조인으로 처리되도록 해야한다.
해시 조인은 해시 함수를 이용한 처리 방식이다. 일반적으로 대용량 데이터를 조인할 때 적합한다.
실제 SQL에 해시 조인을 사용해보고 실행 계획을 확인해보자
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_HASH(T2) */
T1.RGN_ID, T1.CUS_ID, T1.CUS_NM, T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM M_CUS T1, T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID;
USE_HASH 힌트를 사용해 해시 조인을 유도했다 실행계획은 다음과 같다.
실행계획 1번 단계를 보면 HASH JOIN이 나타났다. M_CUS 테이블을 FULL SCAN 한 후 T_ORD 테이블을 FULL SCAN하면서 해시 조인을 처리하고 있다.
해시 조인이 처리되는 과정을 간략하게 그려보면 다음과 같다.
1. 조인하려는 두 개의 테이블 중 고객 테이블을 선택해 읽어 들인다.
2. 고객을 읽어 들이면서 조인 조건으로 사용된 컬럼 값에 해시 함수를 적용한다.
3. 해시 함수의 결괏값에 따라 데이터를 분류해 해시 영역에 올려 놓는다.
4. 주문 테이블을 읽어 들인다.
5. 이때도 주문 테이블의 CUS_ID 값에 같은 해시 함수 처리를 한다.
6. 해시 함수의 결괏값에 따라 해시 영역에 있는 3번의 결과와 조인을 수행한다.
7. 4~6번 과정을 반복 수행하면서 조인 결과를 만들어 내보낸다.
해시 조인은 NL 조인처럼 후행 집합을 반복해서 접근하는 비효율이 없다. 머지 처럼 정렬 작업을 수행하지도 않는다.
하지만, 고비용의 해시 함수와 메모리의 일부인 해시 영역을 사용하는 비용이 추가 투입된다. NL 조인과 머지 조인의 단점을 모두 커버하지만, 시스템 자원을 소모하는 단점이 있다.