친절한 SQL 튜닝_4장.조인 튜닝_1

 

조인 튜닝

 

4.1. NL 조인

4.1.1 기본 메커니즘

<C, JAVA>
 
for(int i=0; i<100; i++){ -- outer loop
    for(in j=0; j<100; j++){ -- inner loop
        // Do anything.,,
    }
}

NL 조인은 위 중첩 루프문과 같은 수행 구조를 사용한다. 중첩 루프문을 사용하는 아래 PL/SQL 코드는 NL 조인이 어떤 순서로 데이터를 액세스하는지 잘 설명해 준다.

 

begin
    for outer in (select 사원번호, 사원명 from 사원 where 입사일자 >= ‘19960101’)
        loop -- outer 루프
            for inner in (select 고객명, 전화번호 from 고객 where 관리사원번호 = outer.사원번호)
            loop -- inner 루프
            dbms_output.put_line(outer.사원명 || ‘:’ || inner.고객명 || ‘:’ || inner.전화번호);
        end loop
    end loop
end

일반적으로 NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다. Outer 쪽 테이블(위 PL/SQL에서 사원)은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다. Table Full Scan 하더라도 그것은 한 번에 그치기 때문이다. 반면, Inner쪽 테이블(위 PL/SQL에서 고객)은 인덱스를 사용해야 한다. 위 PL/SQL을 예로 들어, Inner 루프에서 관리사원번호로 고객 데이터를 검색할 때 인덱스를 이용하지 않으면, Outer 루프에서 읽은 건수만큼 Table Full Scan을 반복하기 때문이다.

결국, NL 조인은 ‘인덱스를 이용한 조인 방식’이라고 할 수 있다.

 

4.1.2 NL 조인 실행계획 제어

NL 조인을 제어할 때는 아래와 같이 use_nl 힌트를 사용한다.

 

SELECT /*+ ordered use_nl(c) */
       e.사원명, c.고객명, c.전화번호
  FROM 사원 e, 고객 c
 WHERE e.입사일자 >= ‘19960101’
   AND c.관리사원번호 = e.사원번호

ordered 힌트는 FROM 절에 기술한 순서대로 조인하라고 옵티마이저에 지시할 때 사용한다. use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용한다. 위에서는 ordered와 use_nl(c) 힌트를 같이 사용했으므로 사원 테이블(-> Driving 또는 Outer Table) 기준으로 고객 테이블(-> Inner 테이블)과 NL 방식으로 조인하라는 뜻이다.

세 개 이상 테이블을 조인할 때는 힌트를 아래처럼 사용한다.

 

SELECT /*+ ordered use_nl(B) use_nl(C) use_hsah(D) */
  FROM A, B, C, D
 WHERE ~

해석해 보면, A->B->C->D 순으로 조인하되, B와 조인할 때 그리고 이어서 C와 조인할 때는 NL 방식으로 조인하고, D와 조인할 때는 해시 방식으로 조인하라는 뜻이다.

ordered 대신 아래와 같이 leading 힌트를 사용할 수도 있다. 이 힌트를 사용하면 FROM 절을 바꾸지 않고도 마음껏 순서를 제어할 수 있어 편리하다.

 

SELECT /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hsah(B) */
  FROM A, B, C, D
 WHERE ~

아래는 ordered나 leading 힌트를 기술하지 않았다. 네 개 테이블을 NL 방식으로 조인하되 순서는 옵티마이저가 스스로 정하도록 맡긴 것이다. 

SELECT /*+ use_nl(A, B, C, D) */
  FROM A, B, C, D 
 WHERE ~

 

4.1.3 NL 조인 수행 과정 분석

NL 조인 수행 과정을 분석하기 위해 아래와 같이 조건절을 추가해 보자. 힌트에 지시한 대로 수행할 때, 조건절 비교 순서는 어떻게 될까? 조건절 우측에 표시한 번호로 순서를 나열해 보자.

 

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 힌트를 명시했으므로 둘 다 인덱스를 이용해서 액세스한다. 인덱스명은 명시하지 않았으므로 어떤 인덱스를 사용할지는 옵티마이저가 결정한다. 조건절 비교 순서와 함게 위 다섯 개 인덱스 중 어떤 것이 사용될지도 고민해 보기 바란다.

