[오라클 DB조인] NL조인(Nested Loop)의 원리 및 사용방법(힌트, 플랜)

1. NL 조인의 개념

Nested(중첩된) Loop(루프)의 뜻으로 아주 간단하게는 중첩된 반복으로, 흔히 이중 포문의 형태를 띄고 있다.
 

2. NL조인의 동작 원리

앞서 이중 포문의 형태를 띄고 있다고 말하였는데,  비유하여 설명하자면 (아래 그림과 같이 확인 필요)
1) 바깥포문(외부테이블 : 사원_X1 테이블)에서 조건(선택조건 : 입사일자>="19960101" and 부서코드="Z123")에
따라 하나의 결과값을 안쪽포문으로 전달(사원번호 전달)
 
2) 안쪽포문(내부테이블 : 고객_X1 테이블)로 들어와,
내부의 모든 건수를 확인(선택조건:관리사원번호와 최종주믄금액>=20000)
 
3) 조건을 만족하여 출력 집합에 포함
 
4) 바깥 포문을 다 확인할때까지 1)~3)을 계속 반복
 
위의 과정을 생각하면서 아래의 그림 순서대로 잘 따라가보세요. 
 

<친젏한 SQL 튜닝 조인편 그림>

 

3.NL 조인의 튜닝 포인트

앞의 조인 동작을 보다보면 몇가지의 조인 튜닝 포인트를 찾을 수 있다
 
1) 사원_X1의 인덱스를 입사일자+부서코드로 설정
먼저 사원_X1테이블에서 입사일자를 인덱스로, 부서코드를 필터조건으로 진행하는 모습을 볼 수 있다.
위의 상황에서 부서코드의 필터조건으로 많은 건수의 데이터 필터가 된다고 한다면 필요없는 랜덤 엑세스가 발생하게 된다.
그렇다면 애초에 입사일자+부서코드를 인덱스로 설정하여 랜덤엑세스를 줄이는편이 좋다.
* 랜덤 엑세스 방식은 간단해 보이지만 내부적으로 많은 프로세스 거친다.
 
2) 사원_X1 결과 건수를 최대한 줄인다
사원_x1테이블에의 결과값수만큼 고객_X1테이블을 한바퀴씩 다 돌고있다.
outer테이블을 줄일수록 전체건수를 줄일수 있어 빠른 결과를 찾을 수 있음
 
3) 고객_X1 의 인덱스를 관리사원번호+최종주문금액으로 설정
1)의 사유와 같다
 

4.NL조인의 힌트 사용방법

select /*+ use_nl(A, B) */
  from A_TABLE as A
       , B_TABLE as B
where
use_nl : NL조인을 사용(A와 B의 순서는 옵티마어지가 선택)
순서를 정하고 싶을떈 ordered 힌트를 사용하여  정할 수 있음
==>/*+ ordered  use_nl(B) */ : A가 아우터테이블 역할

5.NL 조인 튜닝 특징

1) 랜덤 엑세스 위주의 조인 방식이다
레코드 하나를 읽으려고 할 떄에도 블록을 통째로 읽는 행위를 한다. 그렇기 때문에 대량의 데이터를 조인할때 불리함
 
2) 하나의 레코드씩 순차적으로 진행
조인의 동작원리를 살펴보면 건건이 찾아가는 모습을 볼 수있다.
이러한 특징 때문에 대량의 건을 조인할 때 불리하다.
하지만 위의 특징으로 온라인성 업무에서 부분적으로 보여줘야하는 경우에는 빠르게 조회가 가능하다
 
3) 다른 조인 방식과 비교할 떄보다 인덱스의 구성 전략이 특히 중요
NL조인은 인덱스의 영향을 많이 받으며, 추후 다른 조인을 볼경우 더 느껴질 수 있다.
 

6.NL조인의 확장 매커니즘

조인 튜닝 특징은 매번 건건이 데이터를 찾는 특징을 있다고 앞에서 볼 수 있었다.
이게 조인 튜닝의 불리함으로 작용했었다.
하지만 버전이 올라가면거 "건건이"의 개념에서 "모아서"로 확장을 하였고 이는 가칭 테이블 prefetch, 배치 I/O라는 기능이 추가되었다.
 
1) 일반적인 조인의 실행계획

 
2) 테이블 prefetch 실행계획

 
3) 배치 I/O 실행계획

 

테스트 데이터 생성 방법 : 

2025.03.20 - [IT/SQLP] - [데이터 생성] 오라클 DB 데이터 생성 쿼리모음(테이블,인덱스,데이터 등)