친절한 SQL 튜닝 - 4장 조인 튜닝

4장 조인 튜닝

4.1.1 NL 조인 기본 메커니즘

SELECT E.사원명, C.고객명, C.전화번호
FROM 사원 E, 고객 C
WHERE E.입사일자 >= '19960101'
AND C.관리사원번호 = E.사원번호

• 위 테이블에서 1996년 1월 1일 이후 입사한 사원이 관리하는 고객 데이터를 추출하는 데이터를 만들어 보자.
• 가장 쉽게 생각하는 방법은 사원 테이블로부터 1996년 1월 1일 이후 입사한 사원을 찾은 후, 고객 테이블에서 사원번호가 일치하는 레코드를 찾는 것 이것이 Nested Loop 조인이 사용하는 알고리즘이다.
• 아래 수행 구조를 통해 쉽게 이해할 수 있다.

<C ,JAVA>
for(i=0; i<100; i++){
    for(j=0; j<100; j++){
        ...
    }
}

<PL/SQL>
for outer in 1..100 loop
    for inner in 1..100 loop
        dbms_output.put_line(outer || ' : ' || inner);
    end loop;
end loop;

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

• NL 조인은 위 중첩 루프문과 같은 수행 구조를 사용한다.
• Outer와 Inner 양쪽 테이블 모두 인덱스를 이용하며 Outer쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수도 있다.
• 반면 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.사원번호

• 위의 힌트는 사원 테이블(Driving Table)을 기준으로 고객 테이블(Inner Table)과 NL 방식으로 조인하라는 뜻이다.
• 세 개 이상 테이블을 조인할 때는 힌트를 아래처럼 사용한다.

select /*+ordered use_nl(B) use_nl(C) use_hash(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_hash(B)*/
from A,B,C,D
where ...

4.1.5 NL 조인 특징 요약

• NL 조인의 첫 번째 특징
  • 랜덤 액세스 위주의 조인 방식
  • 레코드 하나를 읽으려고 블록을 통쨰로 읽는 랜덤 액세스 방식은 메모리 버퍼에서 빠르게 읽더라고 비효율이 존재한다.
  • 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유이다.
• NL 조인의 두 번쨰 특징
  • 조인을 한 레코드씩 순차적으로 진행한다.
  • 대량 데이터 처리 시 첫 번재 특징으로 불리하지만 두 번째 특징 때문에 아무리 큰 테이블이어도 빠른 응답 속도를 낼 수 있다.

4.2 소트 머지 조인

• 조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때, 옵티마이저는 NL 조인 대신 소트 머지 조인이나 해시 조인을 선택한다.
• 해시 조인의 등장으로 소트 머지 조인 쓰임이 적지만 해시 조인을 사용할 수 없는 상황에서 대량 데이터를 조인하고자 할 때 여전히 유용하다.

4.2.1 SGA vs PGA

• System Glolbal Area(SGA) 공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유 가능하다.
• 동시에 직렬화하여 액세스 하기 위한 Lock 메커니즘으로 래치가 존재한다.
• 오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA라고 부른다.
• PGA는 독립적인 메모리 공간으로 래치 메커니즘이 불필요하므로 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

4.2.2 소트 머지 조인 기본 메커니즘

1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지한다.

• 소트 머지 조인은 아래와 같은 use_merge 힌트로 유도한다.

SELECT /*+ ordered use_merge(c)*/
    E.사원번호, E.사원명, E.입사일자, C.고객번호, C고객명, C.전화번호, C.최종주문금액
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '19960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

• 위 SQL 수행 과정은 아래와 같은 과정으로 풀어서 설명 가능하다.

1. 조건에 해당하는 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬하여 PGA 영역에 할당된 Sort Area에 저장한다. 만약에 PGA에 담을 수 없을 정도로 크면 Temp 테이블 스페이스에 저장한다.
2. 조건에 해당하는 고객 데이터를 읽어 조인컬럼인 관리사원번호 순으로 정렬한다. 마찬가지로 PGA 영역에 할당.
3. PGA에 저장한 사원 데이터와 고객 데이터를 조인한다. (아래 SQL 문 참고)

