스칼라 서브쿼리

쿼리에 내장된 또 다른 쿼리 블록을 서브 쿼리라고 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값만 리턴하는 서브쿼리를 스칼라 서브 쿼리라고 한다. 주고 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 조인문과 100% 같은 결과를 낸다. 

즉 dept 와 조인에 실패하는 emp 레코드가 있다면 dname 으로 null 값이 출력된다.

select /*+ordered use_nl(d)*/ e.empno, e.ename, e.sal, e.hiredate, e.dname
from emp e right outer join dept d
on d.deptno = e.deptno
where e.sal >= 2000

위에서 예시한 쿼리는 결과만 같은 것이 아니라 조인을 수행하는 처리 결로도 동일하다.

NL 방식으로 수행되도록 힌트를 사용했기 때문이다. 다만 스칼라 서브 쿼리에는 내부적으로 캐싱 기법이 작용된다는 점이 다르고, 이를 이용한 튜닝이 자주 행해진다.   

 

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

스칼라 서브 쿼리를 사용하면 내부적으로 캐시를 생성하고, 여기에 서브쿼리에 대한 입력 값과 출력 값을 저장한다.

메인 쿼리로부터 같은 입력 값이 들어오면 서브쿼리를 실행하는 대신 캐시된 출력 값을 리턴한다. 캐시에서 찾지 못할 때만 쿼리를 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.

select empno, ename, sal, hiredate
, (select d.dname from dept d where d.deptno=e.empno) 
from emp e
where sal >= 2000;

스칼라 서브 쿼리는 수행할 때, 입력 값과 출력 값을 빠르게 저장하고 찾기 위해 일반적으로 해싱 알고리즘이 사용된다.  따라서 입력 값의 종류가 소수여서 해시 충돌 가능성이 적은 때라야 캐싱 효과를 얻을 수 있다. 반대의 경우라면 캐시를 확인하는 비용 때문에 오히려 성능은 저하되고 CPU 사용률만 높게 만든다.

 

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

다음 쿼리는 위치가 'CHICAGO'인 부서만 대상으로 급여 수준을 집계하려는 것이다. 사원테이블 전체를 다 읽어야 하는 비효율이 있다. (오라클 11g 이후로 다음에 다룰 '조인 조건 Pushdown 기능이 잘 작동한다면 loc='Chicago'조건에 해당하는 부서와 관련된 사원 테이블만 읽도록 할 수 있다.')

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.deptno
where d.loc='CHICAGO';

다음과 같이 바꿀 수 있으면 좋겠지만 스칼라 서브 쿼리는 한 레코드당 하나의 값만 리턴한다는 특징때문에 그럴 수가 없다.

select d.deptno, d.dname
,(select avg(sal), min(sal), max(sal) from emp where deptno=d.deptno)
from dept d 
where d.loc='CHICAGO'

그렇다고 다음과 같이 쿼리한다면 emp 에서 같은 범위를 반복적으로 액세스하는 비효율이 따른다.

 

select d.deptno, d.dname
,(select avg(sal) from emp where deptno=d.deptno)
,(select min(sal) from emp where deptno=d.deptno)
,(select max(sal) from emp where deptno=d.deptno)
from dept d 
where d.loc='CHICAGO'

이럴 때 다음과 같이 구하고자 하는 값들을 모두 결합하고, 바깥쪽 액세스 쿼리에서 substr 함수로 분리하는 방법이 유용하게 쓰인다. (오라클 조인조건 pushdown 확인 )

 

select deptno, dname
, to_number(substr(sal,1,7)) as avg_sal
, to_number(substr(sal,8,7)) as min_sal
, to_number(substr(sal,15)) as 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'
    )

 

3. 스칼라 서브 쿼리 Unnesting 

오라클 12c 부터 스칼라 서브 쿼리도 Unnesting 이 가능해졌다. 옵티마이저가 사용자 대신 자동으로 쿼리를 변환해 주는 것이다. 다음은 스칼라 서브 쿼리를 Unnesting 할 때의 실행계획이다. 스칼라 서브 쿼리인데도 

NL 조인이 아닌 해시 조인으로 실행될 수 있는 이유는 Unnesting 됐기 때문이다.

select c.고객번호, c.고객명
,(select /*+unnest*/ round(avg(거래금액),2) 평균거래금액
from 거래 
where 거래일시 >= trunc(sysdate,'mm')
and 고객번호 = c.고객번호)
from 고객 x
where c.가입일시 >= trunc(add_months(sysdate,-1),'mm')

------------------------------------
Execution Plan
------------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=15 Bytes=405)
1    0     HASH (GROUP BY) (Cost=7 Card=15 Bytes=405)
2    1       HASH JOIN (OUTER) (Cost=6 Card=15 Bytes=405)
3    2         TABLE ACCESS(FULL) OF '고객' (TABLE) (Cost=3 Card=4 Bytes=80)
4    2         TABLE ACCESS(FULL) OF '거래' (TABLE) (Cost=3 Card=14 Bytes=98)

---

출처

SQL 전문가 가이드 - 한국데이터산업진흥원