NL조인
NL조인
NL(Nested Loop)의 수행구조는 중첩 루프 수행 구조와 동일하다. (for문 안의 for문)
일반적으로 NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다. Outer쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다. 반면 Inner쪽 테이블은 인덱스를 사용해야 한다.
실행계획 위쪽 테이블 기준으로 아래쪽 고객 테이블과 NL 조인을 한다.
NL조인 힌트는 /*+USE_NL(inner_table) USE_NL(inner_table2) USE_HASH(table3)*/
USE_NL(A, B, C, D)로 작성하면 네 개 테이블을 NL 방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡긴것이다. 앞에 LEADING(A, B, C, D)를 두면 순서대로 NL 방식 조인을 한다.
NL 조인 튜닝 포인트
1. OUTER 테이블로 아주 많은 양의 랜덤 액세스가 발생했고 A컬럼에 의해 필터링 되는 비율이 높았다면
OUTER 인덱스에 A컬럼을 추가하는 방안을 고려해야 한다.
2. INNER 인덱스를 탐색할때 INNER 테이블로 랜덤액세스가 많고 B컬럼에 의해 필터링 되는 비율이 높다면
INNER 인덱스에 B컬럼을 추가하는 방안을 고려한다.
조인 메서드 선택
1. OLTP 시스템에서 튜닝할 때는 일차적으로 NL 조인부터 고려한다.
2. 성능이 느리다면 NL 조인 튜닝 포인트에 따라 각 단계의수행 일량을 분석해서 과도한 랜덤 액세스가
발생하는 지점을 우선 파악한다. 조인 순서를 변경해서 랜덤 액세스 발생량을 줄일 수 있는지,
더 효과적인 다른 인덱스가 있는지 등을 검토한다.
3. 여러 방안을 검토한 결과 NL 조인으로 결코 좋은 성능을 내기 어렵다고 판단될때,
해시조인이나 머지 조인을 검토한다.
NL 조인 특징
1. 랜덤 액세스 위주의 조인 방식이다.
2. 한 레코드씩 순차적으로 진행한다. 부분범위 처리 활용에 장점이 있다.
실행계획 용어
1.CR : 논리적인 블록 요청 횟수
2.PR : 디스크에서 읽은 블록 수
3.PW : 디스크에 쓴 블록 수
CR, PR, PW를 낮추는 방향으로 인덱스 설계를 해야 한다.
테이블 Prefetch : 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서
곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다.
실행계획 NESTED LOOPS 상위에 TABLE ACCESS BY INDEX ROWID OF 테이블명이 나타난다.
nlj_prefecth, no_nlj_prefetch 힌트를 이용해 제어 가능
배치 I/O : 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다.
실행계획에서 INDEX SCAN 하위에 TABLEC ACCESS BY INDEX ROWID OF 테이블명이 나타난다.
nlj_batching, no_nlj_batching 힌트를 이용해 제어 가능
11g 부터 NL조인 결과집합이 항상 일정한 순서로 출력되기를 원한다면, 배치 I/O 기능이 작동하지
못하도록 no_nlj_batching 힌트를 추가하거나 ORDER BY 절에 정렬 기준을 명시해야 한다.
일반 NL 조인에서는 아래와 같은 실행계획이 나타남
|
1
2
3
4
5
6
7
8
9
|
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 |SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | DEPARTMENTS |
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES |
| 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
---------------------------------------------------------
|
오라클 9i~11gR1 버전에서는 NL 조인에서 아래와 같은 실행계획이 나타남(새로운 형태)
이는 해당 테이블 엑세스 단계에 Prefetch 기능이 적용되었음을 표현하기 위함임
|
1
2
3
4
5
6
7
8
9
|
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
---------------------------------------------------------
|
오라클 11gR1 이상의 버전에서는 아래와 같이 NL 조인이 두번 들어간 실행계획도 나올수도있음(새로운 형태)
배치I/O 실행계획
|
1
2
3
4
5
6
7
8
9
10
|
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
----------------------------------------------------------
|