BEGIN 
    FOR OUTER IN (SELECT * FROM PGA_정렬된_사원_데이터)
    LOOP -- OUTER 루프
        FOR INNER IN (SELECT * FROM PGA_정렬된_고객_데이터
                      WHERE 관리사원번호 = OUTER.사원번호)
        LOOP -- INNER 루프   
            DBMS_OUTPUT.PUT_LINE(...);
        END LOOP;
    END LOOP;
END;

• 위에 SQL에서 볼 수 있듯이 정렬한 데이터를 NL조인과 같이 사용한다는 것을 볼 수 있다.
• Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 인덱스가 없어도 사용할 수 있는 대용량에 적합한 것이 소트 머지이다.

4.2.3 대량 데이터를 조인할 때 소트 머지 조인이 빠른 이유

• NL 조인은 인덱스를 이용한 조인 방식으로 액세스하는 모든 블록을 랜덤 액세스 방식으로 DB 버퍼캐시를 경유해서 읽는다.
• 즉, 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거치며 버퍼 캐시에 없으면 디스크에서 읽어 들인다.
• 인덱스를 이용하기 때문에 인덱스 손익분기점 한계를 그대로 드러내어 대량 데이터 조인에 NL조인이 불리한 이유이다.
• 반면, 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장한 후 조인한다.
• PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없어 소트 머지 조인이 대량 데이터 조인에 유리한 이유이다.

4.2.4 소트 머지 조인의 주용도

• 랜덤 액세스 위주의 NL조인이 대량 데이터 처리에 한계를 보일 떄 소트 머지 조인이 인기 있었으나 더 빠른 해시조인의 등장으로 소트 머지 조인의 쓰임새는 예전만 못하다.
• 하지만 해시 조인은 조인 조건식이 등치(=) 조건이 아닐 떄 사용할 수 없다는 단점이 존재한다.
• 그러므로 소트 머지 조인은 아래와 같은 상황에 주로 사용된다.

1. 조인 조건식이 등치(=) 조건이 나닌 대량 데이터 조인
2. 조인 조건식이 아예 없는 조인

4.2.5 소트 머지 조인 이해하기

SELECT /*+ ordered use_merge(c)*/
    E.사원번호, E.사원명, E.입사일자, C.고객번호, C고객명, C.전화번호, C.최종주문금액
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '19960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

• ordered는 FROM 절에 기술한 순서대로 조인하라고 옵티마이저에 지시하는 힌트이다.
• ordered와 use_merge(c) 힌트를 같이 사용했으므로 양쪽 테이블을 조인 컬럼 순으로 각각 정렬한 후 '정렬된 사원' 기준으로 '정렬된 고객'과 조인하라는 뜻으로 해석하면 된다.
• 소트 머지의 시행계획은 아래와 같다.

Execution Plan
----------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
    MERGE JOIN 
        SORT(JOIN)
            TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
                INDEX (RANGE SCAN) OF '사원_X1' (INDEX)
        SORT(JOIN)
            TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
                INDEX (RANGE SCAN) OF '고객_X1' (INDEX)

4.2.6 소트 머지 조인 특징 요약

• 소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 같다.
• 양쪽 집합을 정렬한 다음에 NL조인과 같은 방식으로 사용하지만, PGA영역에 저장한 데이터를 이용하기 때문에 빠르다.
• NL 조인은 조인 컬럼에 대한 인덱스 유무에 크게 영향을 받지만, 소트 머지 조인은 영향을 크게 받지 않는다.
• 따라서 조인 컬럼에 인덱스가 없는 상황에서 대용량 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리하다.
• 스캔 위주의 액세스 방식을 사용한다는 점도 중요한 특징이지만, 모든 처리가 스캔 방식으로 이루어지지는 않고 이는 해시 조인도 마찬가지이다.

