02.서브쿼리 Unnesting

서브쿼리 : 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록

서브쿼리의 분류

인라인뷰(Inline View) : from절에 나타나는 서브쿼리
중첩된 서브쿼리(Nested Subquery) : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리
특히 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계 있는(Correlated) 서브쿼리' 라고 한다.
스칼라 서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 서브쿼리
주로 select-list에서 사용되지만 몇 가지 예외사항을 빼면 컬럼이 올 수 있는 대부분의 위치에서 사용 가능하다.

서브쿼리 unnesting 의 의미

서브쿼리는 메인쿼리에 종속적이기 때문에 원칙상 필터 방식으로 수행되어야하지만 필터 방식이 항상 최적의 수행속도를
보장하지 않으므로 옵티마이저는 해당 서브쿼리를 동일한 결과를 보장하는 조인문으로 변환하고 최적화한다.
이를 서브쿼리 Unnesting이라고 한다. 같은 레벨로 서브쿼리를 만들어준다는 의미로 서브쿼리 Flattening이라고도 한다.

서브쿼리 unnesting 이점 :

서브쿼리를 메인쿼리와 같은 레벨로 풀어내면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
이러한 이점 때문에 오라클 9i에서는 정확히 같은 결과집합임이 보장된다면 무조건 서브쿼리 Unnesting을 시도한다.
10g부터는 서브쿼리 Unnesting이 비용기반 쿼리 변환 방식으로 전환된다.

서브쿼리 관련 힌트
- unnest : 서브쿼리 unnest 유도하여 조인 방식으로 최적화
- no_unnest : 서브쿼리를 필터 방식으로 최적화

서브쿼리 Unnesting 기본 예시

예시

select *
  from emp
 where deptno in (select deptno from dept)

위 SQL문을 unnesting하지 않는다면 아래와 같이 수행할 것이다.

select *
  from emp
 where deptno in (select /*+no_unnest*/ deptno from dept)

--------------------------------------
| Id  | Operation          | Name    |
--------------------------------------
|   0 | SELECT STATEMENT   |         |
|*  1 |  FILTER            |         |
|   2 |   TABLE ACCESS FULL| EMP     |
|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |
--------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NOT NULL)
   3 - access("DEPTNO"=:B1) -> 여기

:B1 부분을 보아 서브쿼리가 별도의 서브플랜으로 최적화되었음을 알 수 있다.

unnest 힌트를 사용하거나 옵티마이저가 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

위와 같이 쿼리가 변환되고 nested loop 형태로 조인되는 것을 알 수 있다.

--------------------------------------
| Id  | Operation          | Name    |
--------------------------------------
|   0 | SELECT STATEMENT   |         |
|   1 |  NESTED LOOPS      |         |
|   2 |   TABLE ACCESS FULL| EMP     |
|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |
--------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") -> 조인형태로 변환된 것을 알 수 있다.

Unnesting된 쿼리의 조인 순서 조정

조인순서조정

쿼리 변환이 일어났을 때 어느 테이블을 드라이빙 테이블로 지정할지 정할 수 있다.

select /*+leading(emp) */ *
  from emp
 where deptno in (select /*+unnest*/ deptno
                    from dept)
->emp가 드라이빙 테이블이 됨

select /*+leading(dept) */ *
  from emp
 where deptno in (select /*+unnest*/ deptno
                    from dept)
->dept가 드라이빙 테이블이 됨

select /*+ordered */ *
  from emp
 where deptno in (select /*+unnest*/ deptno
                    from dept)
->dept가 드라이빙 테이블이 됨

--qb_name은 10g부터 사용
select /*+leading(dept@qb1) */ *
  from emp
 where deptno in (select /*+unnest qb_name(qb1)*/ deptno
                    from dept)
->dept가 드라이빙 테이블이 됨

서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때

예시

select *
  from dept
 where deptno in (select deptno from emp)

