- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949052&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
02. 서브쿼리 Unnesting
(1) 서브쿼리의 분류
- 서브쿼리(Subquery) : 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록(Query Block). 즉 쿼리에 내장된 또 다른 쿼리
- 서브쿼리 분류
- 1. 인라인 뷰(Inline View) : from 절에 나타나는 서브쿼리를 말한다.
- 2. 중첩된 서브쿼리(Nested Subquery) : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리를 말한다.
- 3. 스칼라 서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 것이 특징이다. 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 컬럼이 올 수 있는 대부분 위치에서 사용 가능하다.
- 옵티마이저는 쿼리 블록 단위로 최적화 수행.
각 서브쿼리를 최적화했다고 해서 쿼리 전체가 최적화됐다고 말할 수는 없다.
옵티마이저가 숲을 바라보는 시각으로 쿼리를 이해하려면 먼저 서브쿼리를 풀어내야만 한다.
(2) 서브쿼리 Unnesting의 의미
- nest : 상자 등을 차곡차곡 포개넣다. 중첩
- unnest : 중첩된 상태를 풀어낸다.
- 중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.
따라서 논리적인 관점에서 그 처리과정은 IN, Exists를 불문하고 필터 방식이어야 한다.
즉, 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.
하지만 서브쿼리를 처리하는 데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나를 선택한다.
1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 일컬어 '서브쿼리 Unnesting'이라고 한다.
2. 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터(Filter) 오퍼레이션이 나타난다.
(3) 서브쿼리 Unnesting의 이점
- 서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
- 서브쿼리 Unnesting과 관련한 힌트로는 아래 두 가지가 있다.
- unnest : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
- no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.
(4) 서브쿼리 Unnesting 기본 예시
select * from emp where deptno in (select /*+ no_unnest */ deptno from dept)
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5 | 185 | 3 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B1)) 3 - access("DEPTNO"=:B1)
- 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화
- 이처럼, Unnesting하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행
select * from (select deptno from dept) a, emp b where b.deptno = a.deptno select emp.* from dept, emp where emp.deptno = dept.deptno
select * from emp where deptno in (select /*+ unnest */ deptno from dept) ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 350 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 99 | 1 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 10 | 350 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | DEPT_PK | 4 | 8 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | EMP_DEPTNO_IDX | 3 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("DEPTNO"="DEPTNO")
- 서브쿼리에 UNNEST 힌트를 주고 실행계획을 확인한 결과다.
서브쿼리인데도 일반적인 Nested Loop 조인 방식으로 수행된 것을 볼 수 있다.
(5) Unnesting된 쿼리의 조인 순서 조정
- Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다.
선택은 옵티마이저의 몫이며, 판단 근거는 데이터 분포를 포함한 통계정보에 있다. - emp 테이블이 드라이빙된 경우된 경우는 아래와 같다.
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 560 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 560 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DEPTNO"="DEPTNO")
- Unnesting된 쿼리의 조인 순서를 조정하는 방법
select /*+ leading(emp) */ * from emp where deptno in (select /*+ unnest */ deptno from dept) select /*+ leading(dept) */ * from emp where deptno in (select /*+ unnest */ deptno from dept) select /*+ ordered */ * from emp where deptno in (select /*+ unnest */ deptno from dept) select /*+ leading(dept@qb1) */ * from emp where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)
(6) 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때
<사례1>
select * from dept where deptno in (select deptno from emp) select * from (select deptno from emp) a, dept b where b.deptno = a.deptno
<사례2>
select * from emp where deptno in (select deptno from dept)
- 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
- 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인(Semi Join)방식으로 조인한다. 이것이 세미조인이 탄생하게 된 배경이다.
- Sort Unique 오퍼레이션 수행
- 세미 조인 방식으로 수행
(7) 필터 오퍼레이션과 세미조인의 캐싱 효과
(8) Anti 조인
(9) 집계 서브쿼리 제거
문서에 대하여
- 최초작성자 : [smkang]
- 최초작성일 : 2010년 05월 08일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949052&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.