4.4 서브쿼리 조인
4.4.1 서브쿼리 변환 필요 이유
옵티마이저는 비용(cost)을 평가하고 실행계획을 생성하기 전 전달받은 SQL을 최작화에 유리한 형태로 변환 작업한다. 쿼리 변환은 더 나은 성능이 기대되는 형태로 재 작성하는 것.
서브쿼리의 종류
- 인라인 뷰 : FROM 절에 사용한 서브쿼리
- 중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리
- 스칼라 서브쿼리 : 한 레코드당 하나의 값을 반환하는 서브쿼리. 주로 SELECT-LIST에 사용.
4.4.2 서브쿼리와 조인
메인쿼리와 서브쿼리 간에는 부모와 자식인 종속적이고 계층적인 관계다.
메인쿼리에 종속되므로 메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행
필터 오퍼레이션
no_unnest : 서브쿼리를 풀어내지말고 그대로 수행 지시
select ..
from ..
where
exists (
select /*+ no_unnest */
...
)
----------------------------------
0 SELECT STATEMENT ....
1 0 FILTER
2 1 TABLE ACCESS ...
필터 오퍼레이션은 NL조인과 처리 루틴이 같음. 실행계획에 FILTER를 ‘NESTED LOOPS’로 치환하고 처리 루틴을 해석하면됨. NL조인처럼 부분범위 처리가 가능하다.
NL조인과 다른점은
- 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고 메인쿼리의 다음 로우를 계속 처리한다.
- 필터는 캐싱기능을 갖는다. 서브쿼리를 수행하시전에 항상 캐시부터 확인한다.
- 필터 서브쿼리는 일반 NL조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정.
서브쿼리 Unnesting
select ...
from ..
where ..
and exists (
select /*+ unnest nl_sj */
)
‘nest’는 중첩을 의미한다. ‘unnest’는 중첩된 상태를 풀어내라는 뜻이 된다. 서브쿼리 Unnesting은 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어준다는 의미해서 ‘서브쿼리 Flatting’이라 함.
서브쿼리를 그대로 두면 FILTER 방식으로 사용할 수 밖에 없지만, Unnesting 하고나면 조인문 처럼 최적화가 가능하다.
unnest와 nl_sj 힌트를 같이 사용하는 경우 기본적으로 NL 조인과 같은 프로세스이다.
Unnesting을 하는 이유는 필터방식은 항상 메인쿼리가 드라이빙 집합이지만, Unnesting된 서브쿼리는 메인쿼리보다 먼저 처리가 될 수 있다.
Unnesting하고 세미 조인방식으로 실행되도록 hash_sj 힌트도 사용가능
select ...
from ...
where ...
and exists (
select /*+ unnest hash_sj */
)
0 ..
1 0 FILTER
2 1 HASH JOIN(SEMI)
3 2 TABLE ACCESS ..
...
서브쿼리를 Unnesting 해서 메인쿼리와 같은 레벨로 만들면 다양한 조인쿼리와 조인 순서 정할 수 있다.
서브쿼리 Pushing
Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리된다.
PUSH_SUBQ : 서브 쿼리 필터를 먼저 처리하는 힌트
select ..
from ..
where ..
and exists (
select /*+ no_unnest push_sebq */
..
)
💡 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능,
이 기능은 Unnesting 되지 않은 서브쿼리에만 작동,
push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 옳바른 방법이다.
4.4.3 뷰와 조인
최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 독립적으로 최적화 함.
select c.고객번호, c.고객명, t.평균거래, t.최소거래
from 고객 c
,(select 고객번호, avg(거래금액) 평균거래, ...
from 거래
where 거래일시 >= trunc(sysdate, 'mm') -- 당월 가입 고객
group by 고객번호) t
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호
‘전월 이후 가입한 고객’ 조건이 인라인 뷰 바깥에 있어 모든 거래 데이터를 읽어야 한다.
merge 힌트를 이용해 뷰를 메인쿼리와 머징하도록 가능.
뷰 머징을 방지하고자 할 때는 no_merge 힌트 사용
select c.고객번호, c.고객명, t.평균거래, t.최소거래
from 고객 c
,(select /*+ merge */ 고객번호, avg(거래금액) 평균거래, ...
from 거래
where 거래일시 >= trunc(sysdate, 'mm') -- 당월 가입 고객
group by 고객번호) t
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호
실행계획 쿼리
select ..
from 고객 c, 거래 t
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호
and t거래일시 >= trunc(sysdate, 'mm') -- 당월 가입 고객
group by c.고객번호, c.고객명
group by 하고 나서야 데이터를 출력을 할 수 있다. (부분범위 처리 불가) 이런 상황에서는 NL 조인은 좋은 선택이 아니다. 해시 조인이 빠름.
조인 조건 Pushdown
11g 이후로 Pushdown 쿼리 변환 기능이 작동 가능하다.
메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능.
select c.고객번호, c.고객명, t.평균거래, t.최소거래
from 고객 c
,(select /*+ no_merge pushdown */ 고객번호, avg(거래금액) 평균거래, ...
from 거래
where 거래일시 >= trunc(sysdate, 'mm') -- 당월 가입 고객
group by 고객번호) t
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호
....
...
...
1 0 NESTED LOOP ...
2 1 TABLE ACCESS (..고객..)
3 2 INDEX (RANGE SCAN) OF 고객_X01....
4 1 VIEW PUSHED PREDICATE..
5 4 SORT (GROUP BY) ..
6 5 TABLE ACCESS...
7 6 INDEX...(거래_X02)..
변환된 쿼리
select c.고객번호, c.고객명, t.평균거래, t.최소거래
from 고객 c
,(select /*+ no_merge pushdown */ 고객번호, avg(거래금액) 평균거래, ...
from 거래
where 거래일시 >= trunc(sysdate, 'mm') -- 당월 가입 고객
and 고객번호 = c.고객번호
group by 고객번호) t
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
Pushdown 힌트를 사용하면 ‘건건이’ 데이터만 읽어서 조인하고 GROUP BY를 수행한다.
부분범위처리 가능하다. no_merge 힌트와 함께 사용해야함.
4.4.4 스칼라 서브쿼리 조인
스칼라 서브쿼리는 처리 과정에서 캐싱 작용이 일어난다.
스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하고 입력, 출력 값을 내부 캐시에 저장해 둔다.
select ...
,(
select d.name -- 출력 값 : d.name
from dept d
where d.deptno = e.deptno -- 입력 값 : e.deptno
)
from emp e
where sal > 2000
쿼리를 시작할 때 PGA 메모리에 공간을 할당하고,
쿼리를 수행하면서 공간을 채워나가고,
쿼리를 마치는 순간 공간을 반환한다.
스칼라 쿼리 부작용
스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과 있음.
반대의 경우 CPU 사용률과 메모리 사용만 높아진다.
select 거래번호 .....
, (select 고객명 from 고객 where 고개번호 = t.고객번호) 고객명
from 거래 t
where 거래일자 .....
고객이 100만 명일 경우 캐시에 도저히 담을 수 없을 만큼 많은 고객 번호가 존재한다.
그렇다면 메인쿼리에서 50,000개 거래를 읽는 동안 캐시를 매번 탐색하지만, 대부분 데이터를 찾지 못해 조인을 해야한다.