위 쿼리는 아래처럼 쿼리 변형 될 것인데

select *
  from (select deptno from emp) a
       dept b
 where a.deptno = b.deptno

서브쿼리의 deptno가 unique하지 않다면 결과 건수가 달라질 것이다.

이때 옵티마이저는
서브쿼리 쪽 테이블이 먼저 드라이빙 되면 sort unique 오퍼레이션을 수행하여 중복을 제거하고
메인쿼리 쪽 테이블이 먼저 드라이빙 되면 세미 조인 방식으로 조인한다.

테스트 : dept를 먼저 드라이빙

select /*+leading(dept)*/*
  from dept
 where deptno in (select deptno from emp)

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN SEMI    |      | -> 세미 조인으로 풀리는 것을 알 수 있다.
|   2 |   TABLE ACCESS FULL| DEPT |
|   3 |   TABLE ACCESS FULL| EMP  |
-----------------------------------

테스트 : emp를 먼저 드라이빙

select /*+leading(emp)*/*
  from dept
 where deptno in (select deptno from emp)

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|*  1 |  HASH JOIN          |      |
|   2 |   SORT UNIQUE       |      | -> SORT UNIQUE 오퍼레이션이 일어난다.
|   3 |    TABLE ACCESS FULL| EMP  |
|   4 |   TABLE ACCESS FULL | DEPT |
------------------------------------

내부적으로 SORT UNIQUE 오퍼레이션이 수행된 것으로 보아 쿼리 변환이 아래와 같이 일어난 것을 알 수 있다.

select b.*
from (select /*+ no_merge */ distinct deptno from dept order by deptno) a, emp b
where b.deptno = a.deptno

필터 오퍼레이션과 세미조인의 캐싱효과

서브쿼리를 조인형태로 풀어내면 더 나은 실행계획을 수립할 가능성이 높아진다.
그렇다면 쿼리변환을 하지 않고 필터형태도 그대로 수행하는 것에는 장점이 없을까?

=>

서브쿼리로 필터할 때 오라클은 서브쿼리의 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가
같은 값이 서브쿼리로 입력되면 저장된 값을 리턴한다. 8i,9i에서는 256개, 10g에서는 1024개 해시 엔트리를 캐싱한다.

테스트 : 서브쿼리 필터시 캐싱 효과

create table t_emp
as
select *
from   emp
     ,(select rownum no from dual connect by level <= 100);

select count(*) from t_emp t
where exists (select /*+ no_unnest */ 'x' from dept
              where deptno = t.deptno and loc is not null);

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |      18 |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.01 |      18 |
|*  2 |   FILTER                      |         |      1 |        |   1400 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS FULL          | T_EMP   |      1 |   1400 |   1400 |00:00:00.01 |      12 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |00:00:00.01 |       6 |
|*  5 |     INDEX UNIQUE SCAN         | DEPT_PK |      3 |      1 |      3 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------

t_emp가 1400 row이므로 dept테이블도 1400건 반복되어야 하지만 3번만 반복되었다.

이와 같은 캐싱 효과는 10g이후 nested loop 세미조인에서도 나타난다.

select count(*) from t_emp t
where exists (select /*+ unnest nl_sj */ 'x' from dept
              where deptno = t.deptno and loc is not null);

---------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |      1 |00:00:00.01 |      18 |
|   1 |  SORT AGGREGATE     |       |      1 |      1 |      1 |00:00:00.01 |      18 |
|   2 |   NESTED LOOPS SEMI |       |      1 |   1400 |   1400 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS FULL| T_EMP |      1 |   1400 |   1400 |00:00:00.01 |      12 |
|*  4 |    TABLE ACCESS FULL| DEPT  |      3 |      4 |      3 |00:00:00.01 |       6 |
---------------------------------------------------------------------------------------

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 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      10 |
|*  1 |  FILTER            |      |      1 |        |      1 |00:00:00.01 |      10 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      2 |      3 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NULL)
   3 - filter("DEPTNO"=:B1)


