SQL 튜닝이론 - NL 조인 (NESTED LOOP) 정리(특징, NL조인 인덱스 구성조건, 유도 힌트, 부하 포인트, PREFETCH, BATCH I/O)

 

SQL 튜닝이론 - NL 조인 (NESTED LOOP) 정리(특징, NL조인 인덱스 구성조건, 유도 힌트, 부하 포인트, PREFETCH, BATCH I/O)

 

 

NL 조인은 두 테이블이 조인을 할 때, 드라이빙 테이블( Outer 테이블)에서 결합 조건에 일치하는 레코드를 내부 테이블(Inner Table)에서 조인하는 방식입니다.

 

NL 조인의 특징

 

NL 조인은 랜덤 엑세스 위주의 조인방식입니다. 따라서 인덱스 구성이 완벽하더라도 대량 데이터를 조인할 때 매우 비효율 적입니다. 다음으로 한 레코드씩 순차적으로 진행합니다. 이로인해 부분범위처리가 가능한 상황에서 빠른 속도를 낼 수 있습니다. 순차적으로 진행하는 특징으로 인해 엑세스되는 테이블의 처리 범위에 의해 전체 일량이 결정됩니다. 다른 조인방식과 비교했을때, 인덱스 구성 전략이 특히 중요합니다.

 

이런 특징을 종합할때 NL 조인은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합합니다.

 

NL 조인의 테이블별 인덱스 구성조건

 

조인컬럼은 CHECK TABLE의 인덱스에 반드시 포함되야하지만 DRIVING 테이블에 반드시 포함될 필요는 없습니다. CHECK TABLE(INNER TABLE) 의 인덱스 선두에 조인칼럼이 위치하는 것 자체로는  성능에 영향을 주지않습니다. 단, 후행 칼럼이 조건비교절로 사용될 때 조건비교절로 사용된 칼럼이 선두에 위치하는 것 보다는 성능상 유리합니다.

 

NL 조인 유도 힌트

 

ORACLE에서 조인 시, NL 조인으로 유도하기 위한 힌트는 USE_NL(inner_table name) 입니다.

 

/*+ ordered use_nl(c) use_hash(t) */
/*+ leading(o c t) use_nl(c) use_hash(t)*/
FROM 주문 o, 고객 c, 결제방식 t
where o.주문일자 >= trunc(sysdate)
and c.고객번호 = o.고객번호
and t.결제방식코드 = o.결제방식코드

 

SQL SERVER 조인 시, NL 조인으로 유도하기 위한 힌트는 LOOP JOIN 입니다. FROM 절에 나열한 순으로 조인하고자 할때 FORCE ORDER 힌트를 사용합니다.

 

select
from 주문 o, 고객 c, 결제방식 t
where o.주문일자 >= trunc(sysdate)
and c.고객번호 = o.고객번호
and t.결제방식코드 = o.결제방식코드
option (force order, loop join)

 

다음과 같은 방식으로도 가능합니다.

 

