4.1 NL 조인

NL조인은 인덱스를 이용한 조인이다. 일반적으로 양쪽 테이블 다 인덱스를 사용하지만 OUTER 쪽 테이블은 사이즈가 크지 않다면 인덱스를 이용하지 않을 수 있다. INNER 쪽 테이블은 Table Full Scan 시 한 번으로 그치지 않기 때문에(OUTER에서 읽은 건수만 큼 Table Full Scan 함) 반드시 인덱스를 사용해야 한다.

---

4.1.1 기본 메커니즘

// <C, JAVA>
for(i=0; i<100; i++){        //OUTER LOOP
    for(j=0; j<100; j++){    //INNER LOOP
        // Do Anything
    }
}

위 처럼 NL조인은 중첩 루프문(Nested Loop)의 수행구조와 같다.(page 258 예시 참고)

---

4.1.2 NL 조인 실행계획 제어

아래는 NL조인 실행계획이다.

Execution Plan
--------------------------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS
1    0        NESTED LOOPS
2    1            TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
3    2                INDEX (RANGE SCAN) OF '사원_X1' (INDEX)
4    1            TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
5    4                INDEX (TANGE SCAN) OF '고객_X1' (INDEX)

select /*+ ordered use_nl(c) */    *
FROM    e, c
-- ordered : from 절에 기술한 순서대로 조인하라 // use_nl : NL방식으로 조인해라.
-- e(드라이빙 또는 아우터 테이블)을 기준으로 c와 NL 조인하게 된다.

select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *
from    A,B,C,D
-- A-B-C-D순으로 조인하되, 마지막 D와 조인할 때는 해시 방식으로 조인.

select /*+ use_nl(A,B,C,D) */ *
from    A,B,C,D
-- 4개의 테이블을 NL조인하되, 순서는 옵티마이저가 정한다.

---

4.1.4 NL 조인 튜닝 포인트, 4.1.3 NL 조인 수행 과정 분석

(page 261 참고)

온라인 트랜잭션 처리(OLTP, 소량데이터, 부분범위 처리 가능) 튜닝에서는 일차적으로 NL조인부터 고려하는 것이 옳다. 조인순서 변경, 효과적 인덱스 탐색, 인덱스 추가나 구성 변경 등을 고려하여 튜닝 포인트를 찾는다. 여러 방안을 검토한 결과 좋은 성능을 내기 어렵다고 판단될 때, 소트 머지 조인이나 해시 조인을 검토한다.

---

4.1.5 NL 조인 특징 요약

1. 랜덤 액세스 위주.
   • 레코드 하나를 읽으려고 블록을 통째로 읽는 방식.
   • 메모리 버퍼에서 빠르게 읽더라도 비효율 발생.
   • 인덱스 구성이 아무리 완벽해도 대량 데이터 조인 시 NL 조인이 불리한 이유.
2. 한 레코드씩 순차적으로 진행.
   • 부분범위 처리를 활용한다면, 먼저 액세스되는 테이블 처리 범위에 의해 전체 일량이 결정 된다.
   • 첫 번째 단점 해소.
3. 인덱스 구성이 중요.
   • 다른 조인들보다 인덱스 구성에 따른 효율이 천차만별이다.

-> NL조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리(OLTP) 시스템에 적합한 조인 방식이다.

---

4.1.6 NL 조인 튜닝 실습

(page 266 참고)

Rows    Row Source Operation
----    -----------------------------------------------------------------------------
   5    NESTED LOOPS (cr=2732 pr=386 pw=0 time=...)
2780        TABLE ACCESS BY INDEX ROWID 사원 (cr=166 pr=2 pw=0 time=...)
2780            INDEX RANGE SCAN 사원_X1 (cr=4 pr=0 pw=0 time=...)
   5        TABLE ACCESS BY INDEX ROWID 고객 (cr=2566 pr=384 pw=0 time=...)
   8            INDEX RANGE SCAN 고객_X1 (cr=2558 pr=383 pw=0 time=...)

cr : 각 처리 단계별 논리적인 블록 요청 횟수 (사원X_1 인덱스로부터 읽은 블록은 4개)
pr : 디스크에서 읽은 블록 수
pw : 디스크에 쓴 블록 수
위 트레이스 결과로 사원테이블과 고객테이블 조인 시 2,780번 시도와 조인 성공 후 필터링까지 마친 최종 결과집합은 5 건이라는 걸 알 수 있다.

---

4.1.7 NL 조인 확장 메커니즘

버전이 올라가면서 오라클은 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치I/O 기능을 도입했다.

• 테이블 Prefetch : 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능.
• 배치 I/O : 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼버에 처리하는 기능.

올아클에서는 전통적인 실행계획, 테이블 Prefetch 실행계획, 배치 I/O 실행계획 모두 나타나는데 테이블 블록을 모두 버퍼캐시에서 읽는다면 성능 차이는 없지만 디스크에서 읽게 되면 성능 차이가 나타날 수 있다. 특히 배치 I/O 실행계획이 나탈 때는 결과집합의 정렬 순서도 다를 수 있다.(따라서 ODER BY 절을 명시하는게 좋다.) (page 270 예시 참고)