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'를 참고하였습니다.*