NL(Nested Loops) 조인
일반적으로 NL조인은 OUTER와 INNER 양쪽 테이블 모두 인덱스를 이용한다.
OUTER쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다. Table Full Scan 하더라도 그것은 한 번에 그치기 때문이다. 반면, INNER쪽 테이블은 인덱스를 사용해야 된다.
INNER 루프에서 데이터를 검색할 때 인덱스를 이용하지 않으면 OUTER 루프에서 읽은 건수만큼 Table Full Scan을 반복하기 때문이다.
결국, NL조인은 '인덱스를 이용한 조인 방식'이라고 할 수 있다.
여기서 기억할 것은, 각 단계를 모두 완료하고 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다는 사실이다.
NL 조인 튜닝 포인트
쿼리
SELECT E.사원번호, E.사원명, E.입사일자
, C.고객번호, C.고객명, C.전화번호, C.최종주문금액
FROM 사원 AS E, 고객 AS C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '20250101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000인덱스 구성
사원_PK : 사원번호
사원_X1 : 입사일자
고객_PK : 고객번호
고객_X1 : 관리사원번호
고객_X2 : 최종주문금액첫 번째 튜닝 포인트는 사원_X1 인덱스를 읽고 나서 사원 테이블을 엑세스 하는 부분이다.
여기서는 단일 컬럼 인덱스를 ">=" 조건으로 스캔했으므로 비효율 없이 6(=5+1)건을 읽었고, 그만큼만 테이블 랜덤 액세스가 발생했다. 만약 사원 테이블로 아주 많은 양의 랜덤 액세스가 발생했고, 테이블에서 부서코드 = 'Z123' 조건에 의해 필터링 되는 비율이 높다면 사원_X1 인덱스에 부서코드 컬럼을 추가하는 방안을 고려해야 된다.
두 번째는 고객_X1 인덱스를 탐색하는 부분이다.
고객_X1 인덱스를 탐색하는 횟수, 즉 조인 액세스 횟수가 많을수록 성능이 느려진다. 조인 액세스 횟수는 OUTER 테이블인 사원을 읽고 필터링한 결과 건수에 의해 결정된다. 만약 부서코드 조건을 만족하는 레코드 10만 건이고 고객_X1 인덱스 Depth가 3이라면, 수직적 탐색 과정만 30만개 블록을 읽어야 하고, 리프 블록을 수평적으로 스캔하는 과정에서 추가적인 블록 I/O가 발생한다.
세 번째는 고객_X1 인덱스를 읽고 나서 고객 테이블을 액세스하는 부분이다.
최종주문금액 >= 20000 조건에 의해 필터링되는 비율이 높다면 고객_X1 인덱스에 최종주문금액 컬럼을 추가하는 방안을 고려해야 된다.
마지막으로, 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다는 사실도 기억해야된다.
사원_X1 인덱스를 스캔하면서 추출한 레코드가 많으면, 사원 테이블로 랜덤 액세스하는 횟수, 고객_X1 인덱스를 탐색하는 횟수, 고객 테이블로 랜덤 액세스하는 횟수가 전반적으로 많아진다.
NL 조인 특징 요약
NL 조인의 첫 번째 특징은 랜덤 액세스 위주의 조인 방식이라는 점이다. 레코드 하나를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다. 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유다.
두 번째 특징은 조인을 한 레코드씩 순차적으로 진행한다는 점이다. 첫 번재 특징 떄문에 대량 데이터 처리 시 매우 치명적인 한계를 드러내지만, 반대로 이 두 번째 특징 때문에 아무리 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있다. 부분범위 처리가 가능한 상황에서 그렇다. 부분범위 처리를 활용한다면, 사용자가 조회 버튼을 누르자마자 결과 집합을 출력하기 시작한다.
순차적으로 진행하므로 먼저 액세스되는 테이블 처리 범위에 의해 전체 일량이 결정되는 특징도 나타난다.
마지막으로, 다른 조인 방식과 비교할 때 인덱스 구성 전략이 특히 중요하다는 것도 NL조인의 중요한 특징이다. 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.
이런 여러가지 특징을 종합할 때, NL조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 OLTP 시스템에 적합한 조인 방식이라고 할 수 있다.
NL 조인 자가 진단
SELECT *
FROM PRA_HST_STC AS A, ODM_TRMS AS B
WHERE A.SALES_ORG_ID = :sale_org_id
AND A.STRD_GRP_ID = B.STRD_GRP_ID
AND A.STRD_ID = B.STRD_ID
ORDER BY A.STC_DT DESC인덱스 생성 요청
PRA_HST_STC_N1 : SALES_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT해당 인덱스 생성 요청은 NL 조인의 특성을 충분히 고려하지 못한 설계이다.
NL 조인은 OUTER 테이블의 결과를 기준으로 INNER 테이블을 반복 탐색하는 방식이므로, 성능의 핵심은 INNER 테이블의 조인 컬럼에 대한 인덱스 여부에 있다.
그러나 본 요청은 OUTER 테이블에만 인덱스를 집중하고 있어 NL 조인 효율을 높이지 못한다.
SELECT *
FROM PRA_HST_STC AS A, ODM_TRMS AS B
WHERE A.Sales_ORG_ID = :sale_org_id
AND B.STRD_GRP_ID = A.STRD_GRP_ID
AND B.STRD_ID = A.STRD_ID
ORER BY A.STC_DT DESC그리고 이런식으로 INNER 테이블 Alias를 왼쪽에 기술하는 습관을 가지는 것이 좋다고 한다.
"이 테이블을 인덱스로 탐색한다"는 의미를 즉시 이해하게 만들기 위한 실무 습관이라고 함~
소트 머지(Sort Merge) 조인
조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때, 옵티마이저는 NL 조인 대신 소트 머지 조인이나 해시 조인을 선택한다.
SGA VS PGA
공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있다. 여러 프로세스가 공유할 수 있지만, 동시에 액세스할 수 는 없다. 동시에 액세스하려는 프로세스 간 액세스를 직렬화하기 위한 LOCK 매커니즘으로서 Latch가 존재한다. 데이터 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 가장 핵심적인 구성요소이며, 블록을 읽으려면 버퍼 LOCK도 얻어야 된다.
오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 자신만의 고유 메모리 영역을 갖는다. 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA(Process / Program Private Global Area)라고 부르며, 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다. 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블스페이스를 이용한다.
PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간이므로 래치 매커니즘이 불필요하다. 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.
소트 머지 조인의 기본 매커니즘
- Sort 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
- Merge 단계 : 정렬한 양쪽 집합을 서로 Merge한다.
소트 머지 조인이 빠른 이유
NL 조인은 모든 DBMS가 공통으로 제공하는 가장 전통적인 조인 방식이다. 그런 NL 조인의 치명적인 단점은 대량 데이터를 조인할 때 성능이 느리다는 데 있다. 소트 머지 조인과 해시 조인이 개발된 이유다.
그럼 NL 조인과 소트 머지 조인의 성능은 어디에서 차이가 발생할까?
NL 조인은 단적으로 말해 "인덱스를 이용한 조인 방식"이다. 조인 과정에서 액세스 하는 모든 블록을 랜덤 액세스 방식으로 "건건이" DB 버퍼캐시를 경유해서 읽는다. 즉, 인덱스든 테이블이든, 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다. 버퍼캐시에서 찾지 못한 블록은 "건건이" 디스크에서 읽어 들인다. 인덱스를 이용하기 때문에 인덱스 손익 분기점 한계를 그대로 드러낸다. 이것이 대량 데이터 조인에 NL 조인이 불리한 이유다.
반면, 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 "일괄적으로" 읽어 PGA에 저장한 후 조인한다. PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 그렇기 때문에 대량 데이터 조인에 유리하다.
주용도
해시 조인의 등장으로 소트 머지 조인의 쓰임새가 예전만 못하지만, 해시 조인은 조인 조건식이 등치(=) 조건이 아닐 때는 사용할 수 없다는 단점이 있다.
그래서 소트 머지 조인은 아래와 같은 상황에 주로 사용된다.
- 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
- 조인 조건식이 아예 없는 조인(Cross Join)
해시(Hash) 조인
NL 조인은 인덱스를 이용한 조인 방식이므로 인덱스 구성에 따른 성능 차이가 심하다. 인덱스를 아무리 완벽하게 구성해도 랜덤 I/O 때문에 대량 데이터 처리에 불리하고, 버퍼캐시 히트율에 따라 들쭉날쭉한 성능을 보인다. 소트 머지 조인과 해시 조인은 조인 과정에 인덱스를 이용하지 않기 때문에 대량 데이터 조인할 때 NL 조인보다 훨씬 빠르고, 일정한 성능을 보인다.
기본 매커니즘
- Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블을 생성한다.
- Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.
해시 조인이 빠른 이유
Hash Area에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 조인 프로세승 자체는 NL 조인과 같다.
하지만 해시 조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.
해시 조인과 소트 머지 조인 둘 다 조인 오퍼레이션을 PGA에서 처리한다는 공통점을 갖는다. 그럼에도 해시조인 더 빠르다. 이유가 뭘까?
PGA에서 데이터를 탐색하는 알고리즘 차이도 있찌만, 그 효과는 미미하다.
두 조인 메소드의 성능 차이는 조인 오퍼레이션을 시작하기 전, 사전 준비작업에 기인한다.
소트 머지 조인에서 사전 준비작업은 "양쪽" 집합을 모두 정렬해서 PGA에 담는 작업이다.
PGA는 그리 큰 메모리 공간이 아니므로 두 집합 중 어느 하나가 중대형 이상이면, Temp 테이블스페이스, 즉 디스크에 쓰는 작업을 반드시 수반한다.
해시 조인에서 사전 준비작업은 양쪽 집합 중 어느 "한쪽"을 읽어 해시 맵을 만드는 작업이다. 해시 조인은 둘 중 작은 집합을 해시 맵 Build Input으로 선택하므로 두 집합 모두 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면, Temp 테이블 스페이스, 즉 디스크에 쓰는 작업은 전혀 일어나지 않는다.
정리하면 해시 조인은 NL 조인처럼 조인 과정에서 발생하는 랜덤 액세스 부하가 없고, 소트 머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없다. 해시 테이블을 생성하는 비용이 수반되지만, 둘 중 작은 집합을 Build Input으로 선택하므로 대개는 부담이 크지 안핟.
Build Input이 PGA 메모리에 담길 때, 즉 인메모리 해시 조인일 때 가장 효과적인 이유가 바로 여기에 있다.
그렇다고 Build Input이 Hash Area 크기를 초과하면 다른 조인 메소드를 선택하라는 뜻은 아니다.