Unnesting 하면 Anti 조인 방식으로 처리된다.

select *
  from dept d
 where  not exists
       (select /*+ unnest nl_aj */'x' from emp where deptno = d.deptno);

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      10 |
|   1 |  NESTED LOOPS ANTI |      |      1 |      1 |      1 |00:00:00.01 |      10 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      9 |      3 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPTNO"="D"."DEPTNO")

select *
  from dept d
 where  not exists
       (select /*+ unnest merge_aj */'x' from emp where deptno = d.deptno);

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  MERGE JOIN ANTI             |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       2 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |      1 |      4 |      4 |00:00:00.01 |       1 |
|*  4 |   SORT UNIQUE                |         |      4 |     14 |      3 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPTNO"="D"."DEPTNO")
       filter("DEPTNO"="D"."DEPTNO")

select *
  from dept d
 where  not exists
       (select /*+ unnest hash_aj */'x' from emp where deptno = d.deptno);

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  1 |  HASH JOIN ANTI    |      |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="D"."DEPTNO")

집계 서브쿼리 제거

집계 함수(Aggregate Function)를 포함하는 서브쿼리를 Unnesting하고 이를 다시 분석함수로 대체하는 쿼리 변환이 10g에서 도입되었다.

예시

select d.deptno, d.dname, e.empno, e.ename, e.sal
from   emp e, dept d
where  d.deptno = e.deptno
and    e.sal >= (select avg(sal) from emp where deptno = d.deptno)

1차 변환 -->

select d.deptno, d.dname, e.empno, e.ename, e.sal
from  (select deptno, avg(sal) avg_sal from emp group by deptno) x, emp e, dept d
where  d.deptno = e.deptno
and    e.deptno = x.deptno
and    e.sal >= x.avg_sal

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|*  3 |    HASH JOIN                 |         |
|   4 |     VIEW                     |         |
|   5 |      HASH GROUP BY           |         |
|   6 |       TABLE ACCESS FULL      | EMP     |
|   7 |     TABLE ACCESS FULL        | EMP     |
|*  8 |    INDEX UNIQUE SCAN         | DEPT_PK |
|   9 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
------------------------------------------------

-> emp 테이블을 두번 참조하는 것을 알 수 있다.

이 쿼리는 다시 아래와 같이 변환된다.

select deptno, dname, empno, ename, sal
from (
  select d.deptno, d.dname, e.empno, e.ename, e.sal
       ,(case when e.sal >= avg(sal) over (partition by d.deptno)
         then e.rowid end) max_sal_rowid
  from   emp e, dept d
  where  d.deptno = e.deptno
)
where  max_sal_rowid is not null;

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|*  1 |  VIEW                          |         |
|   2 |   WINDOW BUFFER                |         |
|   3 |    NESTED LOOPS                |         |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |
|   5 |      INDEX FULL SCAN           | DEPT_PK |
|*  6 |     TABLE ACCESS FULL          | EMP     |
--------------------------------------------------

emp 테이블을 한번만 액세스했고 window buffer 오퍼레이션이 추가되었다.


해당 쿼리 변환을 사용하지 못하도록 파라미터를 변경해보면..

alter session set "_remove_aggr_subquery" = false;

select d.deptno, d.dname, e.empno, e.ename, e.sal
from   dept d, emp e
where  d.deptno = e.deptno
and    e.sal = (select max(sal) from emp where deptno = d.deptno) ;

-----------------------------------------
| Id  | Operation             | Name    |
-----------------------------------------
|   0 | SELECT STATEMENT      |         |
|*  1 |  HASH JOIN            |         |
|*  2 |   HASH JOIN           |         |
|   3 |    VIEW               | VW_SQ_1 |
|   4 |     HASH GROUP BY     |         |
|   5 |      TABLE ACCESS FULL| EMP     |
|   6 |    TABLE ACCESS FULL  | DEPT    |
|   7 |   TABLE ACCESS FULL   | EMP     |
-----------------------------------------

