NL JOIN

NL 조인 특징 요약

1. 랜덤 액세스 위주의 조인 방식

    *랜덤 액세스는 레코드 하나를 읽기 위해 블록을 통째로 읽는다.

2. 조인을 한 레코드씩 순차적으로 진행 → 부분범위 처리가 가능, Driving 테이블 처리 범위에 따라 전체 일량 결정

3. 인덱스 구성 전략이 중요

 

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

 

기본 메커니즘

NL JOIN = Nested Loop Join 

SELECT E.사원명, C.고객명, C.전화번호
  FROM 사원 E, 고객 C
 WHERE E.입사일자 >= '19960101'
   AND C.관리사원번호 = E.사원번호

 

사원 : Outer Table, Driving Table

고객 : Inner Table

 

NL Join 은 Nested Loop Join 으로, 중첩 루프문과 같은 수행 구조를 사용한다.

일반적으로 NL Join 은 Outer 와 Inner 양쪽 모두 인덱스를 이용한다. 만약 Outer 쪽 테이블의 사이즈가 크지 않다면 인덱스를 이용하지 않을 수 있다. 반면, Inner 쪽은 인덱스를 사용해야 한다. 그렇지 않으면 Outer 루프에서 읽은 건수만큼 Inner 테이블을 Table full scan 반복해야 하기 때문이다.

즉, NL Join 은 '2중 for 문 구조로 수행하며, 인덱스를 이용한 조인 방식' 이라고 할 수 있다.

 

NL 조인 실행계획 제어

 

nl 조인 명령 힌트 : use_nl

조인 순서 제어 힌트 : ordered, leading 

 

사용 예 1)

ordered 힌트를 사용하여 FROM 절에 기술한 순서대로 조인하도록 하고,

조인 방법으로 A 와 B 는 NL 조인, 이어서 C와 NL 조인, 이어서 D와는 해시조인하라.

SELECT /*+ ordered use_nl(B) use_nl(C) use_hash(D) */
  FROM A, B, C, D
 WHERE ...

사용 예 2) 

leading 힌트를 사용해서 FROM 절에 기술된 순서가 아니라 사용자가 지정한 순서로 조인하도록 하고,

조인 방법으로 C와 A는 NL 조인, 이어서 D와 NL 조인, 이어서 B와 해시조인하라.

SELECT /*+ leading(C,A,D,B) use_nl(A) use_nl(D) use_hash(B) */
  FROM A, B, C, D
 WHERE ...

사용 예 3)

4개의 테이블을 NL 방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡김

SELECT /*+ use_nl(A,B,C,D) */
  FROM A, B, C, D
 WHERE ...

 

NL 조인 수행 과정 분석

/*
=========인덱스 구성=========
    사원 테이블
    	- 사원_PK : 사원번호
    	- 사원_X1 : 입사일자
    고객 테이블
    	- 고객_PK : 고객번호
        - 고객_X1 : 관리사원번호
        - 고객_X2 : 최종주문금액
*/

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	------- ④
   ;

조건절 비교 순서와 사용한 인덱스는?

 

힌트에 의해서 사원 테이블이 Outer 테이블이다.

② : E.입사일자 >= '1996010' 조건을 만족하는 레코드를 찾으려고 사원_X1 인덱스를 Range 스캔한다.

③ : 사원_X1 인덱스에서 읽은 ROWID 로 사원 테이블을 액세스(랜덤 액세스)해서 E.부서코드 = 'Z123' 필터 조건을 만족하는지 확인한다.

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

④ : 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스(랜덤 액세스)해서 최종주문금액 >= 20000 필터 조건을 만족하는지 확인한다.

 

위 과정을 반복한다. 인덱스 Range 스캔할 때 스캔할 데이터가 더 있는지 확인하는 데 이를 one-plus 스캔이라고 부른다.

여기서 중요한 것은, 각 단계를 모두 완료하고 다음 단계로 넘어가는게 아니라 한 레코드씩 순차적으로 진행한다는 사실이다.

 

NL 조인 튜닝 포인트

1. 랜덤 액세스를 줄일 수 있는가? 

- 조인 순서 변경, 인덱스 컬럼 추가/구성 변경

2. 더 효과적인 인덱스가 있는가?

- 인덱스 신규 생성

 

조인 액세스 횟수가 많을수록 성능이 느려진다.

조인 액세스 횟수는 Outer 테이블의 결과 건수에 의해 결정된다.

outer 테이블 결과 건수 조회시 랜덤 액세스 양이 많을수록 성능이 느려진다.

