01. Nested Loops 조인

(1) 기본 메커니즘

• Nested Loops조인은 아래와 같은 중첩 루프문과 동일한 원리이다.

• 아래의 PL/SQL과 SQL문은 내부적으로(Recursive하게) 쿼리를 반복 수행하지 않는점 이외에 동일한 처리를 한다.

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

• 힌트 사용예1

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

– ordered : from절에 써있는 순서대로 테이블을 조인하세요
– use_nl : NL방식으로 조인하세요.
=> 즉, dept테이블을 기준으로, emp테이블을 NL방식으로 조인하세요

SQL> explain plan for
  2  select /*+ ordered use_nl(e)*/*
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4192419542

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   798 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |   798 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | => Outer/Driving
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   148 |     2   (0)| 00:00:01 | => Inner/Driven
---------------------------------------------------------------------------

SQL> explain plan for
  2  select /*+ ordered full(d) use_merge(e)*/ *
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1407029907

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   798 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |    14 |   798 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    80 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | => Outer/First
|*  4 |   SORT JOIN         |      |    14 |   518 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 | => Inner/Second

SQL> explain plan for
  2  select /*+ ordered use_hash(e)*/*
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   798 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   798 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 | => Outer/Build Input
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 | => Inner/Probe Input
---------------------------------------------------------------------------

• 힌트사용예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와 조인할때는 Hash로..

• 힌트사용예3
  ordered대신 leading힌트를 이용하여 조인순서제어가 가능(leading을 사용하면 테이블 순서를 일일이 바꿔줄 필요없이 제어가능)

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

=>C->A->D->B 순으로 A와 조인할때, D와 조인할때는 NL로 B와 조인할때는 Hash로..

• 힌트사용예4

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

=> A,B,C,D를 조인할 때 모두 NL로.. 단, 특별히 조인순서를 지정안했으므로, 조인순서는 옵티마이저 판단에 맡김

(3) NL조인 수행과정 분석

|

1) 사용되는 인덱스는 ? dept_loc_idx, emp_deptno_idx
2) 조건비교순서는? (2) -> (3) -> (1) -> (4)
   (실행계획 해석은, 위에서 아래로, 안쪽에서 바깥으로 읽기)
!2-1.jpg!

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

- NL조인의 수행절차
그림 2-2 참조 
-- dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우된다.
-- gb='2'조건에 의해 필터링 되는 비율이 높다면 dept_loc_idx인덱스에 gb컬럼을 추가하는 방안을 고려해볼것
-- sal >= 1500 조건에 의해 필터링 되는 비율이 높다면 emp_deptno_idx인덱스에 sal 컬럼 추가하는 방안고려

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


h3. (4) NL조인의 특징
- Random 액세스 위주의 조인방식
그러므로, 인덱스 구성이 완벽해도 대량의 데이터 조인시 비효율적
- 조인을 한 레코드씩 순차적으로 진행
아무리 대용량 집합이더라도 매우 극적인 응답속도를 낼 수 있으며, 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정

{color:red}
다른 조인방식보다 인덱스 구성 전략이 특히 중요하며, 소량의 데이터를 처리하거나 부분범위 처리가 가능한 OLTP성 환경에 적합한 조인방식이다.
{color}


h3. (5) NL조인 튜닝실습

- SQL트레이스 1 (p220 상단)
테이블을 액세스 한 후에 필터링 되는 비율이 높다면 인덱스에 테이블 필터 조건 컬럼을 추가하는것을 고려
(job_max_sal_ix : max_salary + job_type)

- SQL트레이스 2 (p221 상단)
job_max_sal_ix인덱스로부터 3건을 리턴하기 위해 인덱스 블록을 1000개 읽음. 인덱스 컬럼 순서를 조정
(job_max_sal_ix : job_type + max_salary)

- SQL트레이스 3(p221 하단)
1278번 조인시도했으나 최종성공한 결과집합은 5건뿐
조인순서 jobs -> employees에서 employees -> jobs로 변경고려
조인순서를 바꾸어도 소득이 없다면 소트머지조인과 해시조인을 검토

