• 조인은 기본은 NL조인이다.
• NL조인은 인덱스를 이용한 조인이다.
• 소트 머지 조인, 해시 조인도 NL조인과 프로세싱 과정은 다르지 않다.

기본 메커니즘

아래 사원과 고객 테이블이있다. 이 두 테이블에서 1996년 1월 1일 이후 입사한 사원이 관리하는 고객 테이터를 추출하는 프로그램을 작성해보자

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

• NL조인은 Outer(선행)와 Inner(후행) 양쪽 테이블 모두 인덱스를 이용
• Outer 테이블은 사이즈가 크지 않으면, Table Full Scan을 할수도 있음. Table Full Scan을 하더라도 한번에 그치기 때문.
• Inner 쪽 테이블은 인덱스를 사용해야 함. Inner 루프에서는 관리사원번호 INDEX를 읽어야한다. 그렇지 않을시 Outer루프에서 읽은 건수만큼의 Table Full Scan을 반복함.

NL 조인 실행계획 제어

NL 조인을 제어할 때, 아래와 같이 use_nl을 사용한다.

select/*+ ordered use_nl(c) */
	e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
	and c.관리자사원번호 = e.사원번호

ordered 힌트는 옵티마이저에게 FROM 절에 기술한 수선대로 조인하라고 지시할때 사용한다.

위의 힌트는 사원테이블기준으로 고객테이블과 NL방식으로 조인하라는 뜻.

3개 이상 테이블을 조인할 때는 아래와 같이 사용.

select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */
from A, B, C, D
where ....

• B와 조인할 때 NL조인, C와 조인할 때 NL조인, D와 조인할때 해시조인

아래는 leading 힌트 써서 FROM 절을 사용하지 않고 순서 제어 가능

select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */
from A, B, C, D
where ....

NL조인을 사용하되 옵티마이저가 순서는 스스로 정하도록 맡김

select /*+ use_nl(A, B, C, D) */
from A, B, C, D
where ....

NL 조인 수행 과정 분석

select /*+ ordered use_nl(c) index(e) index(c) */
	e.사원번호, e.사원명, e.입사일자, 
	c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
	and e.입사일자 >= '19960101'
	and e.부서코드 = 'Z123'
	and c.최종주문금액 >= 20000

* 사원_PK : 사원번호
* 사원_X1 : 입사일자
* 고객_PK : 고객번호
* 고객_X1 : 관리사원번호 
* 고객_X2 : 최종주문금액 

1. 입사일자 ≥ ‘19960101’ 조건을 만족하는 레코드를 찾으려고 사원_X1 인덱스를 Range 스캔한다.
2. 사원_X1 인덱스에서 읽은 ROWID로 사원 테이블을 액세스해서 부서코드 = ‘Z123’필터 조건을 만족하는지 확인한다.
3. 사원테이블에거 읽은 사원번호 값으로 조인 조건을 만족하는 고객 쪽 레코드를 찾으려고 고객_X1 인덱스를 Range 스캔한다.
4. 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스해서 최종주문금액 ≥ 20000 필터 조건을 만족하는지 확인한다.

NL 조인 튜닝 포인트

1. 첫번째 튜닝 포인트
   1. 위의 그림에서 사원_X1 인덱스를 읽고나서 사원 테이블을 액세스 하는 부분.
2. 두번째 튜닝 포인트
   1. 고객_X1 인덱스를 탐색하는 부분
3. 세번째 튜닝 포인트
   1. 고객_X1 인덱스를 읽고 나서 고객 테이블을 액세스 하는 부분
4. 마지막 튜닝 포인트
   1. 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다는 사실을 기억하자!

올바른 조인 메소드 선택

OLTP에서 튜닝할 때는 일차적으로 NL조인부터 고려하는 것이 올바른 순서이다.

• NL조인 튜닝 포인트에 따라 각 단계 수행 일량을 분석해서 과도한 랜덤 액세스가 발생하는 지점부터 파악
• 조인 순서 변경해서 개선
• 더 좋은 인덱스 있는지 파악
• 인덱스 추가 또는 구성 변경 고려
• NL조인으로 효과내기 어려우면, 소트 머지 조인이나 해시 조인을 검토해본다.

NL 조인 특징 요약

• 랜덤 액세스 위주의 조인 방식
  • 대량의 데이터 조인할 때 불리함.
• 한 레코드씩 순차적 진행
  • 아무리 큰 테이블을 조인해도 매우 빠른 응답 속도를 낼 수 있음.
• 다른 조인방식과 다르게 인덱스 구성 전략이 특히 중요
• OLTP 시스템에 적합함.

NL 조인 확장 메커니즘

• 오라클의 경우 버전이 올라가면서 조인 성능을 높이기 위해 테이블의 Prefecth, Batch I/O 기능이 도입되었다.
• 테이블 Prefetch
  • 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능.
  • 실행계획에 인덱스 rowid에 의한 Inner 테이블 액세스가 Nested Loops 위쪽에 표시되면, Prefetch 기능이 활성화됨을 의미한다.
• 배치 I/O
  • 디스크 I/O Call을 미뤘다가 일정 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다.
  • Inner쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘으로 테이블 액세스는 나중에 하지만 부분범위처리는 정상적으로 작동한다.
  • 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리한다. (부분범위처리)