[개념정리] 조인 튜닝 - NL 조인

인덱스를 이용한 조인으로 인덱스 튜닝 원리 적용.

Outer, Inner 테이블 모두 인덱스 사용(Outer 테이블크기가 작다면 Table Full Scan 이용할 수도), Inner 테이블은 인덱스 사용(인덱스 사용 안할 경우Outer 결과집합 건수만큼 Table Full Scan하며 조인 하기 때문)
(옵티마이저 힌트 - use_nl(테이블 명 or alias))

Nl 조인은 그 수행 과정에 있어, 중첩 루프 처럼 Outer 테이블의 한 레코드씩 순차적으로 진행한다.

NL 조인 특징

• 랜덤 액세스 방식 - 레코드 하나를 읽기 위해 블록 전체 Read (대량 데이터 조인시 NL조인 불리한 이유)
• 한 레코드씩 순차 진행 - 부분범위 처리가 가능한 상황에서 빠른 응답
• 인덱스 구성 전략 중요 - 조인 컬럼 인덱스가 있는지, 없는지 등..
• 결과적으로 소량 데이터 처리, 부분 범위 처리가 가능한 OLTP(온라인 트랜잭션 처리 시스템)에 적합

NL 조인 튜닝 포인트

select  /* ordered use_nl(c) index(e) index(c) */
		e.no, e.name, e.hiredate, 
        c.no, c.name, c.phonenumber, c.orderCost
from emp e, customer c
where c.managerNo = e.no
and	  e.hiredate >= '19960101'
and   e.deptcode = 'Z123'
and   c.orderCost >= 20000

1. 위의 쿼리 sql trace확인했을 때 인덱스인 hiredate로 추출한 양에서 deptcode로 테이블 필터되는 양이 많다면, 인덱스에 deptcode를 추가해주어야 할 것이다.
2. Outer 테이블의 결과 건수가 조인 액세스 횟수가 되는데, 조인 액세스 건수가 많고  Inner테이블의 인덱스. Depth가 크다면 블록  I/O 가 많아진다. 
3. Inner 테이블 인덱스(여기서는 managerNo) 조인 후 추가 조건절 (orderCost)로 걸러지는 양이 많다면, 인덱스에 컬럼을 추가할 수 있다.

NL 조인 확장 메커니즘

오라클 버전 상향에 따라 NL조인성능을 높이기 위한 방식이 도입(2 방식 모두 읽는 블록 마다 건건 I/O call 발생을 막기 위한 방법)

1. 테이블 Prefetch :  인덱스를 이용, 테이블 액세스하다가 디스크 I/O가 필요해 질 때 이어서 읽게 될 블록 까지 미리 읽어 버퍼캐시에 적재 (힌트 : nlj_prefetch / no_nlj_prefetch)
2. 배치 I/O : 디스크 I/O call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리(힌트 : nlj_batching, no_nlj_batching)

Inner 테이블 블록을 모두 버퍼 캐시에서 읽는다면 테이블 Prefetch, 배치 I/O를 사용하는 것에 아무 성능 차이를 볼 수 없다. 

하지만, 디스크 I/O가 발생되고 배치 I/O 방식을 사용한다면, (11g부터) 정렬 순서를 보장하지 않기 때문에 주의해야한다.

결과집합이 항상 일정 순서로 출력되기를 원한다면 배치I/O기능이 작동하지 않도록 힌트 no_nlj_batching을 써주거나 order by 절을 사용해 정렬 기준을 명시해야 한다.