[SQL튜닝] NESTED LOOPS JOIN

[SQL튜닝] NESTED LOOPS JOIN

---

주요내용

• NESTED LOOPS JOIN
• NESTED LOOPS JOIN의 수행 절차
• NESTED LOOPS JOIN의 장단점
• 조인 순서 제어 방법
• 연결고리에 대한 인덱스

학습목표

• NESTED LOOPS 조인에 대한 이해를 바탕으로 비효율적으로 수행되는 SQL문장을 진단할 수 있다.
• NESTED LOOPS 조인을 활용하여 SQL문장을 성능목표에 부합하도록 개선할 수 있다.
• NESTED LOOPS 조인 활용을 통해 개선된 SQL 문장이 성능목표에 부합하는지 테스트할 수 있다.

---

NESTED LOOPS JOIN

• 옵티마이저가 Driving Table을 결정함(Outer Table)
• Driving Table이 아닌 테이블은 Driven Table로 지정함 -> Inner Table이라고도 함
• Driving Table의 각 row에 대해 이들이 추출될 때마다 Driven Table의 연관된 모든 row를 조인에 의해 액세스
• 튜닝 포인트
  • 테이블 간 조인 횟수를 최소화 할 수 있도록 Driving Table을 선택 -> 조인 순서 제어
  • Driven Table의 연결고리 칼럼에 대한 인덱스 구성

---

NESTED LOOPS JOIN의 수행 절차

• 가정 : joinkey_a, joinkey_b, color, size 등은 모두 인덱스임

  SELECT a.color, ..., b.size, ...
  FROM table_a a, table_b b
  WHERE a.joinkey_a = b.joinkey_b
  AND a.color = 'RED'
  AND b.size = 'MED';

---

NESTED LOOPS JOIN의 장단점

• 인덱스에 의한 랜덤 액세스에 기반하고 있기 때문에 대량의 데이터 처리 시 적합하지 않음
• Driving Table로는 테이블의 데이터가 적은 마스터 테이블이거나, where절 조건으로 적절하게 row를 제어할 수 있는 것이어야 함
• Driven Table에는 조인을 위한 적절한 인덱스가 생성되어 있어야 함

---

조인 순서 제어 방법

• 조인 순서 제어를 위한 힌트 사용 -> ORDERED 힌트와 같이 사용할 경우 LEADING 힌트는 무시됨

  /*+ ORDERED*/

  • FROM절에 기술한 테이블 순서대로 제어

    /*+ LEADING(table명)*/

  • 힌트 내에 제시한 테이블이 드라이빙으로 채택됨
• 뷰(view) 활용
• 서프레싱(suppressing) 활용
• FROM 절의 테이블 순서 변경
  • RBO 하에서 각 테이블에 대한 규칙이 동일할 때, FROM 절로부터 멀리 있는 테이블부터 처리함 (※CBO에서는 이 방법은 의미가 없음)

---

연결고리에 대한 인덱스

• 양쪽 모두 연결고리에 대한 인덱스가 없는 경우

  SELECT *
  FROM TAB_A A, TAB_B B
  WHERE A.NAME = B.NAME;

  • driven 테이블에 대한 FULL table scan 횟수는 driving 테이블로부터 읽어 들이는 row 수만큼이 됨 -> 이러한 상황에서 할 수 있는 조인이 Sort Merge와 Hash 조인임