by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 서브쿼리 Unnesting

Added by 강상미, last edited by 강상미 on 5월 08, 2010



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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.