1. Nested Loops 조인

1.기본 메커니즘

2. 힌트를 이용해 NL조인을 제어하는 방법

3. NL조인 수행과정 분석

C,JAVA
{code} for(i=0; i<100; i++){ -- outer loop for(j=0; j<100; j++){ -- inner loop ..... } } {code}
PL/SQL
{code} for outer in 1..100 loop for inner in 1..100 loop dbms_output.put_line(outer	':'	inner); end loop; end loop; {code}

PL/SQL
{code} begin for outer in (select deptno, empno, rpad(ename, 10) ename from emp) loop -- outer 루프 for inner in (select dname from dept where deptno = outer.deptno) loop -- inner 루프 dbms_output.put_line(outer.empno	' : '	outer.ename	' : '	inner.dname); end loop; end loop; end; {code}
SQL
{code} select /+ ordered use_nl(d) / e.empno, e.ename, d.dname from emp e, dept d where d.deptno = e.deptno {code}

PL/SQL문은 내부적으로 쿼리를 반복 수행하지 않는 점을 제외하고는 위 SQL문과 동일한 데이터 엑세스 및 출력순서도 같다.
소트 머지 조인과 해시 조인도 각각 Sort Area와 Hash Area에 가공해 둔 데이터를 이용하는 점만 다를 뿐 기본적인 조인 프로세싱은 다르지 않다.

/+ ordered use_nl /
{code} select /+ ordered use_nl(e) / * from dept d, emp e where e.deptno = d.deptno {code}

ordered 힌트	from절에 기술된 순서대로 조인하라고 옵티마이저에 지시할때 사용
use_nl 힌트	NL방식으로 조인하라는 지시
ordered와 use_nl(e)힌트를 같이 사용	dept테이블(Driving or Outer Table)을 기준으로 emp 테이블(Inner Table)과 조인할때 NL방식으로 조인하라는 뜻

Nested Loops Join
{code}	Id	Operation	Name

0	SELECT STATEMENT
1	NESTED LOOPS
2	TABLE ACCESS FULL	DEPT	=> Outer/Driving	3	TABLE ACCESS FULL	EMP	=> Inner/Driven --- {code}

Sort Merge Join
{code} -	Id	Operation	Name

-

Hash Join
{code}	Id	Operation	Name

0	SELECT STATEMENT
1	HASH JOIN
2	TABLE ACCESS FULL	DEPT	=> Outer/Build Input	3	TABLE ACCESS FULL	EMP	=> Inner/Probe Input {code}

	NL 조인	소트머지 조인	해시 조인
실행계획상 위쪽	Outer(Driving)테이블	Outer(First)테이블	Build Input
실행계획상 아래쪽	Inner(Driven)테이블	Inner(Second)테이블	Probe Input

Outer와 Inner라는 용어가 헷갈리지만 이는 가장 오래된 NL조인의 틀에 맞추다 보니 현재와 같이 사용되고 있다고 이해하면 된다.
참고로 해시 조인에서도 Build Input을 Driving Table이라고 표현하기도 하지만 소트머지 조인에서는 그런 표현을 사용하지 않는다.

위에서는 두개 테이블을 조인하고 있지만 세개 이상을 조인할 때는 힌트를 아래처럼 사용하는 것이 올바른 방법이다.

{code}
select /*+ ordered use_nl(B) use_nl© use_hash(D) */ *
from A, B, C, D
where ...
{code}

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

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

Oracle 9i까지는 leading힌트에 인자를 하나만 입력할 수 있었다.
10g부터는 leading 힌트에 2개이상 테이블을 기술할 수 있도록 기능이 개선되었다.

{code}
select /*+ use_nl(A,B,C,D)*/ *
from A, B, C, D
where ...
{code}

위는 ordered나 leading힌트를 기술하지 않았으므로 4개 테이블을 NL 방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡기는 것이다.

SELECT STATEMENT

MERGE JOIN

TABLE ACCESS FULL

=> Outer/First

TABLE ACCESS FULL

=> Inner/Second
-
{code}

SQL	INDEX
{code} select /+ ordered use_nl(e) / e.empno, e.ename, d.dname, e.job, e.sal from dept d, emp e where e.deptno = d.deptno ...........(1) and d.loc = 'SEOUL' ...........(2) and d.gb = '2' ...........(3) and e.sal >= 1500 ...........(4) order by sal desc {code}	{code} pk_dept : dept.deptno dept_loc_idx : dept.loc pk_emp : emp.empno emp_deptno_idx : emp.deptno emp_sal_idx : emp.sal{code}

{code}

--

Id

Operation

Name

--

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

--

|

위 실행 계획을 그림으로써 표현해 보면 그림 2-1과 같다.(P.215)
!p_2-1.jpg!

실행계획 해석은, 위에서 아래로, 안쪽에서 바깥으로 읽는다.

