NL 조인의 원리

조시형님의 친절한 SQL 튜닝을 읽고 개인적으로 공부한 내용을 정리한 글입니다.

---

NL 조인은 Nested Loop 조인의 약자로 중첩 루프 문과 같은 방법으로 조인이 이루어진다.

 

 

위 두 테이블를 사원 번호로 조인한다고 가정해보자.

 

프로그래밍 식으로 로직을 구현해보면 다음과 같이 할 수 있다.

 

for(int i=0;i<사원.size();i++){
	for(int j=0;j<고객.size();j++){
    	if(사원[i].번호 == 고객[j].관리사원번호){
        	// 찾으려는 레코드
        }
    }
}

 

사원 테이블의 사원번호를 기준으로 고객 테이블 전체 레코드를 순회하여 기준인 사원번호와 일치하는 레코드를 찾는 방법이다.

 

일반적으로 바깥쪽 루프에 해당하는 테이블을 Outer Table이라 하고 안쪽에 있는 테이블을 Inner Table이라고 한다.

 

💡 Outer와 Inner를 굳이 나눈 이유는 동작 원리를 살펴보면 큰 차이가 있기 때문이다.

 

Outer Table에 있는 레코드는 풀스캔을 하더라고 한번에 그치지만, 🚨 Inner Table을 풀스캔하게 되면 Outer Table의 레코드 수 만큼 풀스캔을 해야된다.

 

 

 

비효율이 존재하기 때문에 물론, 두 테이블을 풀스캔해서 찾는 것은 아니고 인덱스 테이블을 사용한다.

 

예로, 사원 테이블을 입사일자로 인덱스가 구성되어 있고 고객 테이블을 관리 사원 번호로 인덱싱 되어 있다면

 

select * from 사원 e, 고객 c
where e.입사일자 >= '19960101' 
and c.관리사원번호 = e.사원번호

 

위와 같은 쿼리를 실행 할 때

 

입사 일자를 기준으로 정렬되어 있는 사원의 인덱스 테이블에서 입사일자 조건에 맞는 인덱스 레코드를 찾고

 

해당 레코드가 저장하고 있는 테이블 블록 정보를 바탕으로 테이블 블록에 접근하여 사원번호를 알아내고

 

사원번호로 정렬되어 있는 고객의 인덱스 테이블에서 이전에 찾은 사원 번호 조건에 맞는 인덱스 레코드를 찾아 최종적으로 사원 테이블의 레코드로 접근하는 방식이다.

 

총 2번의 인덱스 테이블을 거치게 된다.

 

🚨 만약, 인덱스로 접근하지 않았다면, 고객 테이블을 풀스캔하여 찾으려는 사원번호를 가진 레코드를 찾아야 하는데 이는 매우 비효율적일 수 있다.

 

 

NL 조인 튜닝해보기

SELECT * FROM 사원 e, 고객 c
WHERE e.입사일자 >= '19960101'
AND e.부서코드 = 'Z123'
AND c.관리사원번호 = e.사원번호
AND c. 최종주문금액 >= 20000

 

위와 같은 쿼리를 실행 후,

 

ROWS |  OPERATION
5    |NESTED LOOPS
3    |	TABLE ACCESS BY INDEX ROWID OF 사원
2780 |    	INDEX RANGE SCAN OF 사원_X1
5    |    TABLE ACCESS BY INDEX ROWID OF 고객
8    |    	INDEX RANGE SCAN OF 고객_X1

// 사원_X1 = 입사일자
// 고객_X1 = 관리사원번호

 

쿼리 실행 시 트레이스 결과가 위와 같다고 했을 때, 무엇이 문제일까?

 

1️⃣ 먼저, 인덱스인 입사일자를 기준으로 레코드를 필터링하고, 테이블에 랜덤 엑세스한 횟수가 2780번인 것에 반해

 

최종적으로 부서 코드로 걸러진 결과는 3건에 그친다. 

 

랜덤 I/O를 줄이기 위해 [입사일자 + 부서코드] 로 구성된 인덱스를 추가하고 사용한다면 랜덤 I/O를 줄일 수 있을 것이다.

 

2️⃣ 이렇게 새로운 인덱스를 정의한다고 끝날 문제가 아니다. 각 단계의 처리 건수를 확인해보고, [입사일자 + 부서코드] 로 구성했을때와 [부서코드 + 입사일자] 로 구성했을때를 비교해보고 더 좋은 성능을 갖춘 인덱스가 무엇인지 판단하고 결정하면 더 좋은 인덱스를 구성할 수 있다.