(1) 서브쿼리의 분류
서브쿼리는 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록을 말한다.
- 인라인 뷰: from 절에 나타나는 서브쿼리
- 중첩된 서브쿼리: 결과집합을 한정하기 위해 where 절에 사용된 서브쿼리
- 스칼라 서브쿼리: 한 레코드당 정확히 하나의 컬럼 값만을 리턴 (주로 select 절)
옵티마이저는 쿼리 블록 단위로 최적화를 수행하는데, 쿼리 전체의 최적화를 위해 먼저 서브쿼리를 풀어내야 한다.
서브쿼리를 풀어내는 두 가지 쿼리 변환 중 '서브쿼리 unnesting'은 중첩된 서브쿼리와 관련 있고, '뷰 Merging'은 인라인 뷰와 관련 있다.
(2) 서브쿼리 Unnesting의 의미
'nest' : '상자 등을 차곡차곡 포개넣다' = '중첩'
따라서 서브쿼리 Unnesting의 의미는 중첩된 서브쿼리를 풀어내는 것을 말한다.
-
동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화 한다. 이를 '서브쿼리 Unnesting' 이라고 한다.
-
서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다.
메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타난다.
(3) 서브쿼리 Unnesting의 이점
서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
-> 더 나은 실행계획을 찾을 가능성이 높아진다.
관련 힌트
- unnest: 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
- no_unnest: 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.
(4) 서브쿼리 Unnesting 기본 예시
select * from emp
where deptno in (select /*+ no_unnest */ deptno from dept)
SELECT STATEMENT
FILTER
TABLE ACCESS FULL EMP
INDEX UNIQUE SCAN DEPT_PK
Unnesting 하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행한다.
(5) Unnesting된 쿼리의 조인 순서 조정
Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다. (옵티마이저의 판단)
메인 쿼리 집합 먼저 드라이빙
select /*+ leading(emp) */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)서브쿼리 쪽 먼저 드라이빙
select /*+ ordered */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)10g부터는 쿼리 블록마다 이름을 지정할 수 있는 qb_name 힌트가 제공된다.
select /*+ leading(dept@qb1) */ * from emp
where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)(6) 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때
만약 서브쿼리 쪽 테이블이 조인 컬럼에 PK/Unique 제약 또는 Unique 인덱스가 없다면, 일반 조인문처럼 처리했을 때 어떻게 될까?
select * from dept
where deptno in (select deptno from emp)
해당 서브쿼리의 emp 테이블의 deptno 컬럼에는 Unique 인덱스가 없다.
->
select *
from (select deptno from emp) a, dept b
where b.deptno = a.deptno
위 쿼리는 M쪽 집합인 emp 테이블 단위의 결과집합이 만들어지므로 결과에 오류가 생긴다. select * from emp
where deptno in (select deptno from dept)
위 쿼리는 M쪽 집합을 드라이빙해 1쪽 집합을 필터링 하도록 작성되었으므로 조인문으로 바꾸더라도 결과에 오류가 생기지 않는다. but 제약조건이나 Unique 인덱스가 없으면 옵티마이저가 테이블 간의 관계를 모르기 때문에 일반 조인문으로 쿼리 변환을 하지 않는다.
이럴 때 옵티마이저는 두 가지 방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합이 먼저 드라이빙 되느냐에 따라 달라진다.
- 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 먼저 드라이빙 된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
- 메인 쿼리 쪽 테이블이 드라이빙 된다면 세미 조인 방식으로 조인한다.
Sort Unique 오퍼레이션 수행
select * from emp
where deptno in (select deptno from dept) ;
-> dept에 제약 조건 X인 상태
SQL 트레이스에 SORT UNIQUE 발생
select b.*
from (select /*+ no_merge */ distinct deptno from dept order by deptno) a, emp b
whrere b.deptno = a.deptno
--> 힌트 사용 시
(9i)
select /*+ ordered use_nl(emp) */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)
(10g)
select /*+ leading(dept@qb1) use_nl(emp) */ * from emp
where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)세미 조인 방식으로 수행
select * from emp
where deptno in (select deptno from dept)
SELECT ~
NESTED LOOPS SEMI
TABLE ACCES FULL (EMP)
INDEX RANGE SCAN (DEPT_IDX)
NL 세미 조인으로 수행할 때는 sort unique 오퍼레이션을 수행하지 않고도 결과집합이 M쪽 집합으로 확장되는 것을 방지하는 알고리즘을 사용한다.
Outer 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리하는 방식
힌트 사용
Unnesting한 다음에 메인 쿼리 쪽 테이블이 드라이빙 집합으로 선택되도록 한다.
select /*+ leading(emp) */ * from emp
where deptno in (select /*+ unnest nl_sj */ deptno from dept)(7) 필터 오퍼레이션과 세미조인의 캐싱 효과
옵티마이저가 쿼리 변환을 수행하는 이유는, 전체적인 시각에서 더 나은 실행계획을 수립할 가능성을 높이는 데에 있다.
서브쿼리를 Unnesting해 조인문으로 바꾸고 나면 조인 방식, 순서도 자유롭게 선택할 수 있다.
메인 쿼리를 수행하면서 건건이 서브쿼리를 반복 수행하는 단순한 필터 오퍼레이션을 사용할 수 밖에 없다. 그래도 오라클은 필터 최적화 기법을 갖고 있는데, 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 방식이다.
select count(*) from t_emp t
where exists (select /*+ no_unnest */ 'x' from dept
where deptno = t.deptno and loc is not null)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ----- -------- ------------ ---- ----- ------- ----
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.003 0 18 0 1
------- ----- -------- ------------ ---- ----- ------- ----
Total 4 0.000 0.003 0 18 0 1
Rows Row Source Operation
----- ---------------------------------------------------------------------
1 SORT AGGREGATE (cr=18 pr=0 pw=0 time=2854 us)
1400 FILTER (cr=18 pr=0 pw=0 time=25325 us)
1400 TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=7049 us)
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=122 us)
3 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=55 us) (Object ID 57571)NL 세미 조인의 캐싱 효과
select count(*) from t_emp t
where exists (select /*+ unnest nl_sj */ 'x' from dept
where deptno = t.deptno and loc is not null)
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ----- -------- ------------ ---- ----- ------- ----
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 17 0 1
Fetch 2 0.000 0.001 0 0 0 0
------- ----- -------- ------------ ---- ----- ------- ----
Total 4 0.000 0.001 0 17 0 1
Rows Row Source Operation
----- ---------------------------------------------------------------------
1 SORT AGGREGATE (cr=17 pr=0 pw=0 time=15464 us)
1400 NESTED LOOPS SEMI (cr=17 pr=0 pw=0 time=4220 us)
1400 TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=73 us)
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=31 us) (Object ID 57571)
3 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=...)(8) Anti 조인
not exists, not in 서브쿼리도 Unnesting 하지 않으면 아래와 같이 필터 방식으로 처리된다.
select * from dept d
where not exists
(select /*+ no_unnest */ 'x' from emp where deptno = d.deptno)
| Id | Operation | Name | Rows | Bytes | Cost | (%CPU) |
|----|---------------------|---------------|------|-------|------|--------|
| 0 | SELECT STATEMENT | | 3 | 60 | 5 | (0) |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 | (0) |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX| 2 | 6 | 1 | (0) |기본 처리루틴은 exists 필터와 동일하며, 조인에 성공하는 레코드가 하나도 없을 때만 결과집합에 포함시킨다는 점이 다르다.
- exists 필터: 조인에 성공하는 (서브) 레코드를 만나는 순간 결과집합에 담고 다른 (메인) 레코드로 이동
- not exists 필터: 조인에 성공하는(서브) 레코드를 만나는 순간 버리고 다음 (메인) 레코그로 이동한다. 조인에 성공하는 (서브) 레코드가 하나도 없을 때만 결과집합에 담는다.
(9) 집계 서브쿼리 제거
(10) Pushing 서브쿼리
앞에서 설명한 것처럼 Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대개 실행계획 상에서 맨 마지막 단계에 처리된다.
Pushing 서브쿼리는 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것을 말한다. (push_subq 힌트)
Pushing 서브쿼리는 Unnesting 되지 않은 서브쿼리에만 작동한다.
-> push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 방법
-- 오라클 10g에서 push_subq 힌트 사용하기
select /*+ leading(e1) use_nl(e2) */ sum(e1.sal), sum(e2.sal)
from emp1 e1, emp2 e2
where e1.no = e2.no
and e1.empno = e2.empno
and exists (select /*+ no_unnest push_subq */ 'x' from dept
where deptno = e1.deptno
and loc = 'NEW YORK')