서브쿼리 조인

운구름·2022년 7월 10일
0
post-thumbnail
post-custom-banner

💡 옵티마이저는 서브쿼리에 대해 다양한 형태로 쿼리 변환을 시도해서 쿼리 변환을 알아야 한다.

서브쿼리 변환이 필요한 이유

옵티마이저는 제일 먼저 사용자로부터 받은 SQL을 최적화에 유리한 형태로 변환작업을 한다 ⇒ 쿼리변환 진행

쿼리변환은 옵티마이저가 SQL을 분석하여 의미적으로 동일하지만 더 나은 성능이 기대되는 형태로 재작성 하는 것을 말한다.

  • 서브쿼리는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록을 말한다.
  • 쿼리에 내장된 또 다른 쿼리다.
  • 서브쿼리를 DBMS마다 조금씩 다르게 분류하는데 오라클은 세가지로 분류한다.
  1. 인라인 뷰 : FROM 절에 사용한 서브쿼리
  2. 중첩 서브쿼리 : 결과 집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리를 말한다.
    특히 서브쿼리가 메인 컬럼을 참조하는 형태를 상관관계 있는 서브쿼리라고 한다.
  3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리이다.
    주로 SELECT-LIST에서 사용하지만 몇가지 예외상황을 제외하면 컬럼이 올수 있는 대부분 위치에서 사용할 수 있다.

예시1

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'))

예시2

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)
  • 필터 오퍼레이션은 NL 조인과 처리 루틴이 같다.

차이

  • 조인에 성공하는 순간(exist) 진행을 멈추고 다음 로우 진행
  • 필터는 캐싱기능을 가짐.
    • 서브쿼리 입력값에 따른 반환값을 캐싱하는 기능
    • 필터가 각 서브쿼리 수행에 대해서 캐싱
    • 캐싱은 쿼리단위로 이루어짐
    • 쿼리를 시작할 때 PGA 메모리에 공간 할당, 쿼리 수행하면서 채우고 다 쓰면 반환.
  • 필터 서브쿼리는 일반 NL 조인과 달리 메인 쿼리에 종속되므로 조인 순서가 고정된다.
    • 메인 쿼리가 드라이빙 집합

서브쿼리 Unnesting

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 조인과 같은 프로세스이나, 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점만 다름. (필터 오퍼레이션 기능이기도 함)

  • unnest하면 서브쿼리는 NL 세미조인 외 다양한 방식으로 실행될 수 있다.
  • Unnesting 된 서브쿼리는 메인 쿼리보다 먼저 처리될 수 있다.

leading 힌트

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 해서 메인쿼리와 같은 레벨로 만들면 할수 있는 일

  • 다양한 조인 메소드 선택가능
  • 조인 순서 지정 사용

서브쿼리 Pushing

필터방식에서 서브쿼리는 순서가 정해지고 맨 마지막 단계에 처리된다.

하지만, push_subq 힌트를 사용하여 서브쿼리 필터링을 먼저 처리하게 해서 처리량을 줄일 수 있다.

push는 필터링 상태에서만 적용이 가능하다.

  • Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞단계에서 처리하도록 강제하는 기능
  • Unnesting 되지 않은 서브쿼리에서만 작동
  • push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법

뷰와 조인

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

전월 이후 가입 고객 필터 조건이 뷰 바깥에 있기 때문에 뷰 안에서는 전체 고객에 대한 데이터를 읽어야 한다.

merge 힌트

merge 힌트를 사용해서 메인쿼리와 뷰를 머징한다. 머징하지 않을땐 no_merge

merging을 했을때 단점

  • Group By 하고 데이터를 출력할 수 있다.
  • 부분범위 처리가 불가능하다.

조인조건 PushDown

11g 버전 이후 사용가능.

메인쿼리를 실행하면 조인조건절 값을 건건이 뷰 안으로 밀어넣는 기능.

profile
뭉실뭉실 코더 운구름
post-custom-banner

0개의 댓글