<오라클 성능 고도화 원리와 해법2> Ch02-06 스칼라 서브쿼리를 이용한 조인
오라클 성능 고도화 원리와 해법2 - Ch02-06 스칼라 서브쿼리를 이용한 조인
(1) 스칼라 서브쿼리
쿼리에 내장된 또 다른 쿼리 블록을 서브쿼리라고 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값을 리턴하는 서브쿼리를 ‘스칼라 서브쿼리’라고 한다. 스칼라 서브쿼리는 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 컬럼이 올 수 있는 대부분 위치에서 사용 가능하다.
1
2
3
4
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 값이 출력된다.
1
2
3
4
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 방식으로 수행되도록 힌트를 사용했기 때문이다. 다만 스칼라 서브쿼리에는 내부적으로 캐싱 기법이 작용한다는 점이 다르고, 이를 이용한 튜닝이 자주 행해진다.
(2) 스칼라 서브쿼리의 캐싱 효과
오라클은 스칼라 서브쿼리 실행 횟수를 최소화하려고 입력 값과 출력 값의 내부 캐시(Query Execution Cache)에 저장해둔다. 스칼라 서브쿼리가 수행될 때면 일단 ‘입력 값’을 캐시에서 찾아보고 거기에 있으면 저장된 ‘출력 값’을 리턴한다. 캐시에서 찾지 못할 때만 쿼리를 수행하며, 결과는 버리지 않고 캐시에 저장해둔다.
스칼라 서브쿼리에 있어 입력 값은, 거기서 참조하는 메인 쿼리의 컬럼 값을 말한다.
1
2
3
4
5
6
7
8
select empno, ename, sal, hiredate
,(
select d.dname. → 출력값 : d.dname
from dept d
where d.deptno = e.deptno → 입력값 : e.empno
) dname
from emp e
where sal >= 2000
반복 수행되는 함수 때문에 쿼리 성능이 크게 저하될 때, 아래와 같이 함수에 스칼라 서브쿼리를 덧씌움으로써 호출 횟수를 줄이는 튜닝 사례를 1권 5장 8절에서 자세히 설명하였다.
1
2
3
4
select empno, ename, sal, hiredate
,(select get aname (deptno) from dual) dname
from emp e
where sal >= 2000
입력 값과 출력 값을 빠르게 저장하고 찾기 위해 오라클은 해싱 알고리즘을 사용한다. 필자가 번역한 ‘비용 기반의 오라클 원리(공역, 200g)’ 저자인 조나단 루이스 설명에 의하면 81, 9i에서는 256개 엔트리를 캐싱하고, 10g에서는 입력과 출력 값 크기, query_execution_cache의 mar size 파라미터에 의해 캐시 사이즈가 결정된다고 한다.
그리고 해시 충돌이 발생했을 때 기존 엔트리를 밀어내고 새로 수행한 입력 값과 출력 값을 대체할 것 같지만, 오라클은 기존 캐시 엔트리를 그대로 둔 채 스칼라 서브쿼리만 한 번 더 수행하고 만다. 따라서 해시 충돌이 발생한 입력 값이 반복적으로 입력되면 스칼라 서브쿼리도 반복 수행된다.
결론적으로, 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적은 때라야 효과가 있으며, 반대의 경우라면 캐시를 확인하는 비용 때문에 오히려 성능은 저하되고 CPU 사용률만 높게 만든다. 게다가 스칼라 서브쿼리를 사용하면 NL 조인에서 Inner 쪽 인덱스와 테이블에 나타나는 버퍼 Pinning 효과도 사라진다는 사실을 기억할 필요가 있다.
(3) 두 개 이상의 값이 리턴되어야 할 때
아래 쿼리는 위치가 ‘CHICAGO’인 부서(dept)만 대상으로 급여 수준을 집계하려는 것인데, 사원(emp) 테이블 전체를 다 읽어야 하는 비효율이 있다(스크립트 ch2.09.kt 참조).
- 11g에서 group by 절을 포함한 뷰에 대한 조인 조건(Join Predicate) Pushdown이 가능해졌고, 이에 대한 자세한 설명은 4장 4절을 참조하기 바란다.
1
2
3
4
5
6
select d.deptno, d.dname, avg sal, min_sal, max sal
from dept d
,(select deptno, avg (sal) avg sal, min (sal) min sal, max (sal) max sal
from emp group by deptno) e
where e. deptno (+) = d.deptno
and d.loc = 'CHICAGO'
아래와 같이 바꿀 수 있으면 좋겠지만 스칼라 서브쿼리는 한 레코드당 하나의 값을 리턴한다는 특성 때문에 그럴 수가 없다.
1
2
3
4
select d.deptno, d.aname
,(select avg (sal), min (sal), max (sal) from emp where deptno = d.deptno)
from dept d
where d.loc = 'CHICAGO'
그렇다고 아래와 같이 쿼리한다면 emp에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.
1
2
3
4
5
6
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 함수로 분리하는 방법을 생각해볼 수 있다. (※ 10.2.0.4 버전에서 쿼리를 아래와 같이 작성하면 실행 계획상 스칼라 서브쿼리가 세 번 수행되는 것처럼 보이지만 실제 SQL 트레이스로 확인해 보면 한 번만 수행되는 것을 알 수 있다.)
1
2
3
4
5
6
7
8
9
10
11
select deptno, aname
, 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'
)
두 번째는 오브젝트 TYPE을 사용하는 방식인데, TYPE을 미리 선언해두어야 하는 불편함이 있지만 SQL은 훨씬 깔끔해진다.
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace type saltype as object
(avg_sal number, min_sal number, max_sal number )
/
select deptno, aname
, a.sal.avg_sal, a.sal.min_sal, a.sal.max_sal
from (
select d.deptno, d.dname
,(select saltype(avg(sal), min(sal), max(sal)) from emp where deptno = d.deptno) sal
from dept d
where d.loc = 'CHICAGO'
) a
/