조건절 비교 순서와 인덱스를 찾았는가? 힌트에 지시한 대로 SQL문을 수행했을 때 실행계획은 아래와 같다. 사용되는 인덱스는 사원_X1과 고객_X1인 것을 알 수 있다.

 

Id Operation                        Name    Rows    Bytes    Cost
0 SELECT STATEMENT                            5    58    5
1   NESTED LOOPS                            5    58    5
2    TABLE ACCESS BY INDEX ROWID    사원    3    20    2
3     INDEX RANGE SCAN                사원_X1    5        1
4    TABLE ACCESS BY INDEX ROWID    고객    5    76    2
5     INDEX RANGE SCAN                고객_X1    8        1

SQL 조건절 우측에 표시한 번호로 조건절 비교 순서를 나열하면, 2 -> 3 -> 1 -> 4 순이다.

1.조건절 번호 2 : 입사일자 >= ‘19960101’ 조건을 만족하는 레코드를 찾으려고 사원_X1 인덱스를 Range 스캔한다. (실행계획 ID=3)

2.조건절 번호 3 : 사원_X1 인덱스에서 읽은 ROWID로 사원 테이블을 액세스해서 부서코드 = ‘Z123’ 필터 조건을 만족하는지 확인한다. (실행계획 ID=2)

3.조건절 번호 1 : 사원 테이블에서 읽은 사원번호 값으로 조인 조건(c.관리사원번호 = e.사원번호)을 만족하는 고객 쪽 레코드를 찾으려고 고객_X1 인덱스를 Range 스캔한다. (실행계획 ID=5)

4.조건절 번호 4 : 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스해서 최종주문금액 >= 20000 필터 조건을 만족하는지 확인한다. (실행계획 ID=4)

여기서 기억할 것은, 각 단계를 모두 완료하고 다음 단계로 넘어가는 게 아니라 한 레코드식 순차적으로 진행한다는 사실이다.

 

4.1.4 NL 조인 튜닝 포인트

첫 번째 튜닝 포인트는 사원_X1 인덱스를 읽고 나서 사원 테이블을 액세스하는 부분이다. 여기서는 단일 컬럼 인덱스를 ‘>=’ 조건으로 스캔했으므로 비효율 없이 6(=5+1)건을 읽었고, 그만큼만 테이블 랜덤 액세스가 발했다. 만약 사원 테이블로 아주 많은 양의 랜덤 액세스가 발생했고, 테이블에서 부서코드 = ‘Z123’ 조건에 의해 필터링되는 비율이 높다면 어떻게 해야 할까? 사원_X1 인덱스에 부서코드 컬럼을 추가하는 방안을 고려해야 한다.

두 번째 튜닝 포인트는 고객_X1 인덱스를 탐색하는 부분이다. 고객_X1 인덱스를 탐색하는 횟수, 즉 조인 액세스 횟수가 많을수록 성능이 느려진다. 조인 액세스 횟수는 Outer 테이블인 사원을 읽고 필터링한 결과 건수에 의해 결정된다.

세 번째 튜닝 포인트는 고객_X1 인덱스를 읽고 나서 고객 테이블을 액세스하는 부분이다. 여기서도 최종주문금액 >= 20000 조건에 의해 필터링되는 비율이 높다면 고객_X1 인덱스에 최종주문금액 컬럼을 추가하는 방안을 고려해야 한다.

마지막으로, 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다는 사실도 기억하기 바란다. 사원_X1 인덱스를 스캔하면서 추출한 레코드가 많으면, 사원 테이블로 랜덤 액세스하는 횟수, 고객_X1 인덱스를 탐색하는 횟수, 고객 테이블로 랜덤 액세스하는 횟수가 전반적으로 많아진다.

 

4.1.5 NL 조인 특징 요약

1. 랜덤 액세스 위주의 조인 방식이다.

2. 조인을 한 레코드씩 순차적으로 진행한다.

3. 다른 조인 방식과 비교할 때 인덱스 구성 전략이 특히 중요하다.

NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리(OLTP) 시스템에 적합한 조인 방식이라고 할 수 있다.

 

4.1.6 NL 조인 튜닝 실습

 

4.1.7 NL 조인 확인 메커니즘

 

버전이 올라가면서 오라클은 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입했다. ‘테이블 Prefetch’는 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다. ‘배치 I/O’는 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다. 두 기능 모드, 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안되었다.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음