NL (Nested Loops) 조인

기본 매커니즘

select  t1.cola,
        t2.colb
from    table_1 t1,table_2 t2
where   t1.colx = {value}
and     t2.id1 = t1.id1
;

* 위의 Query를 For문으로 작성하면 다음과 같다.
for r1 in (select rows from table_1 where colx = {value}) loop
    for r2 in (select rows from table_2 that match current row from table_1) loop
          output values from current row of table_1 and current row of table_2
    end loop
end loop

Outer Loop가 tab_1을 처리하고 Inner Loop가 table_2를 처리한다.
이 pseudo 코드의 구조 때문에 NL 조인에 사용되는 두 테이블은 대개 '아우터(outer) 테이블' 과 '이너(inner) 테이블' 이라고 불린다.
그리고 Outer Table을 종종 '선행(Driving) 테이블' 이라고도 한다.

• 9i 이상에서 인덱스를 이용해 Inner Table을 액세스하는NL조인에 대한 실행계획을 보면 두 개의 다른 형태가 나타날 수 있다.
  하나는 옵티마이저가 Inner Table에 속한 인덱스를 사용할 때 unique scan을 하는 경우이고, 다른 하나는 range scan을 사용하는 경우이다.
  Outer Table이 단일 로우를 리턴한다는 보장이 있을 경우에 두번째 형태와 같은 실행계획은 나타나지않는다.

Execution Plan (9.2.0.6 autotrace - unique access on inner table (traditional))
-------------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=324 Card=320 Bytes=11840)
1 0   NESTED LOOPS (Cost=324 Card=320 Bytes=11840)
2 1    TABLE ACCESS (FULL) OF 'DRIVER' (Cost=3 Card=320 Bytes=2560)
3 1    TABLE ACCESS (BY INDEX ROWID) OF 'TARGET' (Cost=2 Card=1 Bytes=29)
4 3     INDEX (UNIQUE SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=1)

Execution Plan (9.2.0.6 autotrace - range scan on inner table (new option))
---------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=322 Card=319 Bytes=11803)
1 0  TABLE ACCESS (BY INDEX ROWID) OF 'TARGET' (Cost=2 Card=1 Bytes=29)
2 1   NESTED LOOPS (Cost=322 Card=319 Bytes=11803)
3 2    TABLE ACCESS (FULL) OF 'DRIVER' (Cost=3 Card=319 Bytes=2552)
4 2    INDEX (RANGE SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=1)

• 두 개의 실행 계획은 모두 같은 쿼리에서 나온 것이다. 두 번째 Table에 대한 액세스는 Primary key 인덱스를 이용해 단일 로우를 찾는 것이므로 워칙적으로는Unique Scan 이어야 한다.
• 위의 예제를 통해 보는 실행 계획은 Driving Table에서 처리된 로우 수에 따라 다른 메커니즘을 보이고 있다. Table이 "320" 로우 일때는 전통적인 방식으로 unique scan하는 실행계획을 사용하고, "319"일때는 Optimizer가 range scan방식으로 전환함으로써 새로운 매커니즘을 사용하였다.

• NL 조인의 두 번째 형태는 9i에서 생긴 것인데, 바로 "table prefetching"이라고 알려진 메커니즘을 표한하는 것인데, 이를 통해 많은 NL조인 시 논리적인 I/O 횟수를 줄일 수 있다.(이는 궁극적으로 래치 요청횟수를 줄이고 대개 물리적 I/O횟수까지 줄이는 효과를 가져오게 된다.)

Nested Loop Join 그림

                                                                                                그림1. Nested Loop Join

• 진행방향을 나타내는 화살표를 이용해 Driving Table 로우들과 다른 테이블 로우들과의 연관성을 보여 주고 있다.

                                                                     그림2. 인덱스를 경유한 Nested Loop 조인

• Inner Table을 찾아가려고 중간에 인덱스를 경유하는 모습을 표현하고 있는데, NL 조인 시 대부분 이런 방식으로 인덱스를 이용한다.

NL조인의 전통적인 방식

• Outer Table에서 첫 번째 로우를 찾고, 인덱스를 거쳐서 Innter Table로 부터 매칭되는 각 로우들을 건건히 방문하는 매커니즘
• 다시 Outer Tble에 있는 두 번째, 세 번째 로우 순으로 진행하면서 Inner Table에 대해 같은 처리를 반복 한다.
• 따라서 두 번째 테이블로부터 가져오는 결과는 (a,a,a,b,b,b,c,c,c,)순으로 정렬된다.
• 이 때문에 조인 이후에 나타나는 order by 절이 SORT(ORDER BY) NO SORT 방식으로 처리 될 수 있다.

NL조인의 새로운 방식

• Outer Table로 부터 첫 번째 로우를 찾고, 인덱스르르 탐침해서 Leaf Block에 도달하면 Inner Table에 액세스하기 위해 필요한 rowid 정보만을 읽고 거기서 멈춘다.
• 그리고 나서 아우터 테이블에 있는 두 번째, 세 번째 로우들을 위해 같은 작업을 반복한다.
• Target rowid를 모두 찾고 나면 실행엔진은 Inner Table에 대한 Access를 시도하는데, 먼저 지금까지 읽은 결과집합을 rowid순으로 정렬하고 나서 Table Access를 시도하기 때문에 테이블 길이를 따라 블록들을 단 한번씩만 방문하면 된다.
• 이 과정에서 읽히는 로우들은 순서를 무시하고 먼저 나타나는 순서대로 읽게 되므로 위 그림에서는 (a,b,b,a,a,c,b,c,a,c)순이 될 것이다.
• 논리적 I/O를 최소화 시킬수 있다. 두 개의 row를 읽으려 할 때 두번이 아닌 단 한 번의 consistent get으로 함께 읽어들일 수 있다는 것이다.
• order by 절을 처리 하고자 할 때 sort를 발생시킬 수 있다.