h3. (6) 테이블 Prefetch

- 테이블 Prefetch란?
인덱스를 경유하여 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야하는데, 그때 곧이어 읽을 가능성이 큰 블록들을 미리 적재해 두는 기능
디스크 I/O에 의한 대기횟수 감소를 노림

- 실행계획에 인덱스 rowid에의한 Inner테이블 액세스가 Nested Loops 위쪽에 표시되면, Prefetch기능이 활성화 됨을 의미
- _talble_lookup_prefetch_size를 0으로 설정하면 전통적인 NL조인 실행계획으로 돌아감
- 실행계획에 위와 같이 나타났다고 항상 테이블 Prefetch가 작동하는 것은 아니다. db_file_sequential_read 대기 이벤트 대신 db_file_parallel_reads대기이벤트가 나타나면 실제작동함을 의미

- Prefetch기능이 나타나는 경우
-- Inner쪽 Non-Unique인덱스를 Range Scan할 때는 항상 나타남
-- Inner쪽 Unique인덱스를 Non-Unique 조건(모든 인덱스 구성컬럼이 '='조건이 아닐때)으로 Range Scan할 때도 항상 나타난다.
-- Inner쪽 Unique인덱스를 Unique조건(모든 인덱스 구성컬럼이 '='조건)으로 액세스할 때도 나타날 수 있다. 이대 인덱스는 Range Scan으로 액세스하며, 테이블 Prefetch실행계획이 안 타타날 때는 Unique Scan으로 엑세스한다.

- 예) p224
-- 지분보고_PK : 회사코드 + 보고서구분코드 + 최초보고일자 + 보고서id + 보고일련번호
-- cardinality힌트를 사용하여 드라이빙 집합의 카디널리티를 변경하면서 이와 같은 실행계획이 나타남을 확인할수 있다.

h3. (7) 배치 I/O
- 오라클 11g에서 시작
- Inner 쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘
- 테이블 엑세스를 나중에 하지만 부분범위처리는 정상적으로 작동한다.
- 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리

- 배치 I/O방식
-- 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner쪽 인덱스와 조인하며 중간 결과집합을 만듬
-- 중간결과집합이 일정량 쌓이면 inner쪽 테이블 레코드를 액세스. 테이블 블록이 버퍼 캐시에 있으면 바로 최종 결과집합에 담고, ?찾으면 중간집합에 남겨둠
-- 위에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크에서 읽음.
-- 버퍼캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담음
-- 모든 레코드를 처리하거나 사용자가 Fetch Call을 중단할 때까지 1~4를 반복

- nlj_batching 힌트를 사용하면 됨
- 이방식을 사용하면 데이터 정렬 순서가 달라질 수 있음

h3. (8) 버퍼 Pinning 효과
+8i에서 나타난 버퍼 Pinning효과+

- 페이블 블록에 대한 버퍼 Pinning기능이 작동
- 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 다른 레코드를 읽기위해 Outer쪽으로 돌아오는 순간 Pin을 해제 

+9i에서 나타난 버퍼 Pinning효과+
- Inner쪽 인덱스 루트 블록에 대한 버퍼 Pinning효과가 나타나기시작
- 9i부터 Inner쪽이 Non-Unique 인덱스일 때는 테이블 액세스가 항상 NL조건 위쪽으로 올라가므로 이때는 항상 버퍼 Pinning효과가 나타나는 셈

+10g에서 나타난 버퍼 Pinning효과+

select /*+ ordered use_nl(d) */ count(e.ename), count(d.dname)
from scott.t_emp e, scott.dept d
where d.deptno = e.deptno

call count cpu elapsed disk query current rows

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation

- Inner쪽 테이블을 Index Range Scan을 거쳐 NL조인 위쪽에서 액세스 할 때는 , 하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 Outer를 돌아오더라도 테이블 블록에 대한 Pinning상태을 유지

+11g에서 나타난 버퍼 Pinning효과+
- User Rowid로 테이블 액세스할 때도 버퍼 Pinning효과가 나타남
- NL조인에서 inner쪽 루트 아래 인덱스 블록들도 Pinning하기 시작