1. Nested Loops 조인 (by hojin223) [2017.03.31]
01. Nested Loops 조인
정의
중첩 루프문(Nested Loop:이중 For문)의 수행 구조
{tip:title=
최적: 후행 테이블 조인 컬럼과 선행 테이블의 where 조건절 컬럼의 인덱스 존재
최악: 후행 테이블 조인 컬럼의 인덱스 미존재 (선행 테이블의 결과 건수만큼 후행 테이블이 FULL_TABLE_SCAN 이 발생됨)}
사용1
select /*+ ordered use_nl(t2) */ *
from t1, t2
where t1.c1 = t2.c1
ordered 힌트 -> from절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용
use_nl 힌트 -> NL 방식으로 조인하라고 지시할 때 사용
사용2
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와 조인할 때는 해시 방식으로 조인하라는 뜻
사용3
select /*+ leading(C, A, D, B) use_nl(A) user_nl(d) user_hash(B) */ *
from A, B, C, D
where ~
from절에 기술된 순서와 상관없이, leading절의 인자 순서대로 조인 진행
사용4
select /*+ use_nl(A, B, C, D) */ *
from A, B, C, D
where ~
ordered나 leading 힌트를 기술하지 않았으므로
4개 테이블을 NL 방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡기는 것
조인별 선후 테이블 명명
"NL 조인" "Sort Merge 조인" "Hash 조인"
선행테이블: Outer(Driving) Outer(First) Build Input
후행테이블: Inner(Driven) Inner(Second) Probe Input
NL 조인의 특징
1. Random 액세스 위주의 조인 방식
인덱스 구성이 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적임
2. 조인을 한 레코드씩 순차적으로 진행함
Random 액세스 위주의 조인으로 대용량 데이터 처리 시, 매우 치명적인 한계가 있지만
조인을 한 레코드씩 순차적으로 진행함으로, 아무리 대용량 집합이더라도 부분범위처리가 가능한 상황에서 효과적인 응답 속도를 낼 수 있음
* 인덱스를 스캔하는 양에 따라 전체 일량이 좌우됨
* 조건에 의해 필터링되는 비율이 높다면 인덱스에 컬럼을 추가하는 방안을 고려해야함
* OLTP 시스템에서 조인을 튜닝할때는 일반적으로 NL 조인부터 고려하는 것이 올바른 순서임
우선, NL 조인 메커니즘을 따라 각 단계의 수행 일량을 분석해 과도한 Random 액세스가 발생하는 지점을 파악함
조인 순서를 변경해 Random 액세스 발생량을 줄일 수 있는 경우가 있고,
그렇지 못할 때는 인덱스 컬럼 구성을 변경하거나 다른 인덱스의 사용을 고려해야함
* 순차적으로 진행하는 특징 때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정됨
* 다른 조인 방식과 비교했을 때 인덱스 구성 전략이 특히 중요함
* 조인 컬럼에 대한 인덱스가 있느나 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라짐
* NL 조인은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합한 조인 방식임
* 테이블을 액세스한 후에 필터링되는 비율이 높다면, 인덱스에 테이블 필터 조건 컬럼을 추가하는 것을 고려해 볼 필요가 있음
테이블 Prefetch
/*+ nlj_prefetch */
* Prefetch 기능이 실제 작동할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 이벤트가 나타남
* Prefetch는 디스크 I/O와 관련이 있음. 디스크 I/O를 수행하려면 비용이 많이 들기 때문에 한번 I/O Call이 필요한 시점에,
곧이어 읽을 가능성이 큰 블록들을 캐시에 미리 적재해 두는 기능. 한번의 I/O Call로써 여려 Single Block I/O를 동시에 수행함
* NL 조인에서 항상 새 포멧의 실행계획이 나타나는 것을 아님. 기본적으로 Outer 쪽 인덱스를 Unique Scan 할 때는 작동하지 않음
이 경우를 제외하면 언제든 새 포맷의 실해계획이 나타날 수 있음
- Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 실행계획이 항상 나타남
- Inner 쪽 Unique 인덱스를 Non-Unique 조건(모든 인덱스 구성컬럼이 '=' 조건이 아닐 때)으로
Range Scan할 때도 테이블 Prefetch 실행계획이 항상 나타남
- Inner 쪽 Unique 인덱스를 Unique 조건(모든 인덱스 구성컬럼이 '=' 조건 )으로
액세스할 때도 테이블 Prefetch 실행계획이 나타날 수 있음
이때 인덱스는 Range Scan으로 액세스 함. 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 액세스 함
테이블 Non-Prefetch
테이블 Prefetch
배치 I/O
/*+ nlj_batching */
/*+ no_nlj_batching */
* 오라클 11g에서 시작된 배치 I/O 메커니즘임
* Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄(batch) 처리하는 메커니즘인 것으로 추정
* 테이블 액세스를 나중에 하지만 부분범위처리는 정상적으로 작동함
따라서 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩(Fetch Call 단위) 나누어 처리함
1. 드라이빙 테이블에서 일정량을 레코드를 읽어 Inner 족 인덱스와 조인하면서 중간 결과집합(sub-resultset)을 만듬
2. 중간 결과집합이 일정량 쌓이면 Inner 쪽 테이블 레코드를 액세스 함. 이때 테이블 블록을 버퍼 캐시에서 찾으면 바로 최종 결과집합에 담고,
못 찾으면 중간 집합에 남겨 둠
3. 2번 과정에서 남겨진 중간 집합에 대한 Inner 쪽 테이블 블록을 디스크로부터 읽음. 이때 Multiple Single Block I/O 방식을 사용함
4. 버퍼 캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음
* Outer 테이블로부터 액세스되는 Inner 쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해,
테이블 Prefetch에 이어 추가로 도입된 메커니즘
* Inner 쪽 테이블 블록이 모두 버퍼 캐시에서 찾아가지 않으면(버퍼 캐시 히트율 < 100%),
실제 배치 I/O가 작동한다면 데이터 정렬 순서가 달라질 수 있음
모두 버퍼 캐시에서 찾을 때는(버퍼 캐시 히트율 = 100%) 이전 메커니즘과 똑같은 정렬 순서를 보임
* 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는
디스크 I/O가 발생하든 안 하든 데이터 정렬 순서가 항상 일정함
배치I/O
버퍼 Pinning 효과
테이블 Prefetch와 배치 I/O 기능이 도입된 시점과 맞물려 버퍼 Pinning 기능에 변화가 생겨
9i와 11g에서 나타난 NL 조인 실행계획 변화를 버퍼 Pinning 효과로 설명도 하지만 이 둘간에 직접적인 연관성은 없음
-8i에서 버퍼 Pinning 효과
테이블 블록에 대한 버퍼 Pinning 기능이 작동하기 시작
단, 하나의 버퍼 블록만 Pinning 함. 그리고 하나의 Fetch Call을 완료하는 순간 Pin을 해제함
이 기능은 NL 조인에서 Non-Uique 조건으로 Inner 쪽 테이블을 액세스할 때도 똑같이 작용함
따라서 Inner 쪽 인덱스를 통해 액세스되는 테이블 블록이 계속 같은블록을 가리키면 논리 I/O(=CR Gets)가 추라로 발생하지 않음
하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 "다른 레코드를 읽기 위해 Outer 쪽으로 돌아오는 순가 Pin을 해제"한다는 점
Inner 쪽을 한 번 액세스할 때마다 여러 개의 테이블 레코드를 읽도록 데이터를 구성해야 함. 클러스터링 팩터가 좋아야 그 효과도 확실함(9i~같음)
NL 조인에서도 하나의 Fetch Call을 완료하면 Pin을 해제함
-9i에서 버퍼 Pinning 효과
9i부터 Inner 쪽 인덱스 "루트 블록에 대한 버퍼 Pinning" 효과가 나타나기 시작함
단, 두 번째 액세스되는 순간 Pinning 함
테이블 블록 버퍼에 대한 Pinning도 8i와 똑같이 작동함
Inner 쪽을 한 번 액세스할 때마다 Non-Unique 인덱스로 여러 개 테이블 레코드를 읽을 때라야 이 기능이 효과를 발휘함
9i부터 Inner 쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL 조인 위쪽에 올라가므로(->테이블 Prefetch 포멧) 이때는 항상 버퍼 Pinning 효과가 나타나는 셈임
Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때는 Inner 쪽에서 한 건만 읽고 바로 Outer 테이블 쪽으로 돌아감
따라서 버퍼 Pinning 효과가 나타날 수 없음
그러다 보니 테이블 액세스가 NL 조인 위쪽으로 올라가는 9i에서의 실행계획 변화를 버퍼 Pinning 효과와 연관시켜 해석하는 오류를 범하기 쉬움
Inner 쪽 Unique 인덱스를 Unique 조건으로 액세스할 때도 테이블 액세스가 NL 조인 위쪽으로 올라가는 경우가 있음
-10g에서 버퍼 Pinning 효과
Inner 쪽 인덱스 루트 블록과 테이블 블록을 Pinning하는 기능이 여전히 작동하면서 새로운 기능이 추가됨
Inner 쪽 테이블을 Index Range Scan을 거쳐 NL 조인 위쪽에서 액세스할 때는,
하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 Outer 쪽으로 돌아오더라도 "테이블 블록에 대한 Pinning 상태를 유지"함
만약 Inner 쪽 테이블이 한 블록뿐일 때 10g에서의 새로운 버퍼 Pinning 기능이 작동한다면 NL 조인이 진행되는 논리적인 블록 I/O는 단 1회만 발생할 것
버퍼 Pinning 효과는 하나의 데이터베이스 Call 내에서만 유효함
-11g에서 버퍼 Pinning 효과
11g에서는 User Rowid로 테이블 액세스할 때도 버퍼 Pinning 효과가 나타남
NL 조인에서 Inner 쪽 루트 아래 인덱스 블록들도 Pinning 하기 시작함
배치 I/O 기능이 나타남과 동시에 이 기능이 추가되다 보니 11g에서의 NL 조인 실행계획 변화가 인덱스 블록 버퍼 Pinning과 관련 있다고 오해하기 쉬움
익덱스 블록 버퍼 Pinning 효과는 배치 I/O 실행계획과 상관없이 나타남
테이블 Prefetch 방식으로 액세스하더라도 인덱스 블록에 대한 버퍼 Pinning 효과는 똑같음
11g에서 실행계획 변화(->배치 I/O 포맷)가 인덱스 블록에 대한 버퍼 Pinning과 관련이 없음
테이블과 마찬가지로 인덱스 블록도 버퍼 Pinnging 효과가 나타나려면 같은 값으로 반복 액세스해야함
Prefetch vs Batch I/O
SQL_ID adtptt05p8ahs, child number 0
-------------------------------------
select /*+ gather_plan_statistics ordered use_nl_with_index(d)
nlj_prefetch(d) */ count(e.ename), count(d.dname) from t_emp e, t_dept
d where d.no = e.no and d.deptno = e.deptno
Plan hash value: 1305463140
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 14167 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 14167 |
| 2 |>>>TABLE ACCESS BY INDEX ROWID| T_DEPT | 1 | 1 | 14000 |00:00:00.02 | 14167 |-> Prefetch
| 3 | NESTED LOOPS | | 1 | 15329 | 28001 |00:00:00.01 | 167 |
| 4 | TABLE ACCESS FULL | T_EMP | 1 | 15329 | 14000 |00:00:00.01 | 95 |
|* 5 | INDEX UNIQUE SCAN | T_DEPT_PK | 14000 | 1 | 14000 |00:00:00.01 | 72 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."NO"="E"."NO" AND "D"."DEPTNO"="E"."DEPTNO")
SQL_ID 2ku0935hr7p7c, child number 0
-------------------------------------
select /*+ gather_plan_statistics ordered use_nl_with_index(d)
nlj_batching(d) */ count(e.ename), count(d.dname) from t_emp e, t_dept
d where d.no = e.no and d.deptno = e.deptno
Plan hash value: 2968996940
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 14167 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 14167 |
| 2 | NESTED LOOPS | | 1 | 15329 | 14000 |00:00:00.07 | 14167 |
| 3 | NESTED LOOPS | | 1 | 15329 | 14000 |00:00:00.01 | 167 |
| 4 | TABLE ACCESS FULL | T_EMP | 1 | 15329 | 14000 |00:00:00.01 | 95 |
|* 5 | INDEX UNIQUE SCAN | T_DEPT_PK | 14000 | 1 | 14000 |00:00:00.01 | 72 |
| 6 | >>>TABLE ACCESS BY INDEX ROWID| T_DEPT | 14000 | 1 | 14000 |00:00:00.01 | 14000 |-> Batch I/O
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."NO"="E"."NO" AND "D"."DEPTNO"="E"."DEPTNO")