4.3 해시 조인

• 소트 머지 조인과 해시 조인은 조인 과정에 인덱스를 이용하지 않아 대량 데이터 조인할 때 NL조인보다 훨씬 빠르다.
• 소트 머지 조인은 양쪽 테이블을 정렬하는 부담이 있지만, 해시 조인은 그런 부담도 없다.

4.3.1 해시 조인 기본 메커니즘

1. Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블을 생성한다.
2. Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하며 조인한다.

SELECT /*+ ordered use_hash(c)*/
    E.사원번호, E.사원명, E.입사일자, C.고객번호, C고객명, C.전화번호, C.최종주문금액
FROM 사원 E, 고객 C
WHERE C.관리사원번호 = E.사원번호
AND E.입사일자 >= '19960101'
AND E.부서코드 = 'Z123'
AND C.최종주문금액 >= 20000

• 소트 머지 조인에서 사용했던 SQL에서 처럼 해시 조인은 use_hash 힌트로 유도한다.

1. Build 단계 : 아래 조건에 해당하는 사원 데이터를 읽어 조인 컬럼인 사원번호를 해시 테이블 키 값으로 사용한다. 즉, 사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인에 데이터를 연결한다. 해시 테이블은 PGA 영역에 할당된 Hash Area에 저장한다.
2. Probe 단계 : 고객 데이터에 해당하는 조건식으로 고객 데이터를 하나씩 읽어 해시 테이블을 탐색한다. 즉, 관리사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인을 스캔해서 값이 같은 사원번호를 찾는다.

BEGIN 
    FOR OUTER IN (SELECT 고객번호, 고객명... 
                  FROM 고객 
                  WHERE 최종주문금액 >= 20000)
    LOOP -- OUTER 루프
        FOR INNER IN (SELECT 사원번호, 사원명, 입사일자 
                      FROM PGA_정렬된_사원 해시맵
                      WHERE 사원번호 = OUTER.관리사원번호)
        LOOP -- INNER 루프   
            DBMS_OUTPUT.PUT_LINE(...);
        END LOOP;
    END LOOP;
END;

• Probe 단계에서 조인하는 과정을 PL/SQL 코드로 표현하면 위와 같다.

4.3.2 해시 조인이 빠른 이유

• Hash Area에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 프로세싱 자체는 NL 조인과 같다.
• 해시 조인도 소트 머지 조인과 같이 해시 테이블을 PGA 영역에 할당하기 때문이다.
• NL 조인은 Outer 테이블 레코드마다 Inner 쪽 테이블 레코드를 읽기 위해 래치 획득 및 캐시버퍼 체인 스캔 과정을 반복하지만, 해시 조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.
• 즉 정리하자면 해시 조인은, NL 조인처럼 조인 과정에서 발생하는 랜덤 액세스 부하가 없고, 소트 머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없다.
• 해시 테이블을 생성하는 비용이 수반되지만, 둘 중 작은 집합을 Build Input 으로 선택하므로 대게는 부담이 크지 않다.

4.3.3 대용량 Build Input 처리

• 대용량 테이블 두 개를 조인할 때 인메모리 해시조인이 아니라 불가능한 상황에서는 분할, 정복 방식을 통해 조인한다.

1. 파티션 단계 : 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성한다.
2. 조인 단계 : 파티션 단계를 완료하면, 각 파티션 짝에 대해 하나씩 조인을 수행한다. 이 때 Build Input과 Probe Input은 독립적으로 각 파티션별로 작은 쪽을 선택하여 해시 테이블을 선택한다.

4.3.5 조인 메서드 선택 기준

1. 소량 데이터 조인 -> NL 조인
2. 대량 데이터 조인 -> 해시 조인
3. 대량 데이터 조인 but 해시 조인으로 처리 불가능(등치 조건 =이 아닐 때, 조인 조건식이 없을 경우) -> 소트 머지 조인

