SQL튜닝 - 스칼라서브쿼리 ( 스칼라서브쿼리 캐싱, 스칼라서브쿼리 UNNESTING)

 

쿼리블록에 내장된 또 다른 쿼리블록을 서브쿼리라고 합니다. 대표적으로 인라인 서브쿼리, 스칼라 서브쿼리, 중첩 서브쿼리가 있습니다. 그 중 스칼라서브쿼리는 함수 처럼 한 레코드당 정확히 하나의 값 만을 리턴합니다. 주로 SELECT-LIST에서 사용되지만 몇 가지 예외사항을 제외하면 칼럼이 올 수 있는 대부분의 위치에서 사용이 가능합니다.

 

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

 

위 스칼라 서브쿼리는 다음의 OUTER 조인문으로 변환할 수 있습니다. 같은 결과를 냅니다.

 

-- DEPT TABLE과 조인에 실패하는 EMP 레코드가 있따면 DEPT.DNAME은 NULL로 출력

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

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

 

스칼라 서브쿼리와 OUTER JOIN 은 결과만 같은 것이 아니라 조인을 수행하는 처리 경로가 동일합니다. 단, 스칼라서브쿼리는 내부적으로 캐싱기법이 적용됩니다.

스칼라서브쿼리의 캐싱효과

 

스칼라 서브쿼리를 사용하면 내부적으로 캐시를 생성하고 서브쿼리에 대한 입출력 값을 저장합니다. 메인쿼리로 부터 같은 입력값이 들어오면 서브쿼리를 실행하는 대신 캐시된 출력 값을 리턴합니다. 캐시에서 값을 찾지못할때만 쿼리를 수행하며, 결과를 캐싱합니다.

스칼라 서브쿼리를 캐싱할때 해싱 알고리즘을 사용합니다. 따라서 해시충돌이 적은 입력 값의 종류 칼럼을 조건절로 조회할때 캐싱효과를 얻을 수 있습니다.

 

두 개 이상의 값을 리턴하고 싶을때

 

아래는 인라인 뷰를 활용해 특정 부서의 급여수준을 파악하는 쿼리입니다.

 

SELECT D.DEPTNO, D.DNAME, AVG_SAL, MIN_SAL, MAX_SAL
FROM DEPT D RIGHT OUTER JOIN
(
  SELECT DEPTNO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL
  FROM EMP GROUP BY DEPTNO
) E
ON E.DEPTNO = D.EPTNO
WHERE D.LOC = 'CHICAGO'

 

다음과 같이 스칼라 서브쿼리를 활용한 쿼리로 바꾸면 캐싱효과를 누릴 수 있지만 서브쿼리는 원칙적으로 한 레코드당 하나의 값만을 리턴합니다.

 

SELECT D.DEPTNO, D.DNAME
,(SELECT AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL FROM EMP WHERE DEPTNO = D.DEPTNO)
FROM DEPT D
WHERE D.LOC = 'CHICAGO'

 

스칼라 서브쿼리를 다회 반복하면 원하는 결과를 얻을 수 있지만 같은 테이블의 동일한 범위를 반복적으로 읽는 비효율이 있습니다.

 

SELECT D.DEPTNO, D.DNAME
, (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AVG_SAL
, (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MIN_SAL
, (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MAX_SAL

 

다음과 같이 구하고자하는 칼럼을 하나의 값에 모두 결합하고 바깥쪽 엑세스 쿼리에서 분리하는 방법을 활용할 수 있습니다.

 

SELECT DEPTNO, DNAME
, to_number(substr(sal, 1,7)) avg_sal
, to_number(substr(sal, 8,7)) min_sal
, to_number(substr(sal, 15)) max_sal
FROM (
  SELECT D.DEPTNO, D.DNAME
  , (SELECT LPAD(AVG(SAL), 7) || LPAD(MIN(SAL), 7) || MAX(SAL)
    FROM EMP WHERE DEPTNO = D.DEPTNO
  ) SAL
  FROM DEPT D
  WHERE D.LOC = 'CHICAGO'
)

 

스칼라서브쿼리 UNNESTING

 

 

스칼라서브쿼리는 옵티마이저에 의해 UNNESTING 되면 OUTER JOIN으로 동작합니다. UNNESTING 됐기 때문에 해시조인으로도 가능합니다.

 

SELECT C.C_NO, C.C_NAME
,(SELECT /*+UNNEST*/ ROUND(AVG(T_COST),2)
FROM T
WHERE T_DATE > TRUNC(SYSDATE, 'MM')
AND C_NO = C.C_NO)
FROM C
WHERE C.REG_D >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')

-- EXECUTION PLAY
-- SELECT STATEMENT OPTIMIZER=ALL_ROWS
--  HASH JOIN(OUTER)
--    TABLE ACCESS(FULL) OF 'C'
--    VIEW OF 'SYS_VM_SSQ_1'(VIEW)
--      HASH (GROUP BY)
--        TABLE ACCESS(FULL) OF 'T'(TABLE)

 

아래는 unnest 와 merge 힌트를 같이 사용했을 때의 실행 계획입니다.

 

-- SELECT STATEMENT OPTIMIZER=ALL_ROWS
--  HASH (GROUP BY )
--    HASH JOIN(OUTER)
--      TABLE ACCESS (FULL) OF 'C'
--      TABLE ACCESS (FULL) OF 'T'