인덱스에 컬럼을 추가해서 인덱스 스캔만으로 결과 건수가 정해지도록 하는 것은?

 

올바른 조인 메소드 선택 방법

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

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

 

NL 조인 특징 요약

1. 랜덤 액세스 위주의 조인 방식

    *랜덤 액세스는 레코드 하나를 읽기 위해 블록을 통째로 읽는다.

2. 조인을 한 레코드씩 순차적으로 진행 → 부분범위 처리가 가능, Driving 테이블 처리 범위에 따라 전체 일량 결정

3. 인덱스 구성 전략이 중요

 

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

 

NL 조인 확장 메커니즘

 

테이블 Prefetch

인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능

오라클 9i 부터 적용 

 

배치 I/O 기능

디스크 I/O CALL 을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능

오라클 11g 부터 적용, inner 테이블에 작동시 결과집합 정렬 보장 안함

 

기본 nl join 예제)

SELECT /*+ ordered use_nl(D) */ E.EMPNO, E.ENAME, E.SAL, D.DNAME
FROM EMP E
	INNER JOIN DEPT D
	ON D.DEPTNO = E.DEPTNO
WHERE E.SAL > 1500
;

Execution plan
------------------------------------------------------------------------------
| Id | Operation                      | Name     | Card  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0| SELECT STATEMENT               |          |    12 |   288 |   16    (0)|
|   1|  NESTED LOOPS                  |          |    12 |   288 |   16    (0)|
|   2|   NESTED LOOPS                 |          |    12 |   288 |   16    (0)|
|*  3|    TABLE ACCESS FULL           | EMP      |    12 |   204 |    4    (0)|
|*  4|    INDEX UNIQUE SCAN           | DEPT_PK  |     1 |       |    0    (0)|
|   5|   TABLE ACCESS BY INDEX ROWID  | DEPT     |     1 |    13 |    1    (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------------------------
   3 - filter("E"."SAL">1500)
   4 - access("D"."DEPTNO"="E"."DEPTNO")
------------------------------------------------------------------------------

 

Prefetch 예제)

nlj_prefetch 힌트 사용

inner 테이블에 대한 디스크 I/O 과정에 테이블 Prefetch 기능이 작동함

SELECT /*+ ordered use_nl(D) nlj_prefetch */ E.EMPNO, E.ENAME, E.SAL, D.DNAME
FROM EMP E
	INNER JOIN DEPT D
	ON D.DEPTNO = E.DEPTNO
WHERE E.SAL > 1500
;

Execution plan
------------------------------------------------------------------------------
| Id | Operation                      | Name     | Card  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0| SELECT STATEMENT               |          |    12 |   288 |   16    (0)|
|   1|  NESTED LOOPS                  |          |    12 |   288 |   16    (0)|
|   2|   NESTED LOOPS                 |          |    12 |   288 |   16    (0)|
|*  3|    TABLE ACCESS FULL           | EMP      |    12 |   204 |    4    (0)|
|*  4|    INDEX UNIQUE SCAN           | DEPT_PK  |     1 |       |    0    (0)|
|   5|   TABLE ACCESS BY INDEX ROWID  | DEPT     |     1 |    13 |    1    (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------------------------
   3 - filter("E"."SAL">1500)
   4 - access("D"."DEPTNO"="E"."DEPTNO")
------------------------------------------------------------------------------

 

배치 I/O 예제)

nlj_batching 힌트 사용

inner 테이블에 대한 디스크 I/O 과정에 테이블 Prefetch 기능이 작동함

SELECT /*+ ordered use_nl(D) nlj_batching */ E.EMPNO, E.ENAME, E.SAL, D.DNAME
FROM EMP E
	INNER JOIN DEPT D
	ON D.DEPTNO = E.DEPTNO
WHERE E.SAL > 1500
;

Execution plan
------------------------------------------------------------------------------
| Id | Operation                      | Name     | Card  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0| SELECT STATEMENT               |          |    12 |   288 |   16    (0)|
|   1|  NESTED LOOPS                  |          |    12 |   288 |   16    (0)|
|   2|   NESTED LOOPS                 |          |    12 |   288 |   16    (0)|
|*  3|    TABLE ACCESS FULL           | EMP      |    12 |   204 |    4    (0)|
|*  4|    INDEX UNIQUE SCAN           | DEPT_PK  |     1 |       |    0    (0)|
|   5|   TABLE ACCESS BY INDEX ROWID  | DEPT     |     1 |    13 |    1    (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------------------------
   3 - filter("E"."SAL">1500)
   4 - access("D"."DEPTNO"="E"."DEPTNO")
------------------------------------------------------------------------------