emp 테이블을 다시 2번 엑세스하는 것을 알 수 있다.

Pushing 서브쿼리

서브쿼리에서 필터링을 먼저해줄 경우 다음 수행 단계로 넘어가는 row 수를 크게 줄일 수 있다면 성능이 그만큼 향상 될 것이다.
pushing 서브쿼리는 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것으로 push_subq 힌트로 제어할 수 있다.
unnesting 되지 않은 상태에서 작동하기 때문에 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법이다.

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 NO_PUSH_SUBQ */ 'x'
          from dept where deptno = e1.deptno
          and  loc = 'NEW YORK');

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      1 |00:00:00.11 |   14347 |     36 |
|   1 |  SORT AGGREGATE                |         |      1 |      1 |      1 |00:00:00.11 |   14347 |     36 |
|*  2 |   FILTER                       |         |      1 |        |   3000 |00:00:00.09 |   14347 |     36 |
|   3 |    NESTED LOOPS                |         |      1 |        |  14000 |00:00:00.09 |   14341 |     36 |
|   4 |     NESTED LOOPS               |         |      1 |  14000 |  14000 |00:00:00.05 |     341 |     36 |
|   5 |      TABLE ACCESS FULL         | EMP1    |      1 |  14000 |  14000 |00:00:00.01 |      94 |      0 |
|*  6 |      INDEX UNIQUE SCAN         | EMP2_PK |  14000 |      1 |  14000 |00:00:00.05 |     247 |     36 |
|   7 |     TABLE ACCESS BY INDEX ROWID| EMP2    |  14000 |      1 |  14000 |00:00:00.02 |   14000 |      0 |
|*  8 |    TABLE ACCESS BY INDEX ROWID | DEPT    |      3 |      1 |      1 |00:00:00.01 |       6 |      0 |
|*  9 |     INDEX UNIQUE SCAN          | DEPT_PK |      3 |      1 |      3 |00:00:00.01 |       3 |      0 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( IS NOT NULL)
   6 - access("E1"."NO"="E2"."NO" AND "E1"."EMPNO"="E2"."EMPNO")
   8 - filter("LOC"='NEW YORK')
   9 - access("DEPTNO"=:B1)


사용후

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');

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |      1 |        |      1 |00:00:00.02 |    3347 |
|   1 |  SORT AGGREGATE                 |         |      1 |      1 |      1 |00:00:00.02 |    3347 |
|   2 |   NESTED LOOPS                  |         |      1 |        |   3000 |00:00:00.02 |    3347 |
|   3 |    NESTED LOOPS                 |         |      1 |    700 |   3000 |00:00:00.02 |     347 |
|*  4 |     TABLE ACCESS FULL           | EMP1    |      1 |    700 |   3000 |00:00:00.01 |     100 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      1 |00:00:00.01 |       6 |
|*  6 |       INDEX UNIQUE SCAN         | DEPT_PK |      3 |      1 |      3 |00:00:00.01 |       3 |
|*  7 |     INDEX UNIQUE SCAN           | EMP2_PK |   3000 |      1 |   3000 |00:00:00.01 |     247 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | EMP2    |   3000 |      1 |   3000 |00:00:00.01 |    3000 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter( IS NOT NULL)
   5 - filter("LOC"='NEW YORK')
   6 - access("DEPTNO"=:B1)
   7 - access("E1"."NO"="E2"."NO" AND "E1"."EMPNO"="E2"."EMPNO")

id 6번에서 미리 'NEW YORK' 데이터를 필터해주고 조인하게 되어 조인 횟수가 14000번에서 3000번으로 줄었다.
읽은 블록 역시 14374개에서 3347개로 줄었다.

2. 서브쿼리 Unnesting (by hrson81) [2017.05.27]