SQL튜닝 - Chapter04.조인튜닝 -1- NL조인

NL조인 ( Nested Loop 조인 )

- 조인의 기본.

- 인덱스를 이용한 조인이다.

 

= 조인을 할 경우 왠만한 상황에선 NL조인을 사용한다. 데이터가 대용량일땐 해시조인을 사용하는 것이 좋다.

 

NL조인 기본 메커니즘

기본적으로 범위가 작은 테이블을 왼쪽에 두어야한다.

 

NL조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다.

 

테이블이 커도 인덱스를 활용하기 때문에 빠르다

 

 

NL 조인 실행계획 제어

 

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

use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용.

힌트를 같이 사용했으므로 사원 테이블 기준으로 고객 테이블과 NL방식으로 조인하라는 뜻

 

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

세 개 이상 테이블을 조인할 때는 이렇게 작성한다

 

A → B → C → D 순으로 조인하되, B와 조인할 때 그리고 이어서 C와 조인할 때는 NL 방식, D와 조인할 때는 해시 방식으로 조인하라는 뜻이다.

 

ordered 대신 leading 힌트를 사용할 수도 있다.

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

 leading을 사용하는 이유는 원본을 건드리지 않기 위해서이다. 

 

 

 

NL 조인 실행계획 제어

 

 

인덱스 구성

- 사원_PK : 사원번호

- 사원_X1 : 입사일자

- 고객_PK : 고객번호

- 고객_X1 : 관리사원번호

- 고객_X2 : 최종주문금액

 

 

조건절 비교 순서를 나열하면 2 → 3 → 1 → 4 

 

1. 조건절 번호 2 : 입사일자 >= '19960101' 조건을 만족하는 레코드를 찾기 위해 사원_X1 인덱스를 Range 스캔

2. 조건절 번호 3 : 사원_X1 인덱스에서 읽은 ROWID로 사원 테이블을 액세스해서 부서코드 = 'Z123' 필터 조건을 만족하는지 확인

3. 조건절 번호 1 : 사원 테이블에서 읽은 사원번호 값으로 조인 조건( c.관리사원번호 = e.사원번호 )을 만족하는 고객 쪽 레코드를 찾으려고 고객_X1 인덱스를 Range 스캔

4. 조건절 번호 4 : 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스해서 최종주문금액 >= 20000 필터 조건을 만족하는 지 확인

 

NL조인 수행 절차

 

 

NL 조인 특징

- 랜덤 액세스 위주의 조인 방식 : 레코드 하나를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다. 인덱스 구성이 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유.

 

- 조인을 한 레코드식 순차적으로 진행한다는 점. 대량 데이터 처리 시 치명적인 한계를 드러내지만,

아무리 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있다. 부분범위 처리가 가능한 상황에서 그러하다

 

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

적합한 조인방식.

[온라인 트랜잭션 처리의 예 : 주문을 할 수 있는 시스템 ( 쇼핑몰 등) ]

 

 

오라클 7 버전에서는 Rows 부분에 각 단계의 처리 건수를 보여주어서 실제 스캔량을 쉽게 확인할 수 있게 했다.

8i부터 바뀌기 시작해 9i에서 스캔 후 출력 건수를 보여주는 방식으로 완전히 바뀌어서 각 단계의 처리 건수를 따로

분석해야 하는 불편함이 생겼다

그래서 9iR2부터 각 처리 단계별 논리적인 블록 요청 횟수( cr : 캐시리드 )와 디스크에서 읽은 블록 수( pr : 피지컬 리드 ) 그리고 디스크에 쓴 블록 수( pw ) 등을 표시하기 시작했다.

 

아래가 해당하는 예

Rows  Row Source Operation
----  ---------------------
5     NASTED LOOPS (cr=112 pr=34 pw=0 time=122 us) [ 여기서 us는 마이크로 세컨드를 의미 ]
3	  	TABLE ACCESS BY INDEX ROWID OF 사원 (cr=105 pr=32 pw=0 time=118 us)
3			INDEX RANGE SCAN OF 사원_X1 (cr=102 pr=31 pw=0 time=16 us)
5		TABLE ACCESS BY INDEX ROWID OF 고객 (cr=7 pr=2 pw=0 time=4 us)
8			INDEX RANGE SCAN OF 고객_X1 (cr=5 pr=1 pw=0 time=0 us)