• 여기서 소량과 대량의 기준은 데이터의 양이 아니라 랜덤 액세스가 많아 성능이 나오지 않는 경우도 대량 데이터 조인에 해당한다.
• 수행 빈도가 높은 쿼리는 해시조인 보다는 NL 조인을 선택하라고 한다. 왜냐하면 NL 조인은 인덱스를 통해 쿼리의 성능을 영구적으로 유지하며 재사용하는 반면에 해시 테이블은 단 하나의 쿼리를 위해 소멸하는 자료구조이기 떄문이다.
• 즉 결론적으로 해시 조인은 아래 세 가지 조건을 만족하는 SQL문에 주로 사용한다.

1. 수행 빈도가 낮음.
2. 쿼리 수행 시간이 오래 걸림
3. 대량 데이터 조인하는 경우

4.4 서브쿼리 조인

• 옵티마이저는 Cost를 평가하고 조인에 대한 실행계획을 생성하기에 앞서 사용자가 사용하는 SQL을 최적하에 유리한 형태로 변환하는 작업부터 진행한다.

필터 오퍼레이션

• 아래 쿼리는 서브 쿼리를 필터 방식으로 처리할 때의 실행계획이다. no_unnest 힌트를 사용하면 서브 쿼리를 풀어내지 말고 수행하라고 옵티마이저에 지시하는 힌트이다.

select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists(
            select /*+ no_unnest */
            from 거래
            where 고객번호 = c.고객번호
            and 거래일시 >= trunc(sysdate, 'mm') )
          )
Execution Plan
------------------------------------------------------
0       SELECT STATEMENT OPTIMIZER = ALL_ROWS 
1   0       FILTER          
2   1           TABLE ACCESS(BY INDEX ROWID) OF '고객' (TABLE)
3   2               INDEX (RANGE SCAN) OF '고객_X01' (INDEX)
4   1           INDEX (RANGE SCAN) OF '거래_X01' (INDEX)

• 필터 오퍼레이션은 기본적으로 NL조인과 루틴이 같으나 차이점이 존재하다면 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 멈추고, 메인쿼리의 다음 로우를 계속 처리한다는 점이다.

4.4.4 스칼라 서브쿼리 조인

• 아래와 같은 GET_DNAME 함수가 있다.

CREATE OR REPLACE FUNCTION GET_DNAME(P_DEPTNO NUMBER) RETURN VARCHAR2
IS 
    L_DNAME DEPT.DNMAE%TYPE;
BEGIN
    SELECT DNAME INTO L_DNAME FROM DEPT WHERE DEPTNO = P_DEPTNO;
    RETURN L_DNAME;
EXCEPTION 
    WHEN OTHERS THEN
        RETURN NULL;
END;

• GET_DNAME 함수를 사용하는 아래 쿼리를 실행하면 함수 안에 있는 SELECT 쿼리를 메인쿼리 건 수 만큼 재귀적으로 반복 실행한다.

SELECT EMPNO, ENAME, SAL, HIREDATE, GET_DNAME(E.DEPTNO) AS DNAME
FROM EMP E
WHERE SAL >= 2000

• 위에 코드는 아래 처럼 풀어서 사용가능하다.

