SQL튜닝 입문 - NESTED LOOPS/SORT/MERGE/HASH

1. NESTED LOOPS JOIN

- Driving Table: 옵티마이저가 결정 / outer table

- Driven Table:Inner Table

 

- 튜닝 포인트

  1) 테이블 간 조인 횟수를 최소화 할 수 있도록 Driving Table을 선택 및 제어

  2) Driven Table의 연결고리 칼럼에 대한 인덱스 구성

2. NESTED LOOPS JOIN 장/단점

1. 인덱스에 의한 랜덤 액세스에 기반하고 있어 대량의 데이터 처리 시 적합하지 않음

2. Driving Table로는 테이블의 데이터가 적은 마스터 테이블이거나 where 절 조건으로 적절하게 row를 제어할 수 있어야 함.

3. Driven Table에는 조인을 위한 적절한 인덱스가 있어야 함

3. 조인 순서 제어 방법

- 조인 순서 제어를 위한 힌트 사용

/*+ ORDERED*/

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

 

/*+LEADING(테이블명)*/

  힌트 내에 제시한 테이블이 드라이빙으로 채택됨

  (ORDERED힌트와 LEADING을 같이 사용할 경우 LEADING은 무시된다)

 

- 뷰 활용

- SUPPRESSING 활용: 인덱스에 있는 칼럼에 변형을 가하지 않음

- FROM절의 테이블 순서 변경

 

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

- 양쪽 모두 조인 조건에 대한 인덱스가 없는 경우

  -> DRIVEN 테이블에 대한 FULL TABLE SCAN 횟수는 DRIVING 테이블로부터 읽는 ROW수

 

* NESTED JOIN! 조인 순서가 최적화 되어야 함.

---

5. SORT/MERGE JOIN

* 사용하는 경우

  - 연결 조건에 인덱스가 전혀 없는 경우

  - 대용량의 자료를 조인해야 함으로써 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은 경우

 

* 과정

    1) 각 테이블에 대해 동시에 독립적으로 데이터를 읽음

    2) 읽혀진 각 테이블의 데이터를 조인을 위한 조건에 대하여 정렬 수행

    3) 정렬이 모두 끝난후 조인 작업 수행

 

* 튜닝 포인트

  - 각 테이블로부터 데이터를 빠르게 읽게 하기

  - 메모리(SORT_AREA_SIZE) 최적화

6. SORT/MERGE JOIN 불리한 케이스

- 각 테이블에서 읽은 데이터의 양이 다른 경우, 한 테이블에서 SORT이 끝나면 나머지 다른 쪽의 SORT을 기다려야 한다.

- SORT는 동시에 수행됨

7. SORT/MERGE JOIN 장/단점

- 연결고리에 인덱스가 생성되어 있지 않은 경우에 빠른 조인을 위하여 사용

- 조인하고자 하는 각 테이블에 대해서 독립적으로 데이터를 읽을 때 얼마나 빠르게 할 것인가가 중요

- 각 테이블로부터 읽혀진 데이터를 연결고리를 사용해 정렬할 때 얼마나 빠르게 할 것인가가 중요

---

8. HASH JOIN

* NESTED LOOP JOIN << 인덱스 사용에 의한 랜덤 액세스의 오버헤드

* SORT/MERGE JOIN << 정렬 작업으로 인한 오버헤드

 

- SORT/MERGE JOIN과 비교하면, 각 테이블에 대한 처리를 독립적으로 하는 것 같으나, HASH JOIN은 Driving Table이 있음

- 읽은 각 테이블의 데이터를 조인하기 위해 Hashing을 이용해 값을 만들고 이를 통해 조인을 수행함

- 튜닝포인트

  1) Driving Table 결정

  2) 각 테이블로부터 데이터를 읽을 때 빠르게 읽도록 함

  3) 메모리 HASH_AREA_SIZE를 최적화 -> 여유가 있을 경우 오히려 큰 데이터를 먼저 읽게 할 수도 있음

9. HASH JOIN 장/단점

- HASH BUCKET이 조인 집합에 구성되어 해시 함수 결과를 저장해야 함 -> 많은 메모리와 CPU 자원 소모

- 기본적으로 HASH_AREA_SIZE에 지정된 크기만큼의 메모리가 할당되어 사용됨

- 조인을 수행하기에 메모리가 부족할 경우, 가장 큰 순서대로 Hash Bucket이 Temporary Tablespace로 내려가서 구성됨

  (디스크로 내려간 Hash Bucket에 변경이 일어날 때마다 디스크 IO가 발생하여 성능이 내려감)

+ 하드웨어 자원이 여유 있지 않을 경우, 다른 조인보다 느려질 수 있음!

 

 

 

출처: 멀티캠퍼스 '업무에 바로 쓰는 SQL 튜닝 입문'