NESTED LOOP JOIN(이하 NL 조인)은 테이블을 연결(조인)하는 가장 기본적인 방식입니다. 인덱스를 이용한 조인으로 인덱스 튜닝 원리를 그대로 적용할 수 있습니다. 그럼 NL 조인이 동작하는 기본적인 메커니즘에 대해 알아보겠습니다. NL 조인은 중첩 루프문과 같은 수행 구조입니다. 아래 PL/SQL 코드를 통해 NL 조인이 어떤 순서로 데이터를 액세스 하는지 알 수 있습니다.
begin
for outer in ( select 사원번호, 사원명 from 사원 where 입사일자 >= '19960101')
loop
for inner in (select 고객명, 전화번호 from 고객 where 관리사원번호 = outer.사원번호)
loop
dmbs_output.put_line(outer.사원명 || ':' || inner.고객명 || ':' || inner.전화번호);
end loop;
end loop;
end
일반적으로 NL 조인은 OUTER와 INNER 테이블의 인덱스를 모두 활용합니다. OUTER 테이블의 크기가 크지 않으면 인덱스를 사용하지 않아도 됩니다. OUTER 테이블은 한 번만 읽으면 되기 때문에 조인성능에 크게 영향을 주지 않습니다. 반면 INNER 테이블은 조건에 맞는 OUTER 테이블의 레코드만큼 반복해서 읽기 때문에 반드시 최적화된 인덱스를 사용해야 합니다. 자세한 작동방식은 다음과 같습니다.
-- 사원_x1 입사일자
-- 고객_x1 사원번호
select e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사번호 >= '19960101'
and c.관리사원번호 = e.사원번호1. 사원_x1 인덱스에서 입사일자 >= '19960101'인 첫 번째 레코드를 찾는다.
2. 인덱스에서 읽은 ROWID로 사원 테이블 레코드를 찾는다.
3. 사원 테이블에서 읽은 사원번호 '0006'으로 고객_x1 인덱스를 검색한다.
4. 고객_x1 인덱스에서 읽은 ROWID 로 고객 테이블 레코드를 찾아간다.
5. 고객_x1 인덱스에서 한 건 더 스캔하고 관리사원번호가 '0006' 임을 확인한다.
6. 고객_x1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아간다.
(고객_x1 인덱스에서 한 건 더 스캔하고 관리사원번호가 0006보다 크므로 거기서 인덱스 스캔을 멈춘다.)
7. 사원_X1 인덱스에서 한 건 더 스캔해서 입사일자가 '19960712'인 레코드를 읽는다.
8. 인덱스에서 읽은 ROWID로 사원 테이블 레코드를 찾는다.
9. 사원 테이블에서 읽은 사원번호 '0003'으로 고객_x1 인덱스를 검색한다.
10. 고객_x1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아간다.
11. 고객_x1 인덱스에서 한 건 더 스캔하고 관리사원번호가 '0003' 임을 확인한다.
12. 고객_x1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아간다.
(고객_x1 인덱스에서 한 건 더 스캔하고 관리사원번호가 0003보다 크므로 거기서 인덱스 스캔을 멈춘다.)
NL 조인의 일반적인 실행계획입니다.
-- EXCUTION PLAN
------------------
0 SELECT STATEMENT
0 1 NESTED LOOPS
2 1 TABLE ACCESS BY INDEX ROWID OF EMP(TABLE)
3 2 INDEX RANGE SCAN OF EMP_X1(INDEX)
4 1 TABLE ACCESS BY INDEX ROWID OF CUST(TABLE)
5 4 INDEX RANGE SCAN OF CUST_X1(INDEX)
옵티마이저가 NL 조인하도록 유도하는 힌트는 use_nl입니다. ordered 힌트는 옵티마이저가 FROM 절에 기술한 순서대로 조인하도록 유도합니다. 결과적으로 아래의 힌트는 사원테이블을 기준으로 고객 테이블을 NL조인하도록 유도합니다.
select /*+ordered use_nl(c)*/
e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사번호 >= '19960101'
and c.관리사원번호 = e.사원번호
세 개 이상의 테이블을 조인할 때는 아래와 같은 힌트로 NL조인을 유도합니다.
select /*+ordered use_nl(B), use_nl(C) use_hash(B)*/
*
from A, B, C, D
where ....
조인 순서는 A-B-C-D, A-B는 NL 조인하고 그 결과와 C를 NL 하고 그 결과와 D를 해시 조인하라는 의미입니다. leading 힌트를 사용하면 지정한 순서대로 조인하도록 유도합니다. FROM 절을 바꾸지 않고 조인순서를 제어할 수 있어 편리합니다.
select /*+ leading(C, A, D, B), use_nl(A), use_nl(D) use_hash(B)*/
*
from A, B, C, D
where ....
조인조건 이외에 테이블 별로 추가적인 조건이 있는 경우 조인 수행과정에 대해 알아보겠습니다.
select /*+ordered use_nl(c) index(e) index(c)*/
e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호 --- 1
and e.입사번호 >= '19960101' --- 2
and e.부서코드 = 'Z123' --- 3
and c.최종주문금액 >= 20000 --- 4
-- 인덱스 구성은 다음과 같다.
-- 사원_Pk : 사원번호
-- 사원_X1 : 입사일자
-- 고객_Pk : 고객번호
-- 고객_X1 : 관리사원번호
-- 고객_X2 : 최종주문금액
우선 힌트를 보면 두 테이블에 index 힌트를 명시했으므로 둘 다 인덱스를 이용해 액세스 하지만 구체적인 인덱스 명을 지정하지 않았으므로 옵티마이저가 어떤 인덱스를 사용할지 결정합니다. 힌트대로 유도된 실행계획은 다음과 같습니다. 사원_x1 인덱스와 고객_x1 인덱스가 액세스에 사용됐음을 알 수 있습니다.
-- OPERATION
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID (사원)
INDEX RANGE SCAN (사원_X1)
TABLE ACCESS BY INDEX ROWID (고객)
INDEX RANGE SCAN (고객_X1)
조건절의 실행순서는 다음과 같습니다.
1. 입사일자 >= '19960101' 조건을 만족하는 레코드를 찾기 위해 사원_X1 인덱스를 RANGE 스캔합니다.
2. 사원_X1 인덱스에서 읽은 ROWID로 사원 테이블을 액세스 해서 부서코드 'Z123' 테이블 필터 조건을 만족하는지 확인합니다.
3. 조인조건에 따라 사원 테이블에서 읽은 사원번호 값과 같은 관리사원번호 값이 있는 고객 쪽 레코드를 찾으려고 고객_X1 인덱스를 RANGE 스캔합니다.
4. 고객 X1_INDEX에서 읽은 ROWID로 고객 테이블을 액세스 해서 최종주문금액 >= 20000 필터 조건을 만족하는지 확인합니다.
NL 조인 튜닝 포인트
-- 2 3 1 4
select /*+ordered use_nl(c) index(e) index(c)*/
e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호 --- 1
and e.입사번호 >= '19960101' --- 2
and e.부서코드 = 'Z123' --- 3
and c.최종주문금액 >= 20000 --- 4
성능 개선을 위해 위의 쿼리와 힌트를 튜닝할 수 있습니다. 첫 번째 튜닝 포인트는 사원_x1 인덱스를 읽고 사원 테이블을 액세스 하는 부분입니다. 단일 칼럼 인덱스를 >= 조건으로 스캔했므로 인덱스 스캔 비효율 없이 6건을 읽었습니다. 6건만큼의 테이블 렌덤 액세스가 발생했습니다.
만약 >= 조건에 맞는 인덱스 레코드가 많아 많은 양의 랜덤 액세스가 발생하고 테이블에서 '= Z123' 조건으로 필터링되는 비율이 높다면 사원_X1 인덱스에 부서코드 칼럼을 추가하는 방안을 고려해야 합니다.
두 번째 튜닝 포인트는 고객_X1 인덱스를 탐색하는 부분입니다. OUTER 테이블인 사원의 필터링 결과에 따라 고객 인덱스에 액세스 하는 횟수가 결정됩니다. 부서코드 = 'Z123' 조건을 만족하는 레코드가 3건이라고 가정하면 고객 인덱스에 대한 액세스는 3번 발생합니다. 만약 조건을 만족하는 레코드가 10만 건이고 고객_X1 인덱스의 DEPTH 가 3이라면 인덱스 수직적 탐색과정에서만 30만 개의 블록을 읽어야 합니다.
세 번째 튜닝 포인트는 고객_X1 인덱스를 읽고 나서 테이블 액세스 하는 부분입니다. 최종주문금액 조건에 의해 필터링되는 비율이 높다면 이를 인덱스에 추가하는 방안을 고려해야 합니다.
올바른 조인 메서드 선택
OLTP 성 시스템에서 튜닝할 때는 NL 조인을 우선적으로 고려합니다. 성능이 느리다면 튜닝 포인트에 따라 각 단계의 수행 일 량을 분석해 과도한 랜덤 액세스가 발생하는 지점을 우선 파악합니다. 조인 순서 변경, 인덱스 교체를 고려하고 필요시 인덱스 추가 또는 구성 변경도 고려해 볼 수 있습니다. 종합적으로 검토한 결과 NL 조인으로 결코 좋은 성능을 낼 수 없다는 판단이 들면 소트 머지 조인이나 해시조인을 검토합니다.
NL 조인 특징 요약
NL 조인은 랜덤 액세스 위주의 조인 방식입니다. 레코드 하나를 읽기 위해 블록을 통째로 읽는 렌덤 액세스로 인해 비효율이 존재합니다. 인덱스 구성이 완벽하더라도 대량 데이터를 조회할 때는 불리한 이유입니다. 두 번째 특징은 조인을 한 레코드씩 순차적으로 진행한다는 점입니다. 이는 부분처리에서 탁월한 성능을 보여줍니다.
SELECT /*+ORDERD USE_NL(B) INDEX_DESC(A (게시판구분, 등록일시))*/
A.게시글ID, A.제목, B.작성자명, A.등록일시
FROM 게시판 A, 사용자 B
WHERE A.게시판구분 = 'NEWS' -- 게시판IDX : 게시판구분 + 등록일시
AND B.사용자ID = A.작성자ID -- 사용자 IDX : 사용자ID
ORDER BY A. 등록일시 DESC
순차적으로 진행하므로 먼저 엑세스 되는 테이블 범위에 의해 전체 일 량이 결정됩니다. 마지막으로 인덱스 구성 전략이 특히 중요하게 작용합니다. 이런 특징을 종합할 때, NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 OLTP 시스템에 적합한 조인방식입니다.
NL 조인 실습
select /*+ordered use_nl(c) index(e) index(c)*/
e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호 --- 3
and e.입사일자 >= '19960101' --- 1
and e.부서코드 = 'Z123' --- 2
and c.최종주문금액 >= 20000 --- 4
-- query
-- parse 0
-- execute 0
-- fetch 9
-- ROWS ROW SOURCE OPERATION
-- 5 NESTED LOOPS
-- 3 TABLE ACCESS BY INDEX ROWID OF 사원
-- 2780 INDEX RANGE SCAN OF 사원_X1
-- 5 TABLE ACCESS BY INDEX ORWID OF 고객
-- 8 INDEX RANGE SCAN OF 고객_X1
사원_X1 인덱스를 스캔하고 사원 테이블에 액세스 한 횟수가 2780 건인데, 테이블에서 부서코드 = 'Z123' 조건을 필터링한 결과는 세 건에 그칩니다. 불필요한 테이블 액세스를 많이 했으므로 인덱스에 테이블필터조건을 추가하는 것을 고려할 필요가 있습니다. 칼럼을 추가하면 사원_X1 인덱스는 [입사일자 + 부서코드]가 됩니다. 조건절을 살펴보면 입사일자는 부등호 조건이고 부서코드는 등치조건입니다. 만약 조건에 해당하는 레코드가 많다고 등치 조건에 해당하는 레코드가 적다면 과도한 인덱스 액세스가 발생하므로 [부서코드 + 입사일자] 순의 인덱스를 고려해 볼 수 있습니다.
만약 원본 쿼리의 트레이스결과가 아래와 같으면 어떨까요?
-- ROWS ROW SOURCE OPERATION
-- 5 NESTED LOOPS
-- 2780 TABLE ACCESS BY INDEX ROWID OF 사원 (cr=166 pr=2 pw=0)
-- 2780 INDEX RANGE SCAN OF 사원_X1 (cr=4 pr=0 pw=0)
-- 5 TABLE ACCESS BY INDEX ORWID OF 고객 (cr=2566 pr=384 pw=0)
-- 8 INDEX RANGE SCAN OF 고객_X1 (cr=2558 pr=383 pw=0)
이번에는 사원인덱스에서 고객 테이블로 액세스 하는 횟수가 결과집합인 5건에 비해 과도하게 많습니다. 이럴 때는 조인 순서 변경을 고려해 볼 수 있습니다. 만약 조인 순서를 바꾸어도 성능상 소득이 없다면 소트머지 조인이나 해시 조인을 검토해 볼 수 있습니다.
NL 조인 확장 메커니즘
NL 조인의 성능을 높이기 위해 PREFETCH 기능과 BATCH I/O 기능이 도입됐습니다.
테이블 PREFETCH는 인덱스를 이용해 테이블을 액세스 하다가 디스크 I/O가 필요하면 이어서 읽게 될 블록까지 미리 테이블에서 읽어 버퍼캐시에 저장하는 기능입니다.
BATCH I/O는 디스크 I/O CALL을 미뤘다가 읽을 블록이 일정량 쌓이면 한 번에 처리하는 기능입니다. 랜덤 액세스로 인해 발생하는 부하를 최소화하는 전략입니다.
-- 전통적인 NL 조인 실행계획
-- 5 NESTED LOOPS
-- 3 TABLE ACCESS BY INDEX ROWID OF 사원
-- 3 INDEX RANGE SCAN OF 사원_X1
-- 5 TABLE ACCESS BY INDEX ORWID OF 고객
-- 8 INDEX RANGE SCAN OF 고객_X1
-- 테이블 PREFETCH 실행계획
-- 5 TABLE ACCESS BY INDEX ROWID OF 고객
-- 12 NESTED LOOPS
-- 3 TABLE ACCESS BY INDEX ROWID OF 사원
-- 3 INDEX RANGE SCAN OF 사원_X1
-- 8 INDEX RANGE SCAN OF 고객_X1
-- BATCH I/O 실행계획
-- 5 NESTED LOOPS
-- 8 NESTED LOOPS
-- 3 TABLE ACCESS BY INDEX ROWID OF 사원
-- 3 INDEX RANGE SCAN OF 사원_X1
-- 8 INDEX RANGE SCAN OF 고객_X1
-- 5 TABLE ACCESS BY INDEX ROWID OF 고객
INNER TABLE에 대한 디스크 I/O 과정에서 테이블 PREFETCH 기능이 작동함을 알리기 위함입니다. NLJ_PREFETCH, NO_NLJ_PREFETCH 힌트를 이용해 이 실행계획이 나오게 하거나 전통적인 방식으로 실행되게 할 수 있습니다.
INNER 쪽 테이블에 대한 디스크 I/O 과정에서 배치 I/O 기능이 작동할 수 있음을 표시합니다. nlj_batching, no_nlj_batching 힌트를 이용해 이 실행계획이 나오게 할 수도 있고 안 나오게 할 수도 있습니다. 세 가지 실행계획 모두 INNER 쪽 테이블 블록을 모두 버퍼케시에서 읽는다면 성능에 차이는 없습니다. 데이터 출력 순서도 동일합니다. 다만, 일부를 테이블에서 읽는 구조라면 성능에 차이가 날 수도 있고 배치 I/O 실행계획이 나타날 때는 결과 집합의 정렬 순서도 다를 수 있습니다. ORDER BY 절을 명시하는 것이 좋습니다.
(
SELECT A.*, ROWNUM NO
FROM
(
SELECT 등록일시, 번호, 제목, 작성자번호, 게시판유형, 질문유형
FROM 게시판
WHERE 게시판유형 = :TYPE
ORDER BY 등록일시 DESC -- 인덱스 구성 : 게시판유형 + 등록일시
) A
WHERE ROWNUM <= (:PAGE * 10)
) A, 회원 B
WHERE A.NO >= (:PAGE-1) * 10 + 1
AND B.회원번호 = A.작성자번호
ORDER BY A.등록일시 DESC --> 11G 부터 여기에 ORDER BY 명시해야 정렬 순서 보장
11G부터는 BATCH I/O를 우선방식으로 동작하기 때문에 NL 조인 결과집합이 항상 일정한 순서로 출력되기를 원한다면, 배치 I/O 기능이 작동하지 못하도록 no_nlj_batching 힌트를 추가하거나 order by 절에 정렬기준을 명시해야 합니다.
'DB > SQL튜닝' 카테고리의 다른 글
| SQL 튜닝이론 - 해시조인의 특징과 유도방법 (0) | 2024.01.28 |
|---|---|
| SQL튜닝이론 - 소트머지조인 (0) | 2024.01.28 |
| SQL 튜닝 - 인덱스 설계방법 (0) | 2024.01.24 |
| SQL튜닝 - PL/SQL 함수호출로 인한 부하를 줄이는 인덱스 구성방법 (0) | 2024.01.23 |
| SQL 튜니이론 - 다양한 선택조건 처리방식 OR EXPANSION, LIKE/BETWEEN, UNION ALL, NVL/DECODE, DYNAMIC QUERY (0) | 2024.01.23 |