SELECT EMPNO, ENAME, SAL, HIREDATE,
        ,(SELECT D.NAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
FROM EMP E
WHERE SAL >= 2000

• 이는 아래 OUTER 조인문처럼 하나의 문장으로 이해하라는 뜻이며, 아래 처럼 NL 조인 방식으로 실행된다.

SELECT EMPNO, ENAME, SAL, HIREDATE, D.DNAME
FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.SAL >= 2000

스칼라 서브쿼리 캐싱 효과

• 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장해 둔다.
• 조인할 때마다 캐시에서 입력 값을 찾아보고 찾으면 저장된 출력 값을 반환한다.
• 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해준다.
• 스칼라 서브쿼리의 입력 값은, 그 안에서 참조하는 메인 쿼리의 컬럼 값이다.

SELECT EMPNO, ENAME, SAL, HIREDATE
        ,(SELECT D.NAME -- 출력 값 : D.NAME
          FROM DEPT D 
          WHERE D.DEPTNO = E.DEPTNO -- 입력 값 : E.EMPNO
        ) AS DNAME
FROM EMP E
WHERE SAL >= 2000

• 캐싱은 쿼리 단위로 이루어지며, 쿼리를 시작할 때 PGA 메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가고, 쿼리를 마치는 순간 공간을 반환한다.
• 아래의 예제는 많이 활용 되는 튜닝 기법에 해당한다.

SELECT EMPNO, ENAME, SAL, HIREDATE
        ,(SELECT GET_DNAME(E.DEPTNO) FROM DUAL) AS DNAME
FROM EMP E
WHERE SAL >= 2000

• 위와 같이 SELECT-LIST에 사용한 함수는 메인 쿼리 결과 건 수 만큼 반복 수행되는데, 스칼라 서브쿼리를 덧씌우면 호출 횟수를 최소화할 수 있다.
• 하지만 캐시 공간은 늘 부족하고, 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다.
• 즉, 캐시를 매번 확인하는 비용 때문에 오히려 성능이 더 나빠지고 CPU 사용률만 높게 만들어 메모리도 더 사용한다.
• 아래 쿼리를 예로 들어서 보자.

SELECT 거래번호, 고객번호, 영업조직ID, 거래구분코드,
    , (SELECT 거래구분명 FROM 거래구분 WHERE 거래구분코드 = T.거래구분코드) 거래구분명
FROM 거래 T
WHERE 거래일자 >= TO_CHAR(add_months(SYSDATE, -3), 'YYYYMMDD')  -- 50,000건

• 거래구분코드로 20개 값이 존재할 때 20개면 캐시에 모두 저장하고도 남아 메인 쿼리에서 50,000개를 읽는 동안 거래구분코드별 조인 액세스는 최초 한 번씩만 발생하여 이후에는 모두 캐시에서 데이터를 찾아 조인 성능을 높이는 데 큰 도움이 된다.
• 위 쿼리에서 스칼라 서브쿼리가 성능에 도움이 되려면, 최근 3개월간 수백 명 이내 일부 고객만 거래를 발생시켰어야 한다.
• 또한, 메인 쿼리 집합이 매우 작으면 캐싱은 쿼리 단위로 이루어지므로 캐시 재사용성이 낮다.

SELECT C.고객번호, C.고객명
    , (SELECT ROUND(AVG(거래금액), 2) 평균거래금액
        FROM 거래 
        WHERE 거래일시 >= TRUNC(SYSDATE, 'MM')
        AND 고객번호 = C.고객번호)
FROM 고객 C 
WHERE C.가입일시 >= TRUNC(add_months(SYSDATE, -1), 'MM')

Execution Plan
------------------------------------------------------------
0       SELECT STATEMENT OPTIMIZER=ALL_ROWS
1   0       SORT 
2   1           TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래'
3   2               INDEX (RANGE SCAN) OF '거래_X02'
4   0       TABLE ACCESS (FULL) OF '고객'
5   4           INDEX (RANGE SCAN) OF '고객_X01'

실행순서

1. 고객_X01 인덱스 범위 스캔 , 가입일시 >= C.가입일시 >= TRUNC(add_months(SYSDATE, -1), 'MM') 를 만족하는 고객 위치 정보를 찾습니다.
2. 찾은 고객 정보를 기반으로 고객번호와 고객명을 메모리에 로드합니다.
3. 거래_X02 인덱스 범위 스캔 , 거래일시 >= TRUNC(SYSDATE, 'MM') 거래 데이터를 조회합니다.
4. 조회한 데이터를 기반으로 거래금액을 메모리에 로드합니다.
5. 각 고객별로 가져온 거래 데이터를 정렬 및 그룹화하여 거래금액의 평균을 계산하고 , ROUND합니다.
6. 최종적으로 결과를 수집하여 사용자에게 반환합니다.