💡 옵티마이저는 서브쿼리에 대해 다양한 형태로 쿼리 변환을 시도해서 쿼리 변환을 알아야 한다.
옵티마이저는 제일 먼저 사용자로부터 받은 SQL을 최적화에 유리한 형태로 변환작업을 한다 ⇒ 쿼리변환 진행
쿼리변환은 옵티마이저가 SQL을 분석하여 의미적으로 동일하지만 더 나은 성능이 기대되는 형태로 재작성 하는 것을 말한다.
SELECT c.고객번호, c.고객명
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), 'mm')
AND EXISTS (SELECT /*+ no_unnest */
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= TRUNC(SYSDATE, 'mm'))
SELECT c.고객번호, c.고객명
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), 'mm')
select 'x'
from 거래
where 고객번호 = :cust_no
and 거래일시 >= TRUNC(SYSDATE, 'mm'))
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c, (
**select 고객번호,
avg(거래금액) 평균거래,
min(거래금액) 최소거래,
max(거래금액) 최대거래
from 거래
where 거래일시 >= turnc(sysdate, 'mm')
group by 고객번호**
) t
where c.가입일시>= turnc(add_months(sysdate, -1), 'mm')
and t.고객번호= c.고객번호
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c, SYS_VW_TEMP t
where c.가입일시>= turnc(add_months(sysdate, -1), 'mm')
and t.고객번호= c.고객번호
select 고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= turnc(sysdate, 'mm')
group by 고객번호
메인쿼리와 서브쿼리간에는 부모와 자식같은 종속적이고 계층적인 관계가 형성된다.
서브가 메인에 종속되기 때문에 단독으로 실행할 수 없고 메인에서 값을 받아서 반복적으로 필터링하는 방식을 사용한다.
서브쿼리를 필터방식으로 처리하려면 no_unnset 힌트를 사용 (unnest(풀어내지) 말고 그대로 수행하라는 뜻)
select c.고객번호, c.고객명,
from 고객 c
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ no_unnest */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm')
)
Execution Plan ------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=289 Card=1 Bytes=39)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=4 Card=190 ... )
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=190)
4 1 INDEX (RANGE SCAN) OF '거래_X01' (INDEX) (Cost=3 Card=4K Bytes=92K)
Unnesting 하려면 unnest 힌트 사용
select c.고객번호, c.고객명
from 고객 c
where c.기입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ unnest nl_sj */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(Sysdate, 'mm') )
nest의 사전적 의미 : 차곡차곡 포개넣다. ⇒ 중첩
nl_sj (NL 세미조인) : 기본적으로 NL 조인과 같은 프로세스이나, 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점만 다름. (필터 오퍼레이션 기능이기도 함)
select /*+ leading(거래@subq) use_nl(c) */ c.고객번호, c.고객명
from 고객 c
where c.기입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ qb_name(subq) unnest */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
서브쿼리 집합에서 고객번호 중복을 제거하기 위해 쿼리를 아래처럼 변환
select /*+ no_merge(t) leading(t) use_nl(c) */ c.고객번호, c.고객명
from (
select distinct 고객번호
from 거래
where 거래일시 trunc(sysdate, 'mm')) t, 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and c.고객번호 = t.고객번호
힌트는 unnsest hash_sj
select c.고객번호, c.고객명
from 고객 c
where c.기입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ unnsest hash_sj */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
서브쿼리를 Unnesting 해서 메인쿼리와 같은 레벨로 만들면 할수 있는 일
필터방식에서 서브쿼리는 순서가 정해지고 맨 마지막 단계에 처리된다.
하지만, push_subq 힌트를 사용하여 서브쿼리 필터링을 먼저 처리하게 해서 처리량을 줄일 수 있다.
push는 필터링 상태에서만 적용이 가능하다.
최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
전월 이후 가입 고객 필터 조건이 뷰 바깥에 있기 때문에 뷰 안에서는 전체 고객에 대한 데이터를 읽어야 한다.
merge 힌트를 사용해서 메인쿼리와 뷰를 머징한다. 머징하지 않을땐 no_merge
merging을 했을때 단점
11g 버전 이후 사용가능.
메인쿼리를 실행하면 조인조건절 값을 건건이 뷰 안으로 밀어넣는 기능.