쿼리 안의 또 다른 쿼리를 서브쿼리 라고 합니다. 그중에 함수처럼 한 레코드당 하나의 값을 리턴하는 서브쿼리를 스칼라 서브쿼리라고 합니다. 주로 SELECT-LIST에서 사용되지만 몇 개의 예외상황을 제외하면 칼럼이 올 수 있는 대부분의 위치에서 사용가능합니다.
SELECT EMPNO, ENAME, SAL, HIREDATE
,(SELECT D.DNAME, FROM DEPT D WHERE D.DEPNO = E.DEPTNO) DNAME
FROM EMP E
WHERE SAL > 2000
스칼라서브쿼리 실행계획
스칼라 서브쿼리의 실행계획은 다음과 같습니다. 메인쿼리의 실행이 실행계획 아래쪽에 위치하고, 스칼라 서브쿼리 실행이 위쪽에 위치합니다.
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
-- SELECT STATMENT
-- SORT (AGGREGATE)
-- TABLE ACCESS BY INDEX ROWID OF '거래'
-- INDEX(RANGE SCAN) OF 거래_idx
-- TABLE ACCESS BY INDEX ROWID OF '고객'
-- INDEX(RANGE SCAN) OF 고객_idx
--스칼라서브쿼리를 조인문으로 변환
스칼라 서브 쿼리를 사용한 쿼리문은 서브쿼리에서 사용된 테이블과 OUTER 조인한 결과와 값이 동일합니다. 즉, 조인에 실패하는 INNER TABLE 칼럼을 NULL로 출력합니다. 아래 쿼리의 경우, DEPT TABLE과 조인에 실패하는 EMP TABLE의 레코드의 경우 DNAME을 NULL로 표현합니다.
-- ANSI
SELECT E.EMPNO, E,ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E LEFT OUTER JOIN DEPT D
ON D.DEPTNO = E.DEPTNO
WHERE E.SAL >= 2000
-- ORACLE
SELECT E.EMPNO, E,ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E, DEPT D
WHERE D.DEPTNO(+) = E.DEPTNO
AND E.SAL >= 2000
-- ANSI
SELECT /*+ordered use_nl(d)*/
E.EMPNO, E,ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E RIGHT OUTER JOIN DEPT D
ON D.DEPTNO = E.DEPTNO
WHERE E.SAL >= 2000
-- ORACLE
SELECT /*+ordered use_nl(d)*/
E.EMPNO, E,ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO (+)
AND E.SAL >= 2000
위의 쿼리는 결과만 같은 것이 아니라 조인을 수행하는 처리 경로도 동일합니다. NL 방식으로 수행되도록 힌트를 사용했기 때문입니다. 다만 스칼라 서브 쿼리는 내부적으로 캐싱기법이 작동됩니다. 스칼라서브쿼리를 조인방식으로 변환할 때, 조인 칼럼이 NOT NULL인 경우 INNER 조인과 OUTER 조인 중 어느 것을 사용해도 결과집합에는 영향이 없습니다. 그렇지만, 성능을 위해 가급적 INNER 조인을 사용하는 것이 권장됩니다. INNER 조인은 어느 쪽 방향으로든 조인이 가능하지만 OUTER 조인은 방향이 한쪽으로 고정되기 때문입니다.
조인문을 스칼라 서브쿼리로 변환
일반 조인문을 스칼라 서브쿼리로 변환할 때는 테이블 간 필수 / 옵션 관계는 신경 쓰지 않아도 됩니다. 스칼라 서브쿼리는 OUTER 조인과 같은 결과를 반환하는데 OUTER 조인은 INNER 조인 결과를 포함하기 때문입니다. 하지만 조인 카디널리티는 반드시 확인해야 합니다. 스칼라 서브쿼리는 메인 집합의 레코드당 하나의 레코드만 반환해야 하므로 집계함수를 사용하거나 ROWNUM <= 1 조건을 사용해야 합니다.
스칼라 서브쿼리의 캐싱 효과
스칼라 서브쿼리를 사용하면 내부적으로 캐시를 생성하고, 캐시에 서브쿼리에 대한 입력값과 처리결과인 출력값을 저장합니다. 메인쿼리로부터 같은 입력 값이 들어오면 서브쿼리를 실행하는 대신 캐시된 출력 값을 리턴합니다. 캐시에 입력값이 없는 경우만 쿼리를 수행하며 결과는 마찬가지로 캐시에 저장합니다.
SELECT EMPNO, ENAME, SAL, HIREDATE
,(
SELECT D.DNAME -- 출력값
FROM DEPT D
WHERE D.DEPTNO = E.EMPNO -- 입력 값 : e.empno
)
from emp e
where sal >= 2000
캐싱을 위해 해싱 알고리즘이 사용됩니다. 입력 값의 종류가 적어 해시 충돌 가능성이 낮은 경우에 캐싱효과를 볼 수 있습니다. 반대의 경우, 캐시를 확인하는 비용이 높기 때문에 성능은 저하되고 CPU 사용률만 증가합니다.
두 개 이상의 값을 리턴하려는 경우
스칼라 서브쿼리는 한 레코드당 하나의 값만을 리터할 수 있습니다.
SELECT D.DEPTNO, D.DNAME, E.AVG_SAL, E.MIN_SAL, E.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.DEPTNO
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
FROM DEPT D
WHERE D.LOC = 'CHICAGO'
대안으로 스칼라 서브쿼리에서 구하고자하는 칼럼을 모두 결합하고, 바깥쪽 액세스 쿼리에서 SUBSTR로 분리하는 방법이 주로 사용됩니다.
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이라 합니다. 다음은 스칼라 서브쿼리를 UNNESTING 할 때의 실행계획입니다.
SELECT
, (
SELECT 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'
조인조건 push down
오라클 11g 부터는 조인조건 pushdown 힌트를 통해 쿼리 변환을 유도해 group by 없이 인라인 뷰를 처리할 수 있습니다. 메인 쿼리를 실행하면서 조인조건절 값을 뷰 안으로 건건이 밀어 넣습니다 VIEW PUSHED PREDICATE OPERATION을 통해 작동여부를 확인할 수 있습니다.
-- 인덱스 구성
-- 고객_pk : 고객번호
-- 고객_x1 : 가입일시
-- 거래_pk : 거래번호
-- 거래_x1 : 거래일시
-- 거래_x2 : 고객번호 + 거래일시
select /*+ordered use_nl(t)*/
C.고객번호, C.고객명, T.평균거래, T.최소거래, T.최대거래
FROM 고객 c
, (
SELECT /*+ no_merge push_pred*/
고객번호
, avg(거래금액) 평균거래
, min(거래금액) 최소거래
, max(거래금액) 최대거리
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
group by 고객번호
) t
where C.가입일시 > trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
-- EXECUTION PLAN
-- SELECT STATEMENT
-- NESTED LOOP
-- TABLE ACCESS BY 고객 TABLE
-- INDEX RANGE SCAN OF 고객_X1
-- VIEW PUSHED PREDICATE
-- SORT (GROUP BY)
-- TABLE ACCESS(BY INDEX ROWID BATCHED) OF 거래 TABLE
-- INDEX RANGE SCAN OF 거래_x2뷰 머징
옵티마이저는 쿼리를 블록 단위로 최적화합니다. 쿼리에서 view를 변환하지 않고 그대로 두면, view 쿼리 블록을 독립적으로 최적화합니다. view 바깥에 있는 조건을 활용해 view 내부 쿼리의 스캔범위를 줄이기 위해 옵티마이저는 뷰를 메인 쿼리와 머징할 수 있습니다. 머징된 뷰는 메인테이블과 조인이 가능합니다. 뷰 내부에 group by 가 있는 경우, 머징 후 group by를 해야 하기 때문에 부분처리가 불가능하게 됩니다. 부분처리가 불가능한 상황에서는 hash 조인으로 메인쿼리와 머징된 뷰를 조인하는 것이 유리합니다.
스칼라 서브쿼리를 활용해 GROUP BY를 포함한 뷰를 부분범위 처리
아래의 쿼리는 인라인 view 내부의 GROUP BY OPERATION으로 인해 부분범위처리가 불가능한 구조입니다.
select
C.고객번호, C.고객명, T.평균거래, T.최소거래, T.최대거래
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.고객번호
스칼라 서브쿼리를 이용해 아래와 같이 변경하면 group by operation을 사용하지 않아도 되므로 부분범위 처리가 가능해집니다.
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)
|| max(거래금액) 평균거래, 10
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = C.고객번호
)
FROM 고객 c
where C.가입일시 > trunc(add_months(sysdate, -1), 'mm')
)