{color:red}{*}여기서 기억할 것!!!*{color}
각 단계를 완료하고 나서 다음 단계로 넘어가는 것이 아니라 한 레코드씩 순차적으로 진행한다는 사실.
단 order by는 전체 집합을 대상으로 정렬해야 하므로 작업을 모두 완료한 후 다음 오퍼레이션을 진행


NL조인의 수행절차(P.216)
!p_2-2.jpg!


* dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우
* gb='2'조건에 의해 필터링 되는 비율이 높다면 dept_loc_idx인덱스에 gb컬럼을 추가하는 방안을 고려
* sal >= 1500 조건에 의해 필터링 되는 비율이 높다면 emp_deptno_idx인덱스에 sal 컬럼 추가하는 방안고려

{color:red}
*OLTP시스템에서 조인을 튜닝할 때는 우선적으로 NL조인부터 고려*
{color}

h4. 4. NL조인의 특징

* Random 액세스 위주의 조인방식
 >> 따라서  인덱스 구성이 완벽해도 대량의 데이터 조인시 비효율적
* 조인을 한 레코드씩 순차적으로 진행
 >> 아무리 대용량 집합이더라도 매우 극적인 응답속도를 낼 수 있음(부분범위처리가 가능한 상황에서)
 >> 순차적으로 진행되는 특징 때문에 먼저 엑세스 되는 테이블의 처리 범위에 의해 전체 일량이 결정됨
* 다른 조인 방식과 비교했을 때 인덱스 구성 전략이 특히 중요
 >> 인덱스의 유무 및 컬럼구성에 의해 효율이 크게 달라짐
{color:red}
-  소량의 데이터를  주로 처리
-  부분범위 처리가 가능한 OLTP성 환경에 적합
{color}

h4. 5. NL조인 튜닝실습

교재참고

h4. 6. 테이블 Prefetch

참고 http://wiki.gurubee.net/display/DBSTUDY/Prefetch

테이블 Prefetch를 제어하는 파라메터 중 하나인 _table_lookup_prefetch_size를 0으로 설정하면 NL 조인 실행계획으로 되돌아 간다. (P.222 하단 Execution Plan 참조)
새로운 포맷의 실행계획이 나타난다고 항상 테이블 Prefetch가 작동하는 것은 아니다. (기능이 활성화됨)
Prefetch 기능이 실제 작동할 때면 db file sequential read 대신 db file parallel reads 대기 이벤트가 나타남

NL조인에서 항상 새포맷의 실행계획이 나타나는 것은 아니다. 
* Inner쪽 Non-Unique 인텍스를 Range Scan할때는 테이블 Prefetch 실행계획이 항상나타남
* Inner쪽 Unique 인덱스를 Non-Unique 조건으로 Range Scan할때도 항상 나타남
* Inner쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 때도 실행계획이 나타날 수 있음
   이때 인덱스는 Range Scan으로 엑세스 한다. 테이블 Prefetch 실행계획이 안나타날 때는 Unique Scan 으로 엑세스 한다.


h4. 7. 배치 I/O

* 오라클 11g에서 시작
*  Inner 쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘
* 테이블 엑세스를 나중에 하지만 부분범위처리는 정상적으로 작동.
* 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리
* 해당 매커니즘 유도를 위해서는 nlj_batching 힌트를 사용하면 됨
* 배치 I/O가 작동하면 데이터 정렬 순서가 달라질 수 있음.

p.225 Execution Plan 설명
1. 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner쪽 인덱스와 조인하며 중간 결과집합(sub-resultset)을 만듬
2. 중간결과집합이 일정량 쌓이면 inner쪽 테이블 레코드를 액세스,
. 테이블 블록이 버퍼 캐시에 있으면 바로 최종 결과집합에 담고, 못찾으면 중간집합에 남겨둠
3. 2번 과정에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크에서 읽음.(Multiple Single Block I/O)
4 버퍼캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음
5 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4를 반복


h4. 8. 버퍼 Pinning 효과

|Oracle ver.|버퍼 Pinning 효과|
|8i|* 테이블 블록에 대한 버퍼 Pinning기능이 작동
* 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 다른 레코드를 읽기위해 Outer쪽으로 돌아오는 순간 Pin을 해제 |
|9i|* 9i부터 Inner쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL조건 위쪽으로 올라가므로 이때는 항상 버퍼 Pinning효과가 나타나는 셈|
|10g|* 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 Outer를 돌아오더라도 테이블 블록에 대한 Pinning상태을 유지|
|11g|* User Rowid로 테이블 액세스할 때도 버퍼 Pinning효과가 나타남
* NL조인에서 inner쪽 루트 아래 인덱스 블록들도 Pinning하기 시작|

h2. 문서에 대하여

* 최초작성자 : [꼬챙이]
* 최초작성일 : 2010년 08월 20일
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}