[튜닝] 중첩 루프 조인 (USE_NL)

중첩 루프 조인의 구조

중첩 루프 조인 수행의 가장 큰 특징은 아래와 같다.

● DRIVING 테이블

    - 먼저 엑세스 하는 테이블

    - 조건을 만족하는 데이터에 대해 한 번만 엑세스 하며 조인 조건을 상수로 제공받지 못함

● INNER 테이블

    - 뒤에 엑세스하는 테이블

    - DRIVING 테이블에 추출되는 데이터 건수만큼 반복 수행하며 조인 조건을 상수로 제공 받음

---

SELECT A.COL1, A.COL2, B.COL2, B.COL3
  FROM TABLE_A A, TABLE_B B
 WHERE A.COL1 = B.COL1
   AND A.COL1 = '1234567890'
   AND B.COL2 > TRUNC(SYSDATE - 10)

중첩 루프 조인의 성능은 아래와 같은 조건에 의해 결정

1. INNER 테이블의 효과적인 엑세스
2. DRIVING 테이블에서 추출되는 데이터 건수
3. DRIVING 테이블의 효과적인 엑세스

① INNER 테이블의 효과적인 엑세스

  - 중첩 루프 조인의 특징에서 INNER 테이블은 DRIVING 테이블에서 추출되는 데이터 건수만큼 반복 엑세스를 하기 때문에 INNER 테이블의 효과적인 엑세스가 제일 중요하다.

  - INNER 테이블의 반복 엑세스를 최적화하기 위해서는 최적의 인덱스 구성이 필요하다.

  - 위 예제의 경우에서는 B.COL1이 A 테이블로부터 상수값을 제공받기 때문에 구성할 수 인덱스는 B.COL1 + B.COL2 혹은 B.COL2 + B.COL1으로 구성할 수 있다. 다만 B.COL1은 점 조건, B.COL2는 선분 조건이므로 최적의 인덱스 구성은 B.COL1 + B.COL2로 구성해야 한다.

② DRIVING 테이블에서 추출되는 데이터 건수

  - DRIVING 테이블에서 추출되는 건수만큼 INNER 테이블과 조인을 수행하기 때문에 DRIVING 테이블에서 추출되는 데이터 건수가 적을수록 성능을 보장한다.

③ DRIVING 테이블의 효과적인 엑세스

  - DRIVING 테이블에 빠른 엑세스를 위해 인덱스 구성이 필요하다.

  - TABLE A의 경우 A.COL1이 점 조건이므로 A.COL1에 인덱스를 구성해야 한다.

 

중첩 루프 조인의 경우 처리 범위가 가장 적은 테이블을 DRIVING 테이블로 지정해서 가장 먼저 엑세스되도록 하는 것이 중요하다. 그 다음에 INNER 테이블의 반복 엑세스를 최적하하기 위해 인덱스를 선정해야 하며, 마지막으로 DRIVING 테이블에 엑세스하기 위한 인덱스를 선정해야 한다.

---

SELECT /*+ ORDERED USE_NL(A, B) */
       A.COL1, A.COL2, B.COL2, B.COL3
  FROM TABLE_A A, TABLE_B B
 WHERE A.COL1 = B.COL1
   AND A.COL1 = '1234567890'
   AND B.COL2 > TRUNC(SYSDATE - 10)

중첩 루프 조인의 실행 계획은 위와 같은 방법으로 제어한다.

• ORDERED : FROM 절의 순서대로 테이블에 엑세스 수행
• USE_NL(A, B) : A 테이블과 B 테이블을 중첩 루프 조인 방식으로 조인을 수행
  • A : DRIVING 테이블
  • B : INNER 테이블

#실행 계획 샘플
SELECT STATEMENT
  NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID OF 'TABLE_A'
      INDEX RANGE SCAN OF 'A.COL1_IDX'
    TABLE ACCESS BY INDEX ROWID OF 'TABLE_B'
      INDEX RANGE SCAN OF 'B.COL2_IDX'

 

[참고] 실행 계획으로 배우는 고성능 데이터베이스 튜닝