서브쿼리 조인

운구름·2022년 7월 10일

sql 서브쿼리조인 스터디 조인 튜닝

친절한 SQL 튜닝 스터디

목록 보기

16/27

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

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

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

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

서브쿼리는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록을 말한다.
쿼리에 내장된 또 다른 쿼리다.
서브쿼리를 DBMS마다 조금씩 다르게 분류하는데 오라클은 세가지로 분류한다.

인라인 뷰 : FROM 절에 사용한 서브쿼리
중첩 서브쿼리 : 결과 집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리를 말한다.
특히 서브쿼리가 메인 컬럼을 참조하는 형태를 상관관계 있는 서브쿼리라고 한다.
스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리이다.
주로 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') )