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

조인 튜닝

 

4.4 서브쿼리 조인

 

4.4.1 서브쿼리 변환이 필요한 이유

 

서브쿼리(Subquery)는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록(Query Block)을 말한다. 쿼리에 내장된 또 다른 쿼리다. 서브쿼리를 DBMS마다 조금씩 다르게 분류하는데, 오라클은 아래 세 가지로 분류한다.

 

SELECT c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
     , (SELECT 고객분류명 FROM 고객분류 WHERE 고객분류코드 = c.고객분류코드) -> 스칼라 서브쿼리
  FROM 고객 c
     , (SELECT 고객번호, AVG(거래금액) 평균거래
             , MIN(거래금액) 최소금액, MAX(거래금액) 최대거래
          FROM 거래
         WHERE 거래일시 >= TRUNC(SYSDATE, ‘MM’)
         GROUP BY 고객번호) t -> 인라인 뷰
 WHERE c.가입일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1), ‘mm’)
   AND t.고객번호 = c.고객번호
   AND exists (SELECT ‘X’
                 FROM 고객변경이력 h
                WHERE h.고객번호 = c.고객번호
                  AND h.변경사유코드 = ‘ZCH’
                  AND c.최종변경일시 BETWEEN h.시작일시 AND h.종료일시) -> 중첩된 서브쿼리

 

1.인라인 뷰(Inline View) : FROM 절에 사용한 서브쿼리를 말한다.

 

2.중첩된 서브쿼리(Nested Subquery) : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리를 말한다. 특히, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 ‘상관관계 있는(Correlated) 서브쿼리’라고 부른다.

 

3.스칼라 서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리다. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분에 위치에 사용할 수 있다.

 

이들 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 최적화를 수행한다.

 

 

 

4.4.2 서브쿼리와 조인

 

필터 오퍼레이션

 

아래는 서브쿼리를 필터 방식으로 처리할 때의 실행계획이다. 서브쿼리를 필터 방식으로 처리하게 하려고 의도적으로 no_nnest 힌트를 사용했다. 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’))

 

 

Excution    Plan
0    SELECT STATMENT Optimizer=ALL_ROWS (Cost=289 Card=1 Bytes=39)
1    0    FILTER
2    1    TABLE ACCESS (BY INDEX ROWID) OF ‘고객’ (TABLE) (Cost=4 Card=190 ...)
3    2        INDEX (RANGE SCAN) OF ‘고객_X01’ (INDEX) (Cost=2 Card=190.)
4    1    INDEX (RANGE SCAN) OF ‘고객_X01’ (INDEX) (INDEX) (Cost=3 Card=4K Bytes=92k)

 

필터(Filter) 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다. 따라서 위 실행계획에서 ‘FILTER‘를 ‘NESTED LOOPS’로 치환하고 처리 루틴을 해석하면 된다. NL 조인처럼 부분 범위 처리도 가능하다.

 

차이가 있다면 첫째, 필터는 메인쿼리(고객)의 한 로우가 서브쿼리(거래)의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다는 점이다.

 

NL 조인과 다른 두 번째 차이점은, 필터는 캐싱기능을 갖는다는 점이다. 이는 필터 처리한 결과, 즉 서브쿼리 입력 값에 따른 반환 값(true 또는 false)을 캐싱하는 기능이다.

 

마지막으로, 필터 서브쿼리는 일반 NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.

 

 

 

4.4.3 뷰(View)와 조인

 

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰(View) 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화 한다.

 

 

 

4.4.4 스칼라 서브쿼리 조인

 

(1)스칼라 서브쿼리의 특징

 

아래와 같이 GET_DNAME 함수를 만들어보자.

 

create or replace function GET_DNAME(p_deptno number) return varchar2
is
    l_dname dept.dname%TYPE;
begin
    select dname into l_dname from dept where deptno = p_deptno;
    return l_dname;
exception
    where others then
    return null;
end;
/

 

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

 

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

 

아래 스칼라 서브쿼리는 메인쿼리 레코드마다 정확히 하나의 값만 반환한다. 메인쿼리 건수만큼 DEPT 테이블을 반복해서 읽는다는 측면에서 함수와 비슷해 보이지만, 함수처럼 ‘재귀적으로’ 실행하는 구조는 아니다. 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행한다.

 

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

 

 

(2)스칼라 서브쿼리 캐싱 효과

 

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cashe)에 저장해 둔다. 조인할 때마다 일단 캐시에서 ‘입력 값’을 찾아보고, 찾으면 저장된 ‘출력 값’을 반환한다. 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.

 

스칼라 서브쿼리의 입력 값은, 그 안에서 참조하는 메인 쿼리의 컬럼 값이다.

 

 

1 2 3 4 5 6

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

cs

스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능이다. 이런 캐싱 메커니즘은 조인 성능을 높이는 데 큰 도움이 된다. 메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있기 때문이다.

 

캐싱은 쿼리 단위로 이루어진다. 쿼리를 시작할 때 PGA 메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가며, 쿼리를 마치는 순간 공간을 반환한다.

 

 

 

(3)스칼라 서브쿼리 캐싱 부작용

 

스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다. 반대의 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만든다. 메모리도 더 사용한다.

 

 

 

(4)두 개 이상의 값 반환

 

스카라 서브쿼리에는 치명적인 제약이 하나 있다. 두 개 이상의 값을 반환할 수 없다는 제약이다. 즉, 쿼리를 아래와 같이 작성할 수 없다. 부분범위 처리 가능하다는 스칼라 서브쿼리의 장점을 이용하고 싶을 때 고민이 생기게 마련이다.

 

SELECT c.고객번호, c.고객명
     , (SELECT AVG(거래금액)
          FROM 거래
         WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
  FROM 고객 c
 WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

 

그렇다고 쿼리를 아래와 같이 작성하면, 거래 테이블에서 같은 데이터를 반복해서 읽는 비효율이 있다.

 

SELECT c.고객번호, c.고객명
     , (SELECT MIN(거래금액)
          FROM 거래
         WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
     , (SELECT AVG(거래금액), MIN(거래금액), MAX(거래금액)
          FROM 거래
         WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
     , (SELECT MAX(거래금액)
          FROM 거래
         WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
  FROM 고객 c
 WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

 

이럴 때 SQL 튜너들이 전통적으로 많이 사용해 온 방식은 아래와 같다.

SELECT 고객번호, 고객명
     , TO_NUMBER(SUBSTR(거래금액, 1, 10)) 평균거래금액
     , TO_NUMBER(SUBSTR(거래금액, 11, 10)) 최소거래금액
     , TO_NUMBER(SUBSTR(거래금액, 21)) 최대거래금액
  FROM (SELECT C.고객번호, C.고객명
             , (SELECT LPAD(AVG(거래금액), 10 || LPAD(MIN(거래금액), 10 || LPAD(MAX(거래금액), 10
                  FROM 거래
                 WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
                   AND 고객번호 = c.고객번호) 거래금액
          FROM 고객 c
         WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)
        )

 

구하는 값들을 문자열로 모두 결합하고, 바깥쪽 액세스 쿼리에서 substr 함수로 다시 분리하는 방식이다.

 

 

 

(5)스칼라 서브쿼리 Unnesting

 

스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다. 그래서 다른 종인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환하고 싶을 때가 있다. 특히, 병렬(Parallel) 쿼리에선 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 한다. 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이기 때문이다.

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