Nested Loop Join

1. NESTED LOOPS JOIN (NL Join)

- 옵티마이저가 Driving Table을 결정함. (Outer Table)
- Driving Table이 아닌 테이블은 Drvien Table로 지정함 (Inner Table)
- Driving Table의 각 row에 대해 이들이 추출될 때마다 Driven Table의 연관된 모든 row를 조인에 의해 엑세스

 

* NESTED LOOPS JOIN의 튜닝포인트
① 테이블 간 조인 횟수를 최소화 할 수 있도록 Driving Table을 구성 -> 조인 순서 제어
② Driven Table의 연결고리 칼럼에 대한 인덱스 구성
(연결되는 테이블은 인덱스가 있어야 효율적으로 쿼리 실행이 가능함)

* NESTED LOOPS JOIN의 특징
① 인덱스에 의한 랜덤 엑세스에 기반하고 있기 때문에 대량의 데이터 처리 시 적합하지 않음. (테이블 간의 조인 횟수 최소화를 위한 조인 순서의 최적화)
② Driving Table로는 테이블의 데이터가 적은 마스터 테이블이거나, where절 조건으로 적절하게 row를 제어할 수 있는 것이어야 함
③ Driven Table에는 조인을 위한 적절한 인덱스가 생성되어 있어야 한다.


2. NESTED LOOPS JOIN의 수행 절차

ex) joinkey_a, joinkey_b, color, size 등은 모두 인덱스임
SELECT /*+ORDERED USE_NL(a b)*/
a.color, ... , b.size, ...
FROM table_a a, table_b b
WHEREE a.joinkey_a = b.joinkey_b
AND a.color = 'RED'
AND b.size = 'MED'
;
힌트(a b) 구문을 통해 table a를 먼저 읽고(driving table),  USE_NL로 인해 NESTED LOOPS를 사용해 table b를 읽어온다. 연결고리 칼럼 joinkey_b에 인덱스가 존재하므로 효율적으로 쿼리가 실행된다,


3. NESTED LOOPS JOIN 장단점
*장점 
① 인덱스를 통한 랜덤 엑세스(random access) 기반에서 좋은 성능을 보인다.
*단점

① 인덱스가 없는 상태에선 속도가 저하됨
② 대용량 데이터를 처리할 경우 성능이 저하됨.


4. DRVING TABLE의 원리

위 그림과 같이, 여러 번의 NL Join을 진행할 경우, 데이터가 적은 테이블부터 NL Join을 진행하는 것이 효율적.

 

5. 조인 순서 제어 방법

① 힌트로 아래와 같이 순서를 제어 
/*+ORDERED */
-> FROM 절에 기술한 테이블 순서대로 제어
/*+LEADING(table명) */
-> 힌트 내에 제시한 테이블이 Driving Table으로 채택됨
(ORDERED 힌트와 같이 사용할 경우 LEADING 힌트는 무시됨)

② 뷰(view) 활용
③ suppressing 활용
④ FROM 절의 테이블 순서 변경
(RBO 에서는 각 테이블에 대한 규칙이 동일할 때, FROM 절로부터 멀리 있는 테이블부터 처리함, CBO에서는 이 방법이 의미가 없다.)

 

6. 연결고리에 대한 인덱스

① 양 쪽 모두 인덱스가 있는 경우
- 두 테이블 중 조회되는 결과가 적은 테이블을 선택하여 driving table로 선택
② 한쪽만 인덱스가 있는 경우
- 인덱스가 없는 쪽 테이블을 driving table로 사용함.
③ 양쪽 모두 연결고리에 대한 인덱스가 없는 경우
ex)  SELECT *
       FROM TAB_A A, TAB_B B
        WHERE A.NAME = B.NAME;
A -> B 이던, B -> A이던 Full table scan으로 간다.

driven 테이블에 대한 Full table scan 회수는 driving 테이블로부터 읽어 들이는 row 수만큼 된다.

이러한 상황에서 할 수 있는 조인이 Sort Merge와 Hash조인, Nested Loops 조인방식으로 조인이 이뤄지지 않는다.