SELECT
FROM ORDER O INNER LOOP JOIN CUSTOMER C ON(C.CUSTNO = O.CUSTNO)
WHERE O.ORDER DT >= TRUNC(SYSDATE
AND C.CUSTNO = O.CUSTNO
OPTION(FORCE ORDER)

 

NL 조인의 부하지점

 

아래는 EMP TABLE 과 DEPT TABLE을 NL 조인하는 쿼리와 실행계획입니다.

 

-- index
-- pk_dept : dept.deptno
-- dept_loc_idx : dept.loc
-- pk_emp : emp.empno
-- emp_deptno_idx : emp.deptno
-- emp_sal_idx : emp.sal

select /*+ordered use_nl(e)*/ e.empno, e.ename, d.dname, e.job, e.sal
from scott.emp e, scott.dept d
where d.deptno = e.deptno --- 1
and d.loc = 'SEOUL' --- 2
AND d.gb = '2' --- 3
and e.sal >= 1500 --- 4
order by sal desc

-- Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1   NESTED LOOPS
3 2     TABLE ACCESS BY INDEX ROWID DEPT
4 3       INDEX RANGE SCAN DEPT_LOC_IDX
5 2     TABLE ACCESS BY INDEX ROWID EMP
6 5       INDEX RANGE SCAN EMP_DEPTNO_IDX

 

NL 조인 시, 조건절의 비교 순서는 2 - 3 - 1 - 4 번 순입니다. 실행계획을 해석하면 다음과 같습니다.

1. LOC = 'SEOUL' 조건을 만족하는 레코드를 찾기위해 DEPT_LOC_IDX 인덱스 범위 스캔 합니다.
2. DEPT ROWID로 테이블 엑세스 DEPT.GB = 2 필터 조건을 만족하는 레코드를 찾습니다.
3. DEPT TABLE에서 읽은 DEPTNO 값으로 조인 조건을 만족하는 EMP TALBE 쪽 레코드를 찾기 위해 EMP_DEPT_IDX 인덱스 범위스캔
4. INDEX ROWID 로 EMP 테이블 엑세스 해 SAL > 1500 필터 조건을 만족하는 레코드를 찾습니다.
5. SAL 칼럼을 기준으로 내림차순 정렬 후 결과를 리턴합니다.

 

첫 번째 부하지점은 DEPT_LOC_IDX로 인덱스를 스캔할 때 입니다. 스캔 결과에 따라 전체 일량이 좌우됩니다. 단일 컬럼 인덱스를 = 조건으로 스캔했으므로 인덱스 스캔 비효율 없이 6건을 읽었고 그 만큼 테이블 랜덤 엑세스가 발생합니다. 만약 DEPT TABLE로 많은 양의 랜덤 엑세스가 발생했는데 GB=2 테이블 필터 조건에 의해 필터되는 비율이 높다면 인덱스 추가를 고려해볼 수 있습니다.

 

두 번째 부하지점은 OUTER TABLE에서 조건에 만족하는 레코드를 INNER TABLE와 조인할 때 입니다. OUTER TABLE인 DEPT를 읽고나서 조인 엑세스가 얼마나 발생하느냐에 따라 EMP TABLE의 인덱스의 스캔양이 결정됩니다. 만약 EMP_DEPTNO_IDX의 높이가 3이면 조인 엑세스 마다 3건의 블록 I/O가 발생하고 리프 블록을 스캔하면서 추가적인 I/O가 발생합니다.

 

세 번째 부하지점은 EMP_DEPTNO_IDX 를 스캔하고나서 EMP 테이블에 랜덤 엑세스 하는부분입니다. 테이블 필터조건인 SAL 에 의해 필터링 되는 비율이 높다면 SAL 칼럼을 인덱스에 추가하는 방법을 고려해야합니다.

 

OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려해야 합니다. NL 조인 메커니즘에 따라 각 단계의 수행 일량을 분석해 랜덤 엑세스가 과도학 발생하는 지점을 파악합니다. 조인 순서 변경을 통해 랜덤 엑세스 발생량을 줄이거나, 그렇지 못할 때는 인덱스 칼럼의 구성을 변경하거나 다른 인덱스 사용을 고려합니다. 여러가지 방안을 검토한 결과 NL 조인이 효과적이지 못하다고 판단되면 해시 조인이나 소트머지 조인을 검토합니다.

 

NL 조인확장메커니즘

 

PREFETCH 와 BATCH I/O 두 기능은 읽는 블록마다 건건이 I/O CALL을 발생시키는 비효율을 줄이기 위해 고안됐습니다.

 

PREFETCH

 

PREFETCH는 인덱스를 이용해 테이블을 엑세스 하다가 디스크 I/O가 필요하면, 이어서 곧 읽게 될 블록까지 미리 읽어 버퍼캐시에 적재하는 기능입니다. nlj_prefetch, no_nlj_prefetch 힌트를 통해 이 기능을 제어할 수 있습니다.

 

아래는 PREFETCH가 발생하는 실행계획입니다. inner table의 table access가 nested loop 위쪽에 위치합니다.

 

TABLE ACCESS BY INDEX ROWID OF CUS
  NESTED LOOP
    TABLE ACCESS BY INDEX ROWID OF EMP
      INDEX RANGE SCAN OF EMP_X1
    INDEX RANGE SCAN OF CUS_X1

 

 

BATCH I/O

 

배치 I/O는 디스크 I/O CALL을 미뤘다가 읽을 블록이 일정량 쌓이면 한 번에 처리하는 방식입니다. nlj_batching, no_nlj_batching 힌트로 제어할 수 있습니다. 아래는 BATCH I/O가 발생했을때 실행계획입니다.

 

INNER TABLE ACCESS가 INNER 쪽 인덱스 스캔 아래 쪽에 위치합니다.

 

NESTED LOOPS
  NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID OF EMP
      INDEX RANGE SCAN OF EMP_X1
    INDEX RANGE SCAN OF CUS_X1
  TABLE ACCESS BY INDEX ROWID OF CUS

 

오라클 11g 이후 세 가지 실행계획 모두 나타나는데 INNER 테이블 블록을 모두 버퍼캐시에서 읽었다면 성능의 차이는 없습니다. 다만 일부를 디스크에서 읽게되면 성능차이가 발생하고, 특히 배치 I/O 로 처리될 경우 결과 집합의 정렬 순서도 다를 수 있습니다.