서브쿼리 조인

4.4.3 뷰와 조인

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

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.고객번호

• 단점
  • 고객 테이블에서 '전월 이후 가입한 고객'을 필터링하는 조건이 인라인 뷰 바깥에 있다.
  • 그래서 인라인 뷰 안에서는 당월에 거래한 '모든'고객의 거래 데이터를 읽어야 한다.

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
    ,(select /*+ merge */ 고객번호, avg(거래금액) 평균거래
             , min(거래금액) 최소거래, max(거래금액) 최대거래
      from 거래
      where 거래일시 >= trunc(sysdate, 'mm')
      group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호

• merge힌트를 이용해 뷰를 메인 쿼리와 머징 하도록 했다.

select c.고객번호, c.고객명
    , avg(t.거래금액) 평균거래, min(t.거래금액) 최소거래, max(t.거래금액) 최대거래
from 고객 c, 거래 t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
and t.거래일시 >= trunc(sysdate, 'mm')
group by c.고객번호, c.고객명

• 실행계획을 보면 쿼리가 위와 같이 변환되었다.
• 가장 먼저 액세스하는 고객_X01 인덱스는 가입일시가 선두 컬럼이다. 인덱스를 Range Scan했다.
• 거래_X02인덱스는 고객번호 + 거래일시로 구성되었다.
• 고객테이블을 먼저 읽고, 거래 테이블과 조인할 때는 해당 고객들에 대한 당월 거래만 읽는다. 거래 테이블을 NL조인하기 때문이다.
• 단점
  • 조인에 성공한 전체 집합을 group by 하고서야 데이터를 출력할 수 있다.
  • 부분범위 처리가 불가능하다.
  • 만약 전월 이후 가입한 고객과 당월 거래가 매우 많다면, NL조인은 좋은 선택이 아니다.

조인 조건 Pushdown

11g 이후로 '조인 조건 Pushdown'이라는 쿼리 변환 기능이 작동한다. 이것은 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.

select 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.고객번호

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
    ,(select 고객번호, avg(거래금액) 평균거래
             , min(거래금액) 최소거래, max(거래금액) 최대거래
      from 거래
      where 거래일시 >= trunc(sysdate, 'mm')
      and 고객번호 = c.고객번호
      group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호

위 쿼리처럼 옵티마이저가 변환시켜서 최적화했다.

• 전월 이후 가입한 고객을 대상으로 건건이 당월 거래 데이터만 읽어서 조인하고 group by를 수행
• 부분범위 처리 가능

4.4.4 스칼라 서브쿼리 조인

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

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
    when others then
    return null;
end;

위와 같이 GET_DNAME함수를 만들고

select empno, ename, sal, hiredate
    , GET_DNAME(e.deptno) as dname
from emp e
where sal >= 2000

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

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

위 스칼라 서브쿼리는 메인쿼리 레코드마다 정확히 하나의 값만 반환한다. 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행한다.

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

위 outer 조인문처럼 NL 조인 방식으로 실행된다. DEPT와 조인에 실패하는 EMP레코드는 DNAME에 NULL값을 출력하는 것도 같다. 차이가 있다면 스칼라 서브쿼리는 처리 과정에 캐싱 작용이 일어난다.

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

스칼라 서브쿼리로 조인하면 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시에 저장한다. 메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있다.

SELECT-LIST에 사용한 함수는 메인쿼리 결과 건수만큼 반복 수행되는데, 아래와 같이 스칼라 서브쿼리를 덧씌우면 호출 횟수를 최소화할 수 있다.

select empno, ename, sal, hiredate
    ,(select GET_DNAME(e.deptno) from dual) dname
from emp e
where sal >= 2000

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

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

select 계좌번호, 계좌명, 고객번호, 개설일자, 계좌종류구분코드, 은행계설여부, 은행연계여부
    ,(select brch_nm(관리지점코드) from dual) 관리지점명
    ,(select brch_nm(개설지점코드) from dual) 개설지점명
from 계좌
where 고객번호 = :고객번호

위 쿼리는 메인집합이 낮기 때문에, 즉 고객당 계좌가 보통 한 개일 것이기 때문에 쿼리단위로 캐싱하는 특성상 재사용성이 낮아 오히려 성능을 떨어뜨린다.

(4) 두 개 이상의 값 반환

스칼라 서브쿼리에는 두 개 이상의 값을 반환할 수 없다는 치명적인 제약이 있다.

select c.고객번호, c.고객명
    ,(select avg(거래금액), min(거래금액), 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) || max(거래금액)
          from 거래
          where 거래일시 >= trunc(sysdate, 'mm')
          and 고객번호 = c.고객번호) 거래금액
    from 고객 c
    where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
    )

(5) 스칼라 서브쿼리 Unnesting

스칼라 서브쿼리도 NL방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다. 그래서 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환하고 싶을 때가 있다.
오라클 12c부터는 스칼라 서브쿼리도 Unnesting이 가능해졌다. 옵티마이저가 사용자 대신 자동으로 쿼리를 변환해 주는 것이다.

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

위는 스칼라 서브쿼리를 Unnesting할 때의 쿼리문이다. 스칼라 서브쿼리인데도 NL조인이 아닌 해시 조인으로 실행될 수 있는 이유는 Unnesting 되었기 때문이다.