서브쿼리 Unnesting

서브쿼리란?
서브쿼리의 분류
서브쿼리의 동작 방식 이해하기
서브쿼리 실행계획 제어하기
서브쿼리를 활용한 SQL 성능개선

서브쿼리란?

일반적인 서브쿼리란?

쿼리에 비교 조건으로 사용되는 중첩된 SELECT 쿼리를 의미
집합적인 사고를 필요로 하는 조인 보다는 절차적이므로 사용하기 쉽다.
무분별하게 남용하여 사용할 경우 성능 문제가 발생할 확률이 높다.


-- 사용패턴 1
-- 서브쿼리 추출 결과는 반드시 1건 (서브쿼리 -> Main SQL)
SELECT *
  FROM emp
 WHERE sal > (SELECT avg(sal)
          FROM emp)

-- 사용패턴 2
-- 서브쿼리 추출 결과가 여러 건 (서브쿼리 <-> Main SQL)
-- 성능문제가 주로 발생하는 패턴
SELECT c1,
       c2,
       c3
  FROM SUBQUERY_T2 t2
 WHERE c2 = 'A'
   AND EXISTS (SELECT /*+ NO_UNNEST */'x'
          FROM SUBQUERY_T1 t1
         WHERE t1.c5 = t2.c2)

서브쿼리의 분류

인라인 뷰(Inline View)	from 절에 나타나는 서브쿼리
중첩된 서브쿼리(Nested Subquery)	where 절에 사용된 서브쿼리. 특히, 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태는 '상관관계 있는(Correlated) 서브쿼리'라고 함.
스칼라 서브쿼리(Scalar Subquery)	한 레코드당 하나의 컬럼 값만을 리턴하는 서브 쿼리.

옵티마이저는 쿼리 블록 단위로 최적화를 수행하고, 각 서브쿼리를 최적화했다고 쿼리 전체가 최적화됐다고 할 순 없음.
서브쿼리 Unnesting 은 중첩된 서브쿼리(Nested Subquery), 뷰 Merging 은 인라인 뷰와 관련이 있음.

서브쿼리의 동작 방식 이해하기

Filter 동작 방식

최대 Main SQL에서 추출된 데이터 건수 만큼 서브쿼리가 반복적으로 수행되며 처리 되는 방식{}
1. Main SQL의 추출건수가 100만 건이면 서브쿼리는 최대 100만 번 수행된다.
2. Input 값에 해당하는 값의 종류가 적은 경우에는 Filter Optimization{}{} 최적화 작업을 통해 오히려 조인 방식보다 효율적일 수 있다.

Q. Filter 동작방식은 항상 성능이 좋지 않다??

--> Input 값의 종류가 다양하지 않다면??

-> Filter 동작 방식이 유리할 수 있다.

상황 1 (Main SQL의 추출건수가 많고, Input 값이 unique한 경우)


-- Main SQL 추출 건수 : 380,001건
-- t2.c1은 unique한 컬럼

SELECT c1,
       c2,
       c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */'x'
          FROM SUBQUERY_T1 t1
         WHERE t1.c4 = t2.c1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          2          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch    15535      2.34       2.41          0    1156653          0      230001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    15337      2.35       2.42          0    1156653          0      230001

Rows     Row Source Operation
-------  ---------------------------------------------------
 230001  FILTER (cr=1156653 pr=0 pw=0 time=1846157 us)
 380001   FILTER  (cr=16650 pr=0 pw=0 time=3336 us)
 380001    TABLE ACCESS SUBQUERY_T2 (cr=16650 pr=0 pw=0 time=35462 us)
 230001   INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=1140003 pr=0 ...)

상황 2 (Main SQL의 추출건수가 적고, Input 값이 unique한 경우)


-- Main SQL 추출 건수 : 5건
-- t2.c1은 unique한 컬럼

SELECT c1,
       c2,
       c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */'x'
          FROM SUBQUERY_T1 t1
         WHERE t1.c4 = t2.c1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       1332          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0       1332          0           5

Rows     Row Source Operation
-------  ---------------------------------------------------
 230001  FILTER (cr=1332 pr=0 pw=0 time=385929 us)
 380001   FILTER  (cr=26650 pr=0 pw=0 time=3480 us)
 380001    TABLE ACCESS SUBQUERY_T2 (cr=26650 pr=0 pw=0 time=40002 us)
 230001   INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=15 pr=0 pw=0 time=223 us)

상황 3 (Main SQL의 추출건수는 많지만, Input 값의 종류가 26가지인 경우)


-- Main SQL 추출 건수 : 380,001건
-- t2.c2는 값의 종류가 26가지

SELECT c1,
       c2,
       c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */'x'
          FROM SUBQUERY_T1 t1
         WHERE t1.c5 = t2.c2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    25335      1.01       1.25          0      26728          0      380001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25337      1.01       1.25          0      26728          0      380001

Rows     Row Source Operation
-------  ---------------------------------------------------
 380001  FILTER (cr=26728 pr=0 pw=0 time=385929 us)
 380001   FILTER  (cr=26650 pr=0 pw=0 time=3480 us)
 380001    TABLE ACCESS SUBQUERY_T2 (cr=26650 pr=0 pw=0 time=40002 us)
     26   INDEX RANGE SCAN SUBQUERY_T1_IDX_02 (cr=78 pr=0 pw=0 time=223 us)

Filter 동작방식의 성능을 결정하는 요소

Main SQL의 추출 건수
Input 값의 종류

Filter 동작방식의 특징

구분	내용
수행 순서	Main SQL이 먼저 수행된다.
Main SQL 추출건수	최대 Main SQL 추출 건수 만큼 서브 쿼리가 수행된다. Main SQL 추출 건수가 적을 경우에는 Filter 동작방식은 불리하지 않다.
Input 값의 종류	Unique 할 경우 Main SQL 추출 건수 만큼 서브쿼리가 수행된다. 값의 종류가 적을 경우, 최소 값의 종류만큼만 서브쿼리가 수행된다.
유연성	Main SQL을 먼저 수행해야만 하므로 다양한 상황에서 유연하게 대처하기는 어려운 동작 방식.

Join 동작 방식

Join 동작방식은 Filter 동작 방식에 비해 유연한 대처가 가능하다{}
1. 조인방법, 조인순서 유리한 것을 선택 할 수 있다.
2. Filter Optimization 효과{}{}를 이용할 수 없다. Input이 동일한 값이 많다면 Filter 동작방식이 유리할 수 있다.

조인동작방식은 조인방법, 조인순서의 선택이 가능

--> 상황에 따른 유연한 처리가 가능함

-> 조인동작방식이 항상 유리하진 않다.

상황 Sub Query에 적절한 Index가 없어 비효율이 발생한 경우



SELECT c1,
       c2,
       c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */'x'
          FROM SUBQUERY_T1 t1
         WHERE t1.c6 = t2.c3 AND t1.c6 >= :b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2    156.11     157.03          0    7863857          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    156.12     157.03          0    7863857          0          11

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  FILTER (cr=7863857 pr=0 pw=0 time=157033184 us)
    221   FILTER  (cr=7 pr=0 pw=0 time=2467 us)
    221    TABLE ACCESS BY INDEX ROWIDSUBQUERY_T2 (cr=7 pr=0 pw=0 time=2125 us)
    221     INDEX RANGE SCAN PK_SUBQUERY_2 (cr=4 pr=0 pw=0 time=690 us)
     11   FILTER (cr=7863850 pr=0 pw=0 time=157028384 us)
     11    TABLE ACCESS FULL SUBQUERY_T1 (cr=7863850 pr=0 pw=0 time=157028031 us)

Q. 테이블 T1에 대해 C6 컬럼에 대해 인덱스 생성?

--> SQL의 수행빈도 조사(수행빈도 낮음)

-> 인덱스 생성 외의 개선방안

---> 조인동작방식 선택, 조인은 Hash Join으로

-> 반복적인 Full Table Scan을 줄여 성능 개선

개선 Index를 생성하지 않고 조인동작방식(Hash)으로 처리하여 개선



SELECT c1,
       c2,
       c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1 AND c1 <= :b2
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */'x'
          FROM SUBQUERY_T1 t1
         WHERE t1.c6 = t2.c3 AND t1.c6 >= :b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.90       1.91          0      37422          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.90       1.92          0      37422          0          11

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  FILTER (cr=37422 pr=0 pw=0 time=1910470 us)
    221   HASH JOIN SEMI  (cr=37422 pr=0 pw=0 time=1910466 us)
    221    TABLE ACCESS BY INDEX ROWIDSUBQUERY_T2 (cr=5 pr=0 pw=0 time=42 us)
    221     INDEX RANGE SCAN PK_SUBQUERY_2 (cr=3 pr=0 pw=0 time=31 us)
6400640   TABLE ACCESS FULL SUBQUERY_T1 (cr=37417 pr=0 pw=0 time=6261 us)

서브쿼리 동작방식을 제어하는 힌트

HINT 명	설명
NO_UNNEST	서브쿼리를 Filter 방식으로 유도하는 HINT
UNNEST	서브쿼리를 조인동작방식으로 유도하는 HINT
NL_SJ	조인동작방식 중 Nested Loops Join Semi로 유도하는 HINT
HASH_SJ	조인동작방식 중 Hash Join Semi로 유도하는 HINT
NL_AJ	조인동작방식 중 Nested Loops Join Anti로 유도하는 HINT
HASH_AJ	조인동작방식 중 Hash Join Anti로 유도하는 HINT
ORDERED	FROM절에 나열된 순서대로 조인 순서를 정하는 HINT (SUB QUERY가 존재하면 서브쿼리부터 수행하도록 유도함
QB_NAME	QUERY BLOCK의 이름을 지정하는 HINT
SWAP_JOIN_INPUTS	HASH OUTER JOIN과 같이 순서가 고정된 상황에서 조인 순서를 바꾸도록 유도하는 HINT
NO_SWAP_JOIN_INPUTS	HASH OUTER JOIN과 같이 순서가 고정된 상황에서 조인 순서를 바꾸지 못하도록 유도하는 HINT
PUSH_SUBQ	서브쿼리가 먼저 수행하도록 유도하는 HINT

책 예제 풀이

원본 쿼리 1


SQL> select * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7     and    job = 'SALESMAN'
  8     and    sal > 1000)
  9  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3497946635

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    62 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |          |     1 |    62 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP      |     4 |   128 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_X01  |     2 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - access("DNAME"='SALES')
   4 - filter("SAL">1000)
   5 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='SALESMAN')

1번


  1  select * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select /*+ no_unnest no_push_subq */ 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7     and    job = 'SALESMAN'
  8*    and    sal > 1000)
  9  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3269917256

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    30 |     4   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    32 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_X01  |     2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
              WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
   3 - access("DNAME"='SALES')
   4 - filter("SAL">1000)
   5 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')

2번


  1  select  * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select /*+ no_unnest no_push_subq */ 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7*    and    job = 'SALESMAN')
  8  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2128684681

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    30 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                      |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | EMP_X01  |     1 |    19 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
              WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
   3 - access("DNAME"='SALES')
   4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')

3번


  1  select * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select /*+ no_unnest */ 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7     and    job = 'SALESMAN'
  8*    and    sal >= 1000)
SQL>
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3410011884

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    30 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN            | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    32 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_X01  |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("DNAME"='SALES')
       filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
              "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">=1000))
   3 - filter("SAL">=1000)
   4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')

4번


  1  select /*+ index(d dept_x01) */ * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select /*+ no_unnest index(emp emp_x01) */ 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7*    and    job = 'SALESMAN')
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 166048891

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    30 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | EMP_X01  |     1 |    19 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("DNAME"='SALES')
       filter( EXISTS (SELECT /*+ NO_UNNEST INDEX ("EMP" "EMP_X01") */ 0 FROM
              "EMP" "EMP" WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
   3 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')

5번



drop index dept_x01; 
create index dept_x01 on dept(dname);

SQL> select /*+ index(d dept_x01) */ * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select /*+ no_unnest push_subq  */ 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7     and    job = 'SALESMAN'
  8     and    sal > 1000);

Execution Plan
----------------------------------------------------------
Plan hash value: 3234211606

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    30 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    32 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_X01  |     2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
              WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
   2 - access("DNAME"='SALES')
   3 - filter("SAL">1000)
   4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')

6번


  1  select /*+ index(d dept_x01) */ * from dept d
  2  where  dname = 'SALES'
  3  and    exists (
  4     select /*+ no_unnest push_subq  */ 'x'
  5     from   emp
  6     where  deptno = d.deptno
  7*    and    job = 'SALESMAN')
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3152071781

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    30 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DEPT_X01 |     1 |       |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN          | EMP_X01  |     1 |    19 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
              WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
   2 - access("DNAME"='SALES')
   3 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')

7번


drop index emp_x01 ;
create index emp_x01 on emp(sal, deptno);

SQL> select  * from dept d
  2  where  deptno = (
  3     select /*+ index(e emp_x01) */ deptno
  4     from  emp e
  5     where sal >= 5000
  6     and   job = 'PRESIDENT')
  7  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2576796375

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    30 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    30 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN           | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    32 |     2   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | EMP_X01 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM "EMP"
              "E" WHERE "SAL">=5000 AND "JOB"='PRESIDENT'))
   3 - filter("SAL">=5000)
   4 - access("JOB"='PRESIDENT')
       filter("JOB"='PRESIDENT')

8번


  1  select  * from dept d
  2  where  deptno = (
  3     select /*+ index(e emp_x01) */ deptno
  4     from  emp e
  5     where 1 = 1
  6*    and   job = 'PRESIDENT')
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3718500214

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    30 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN          | EMP_X01 |     1 |    19 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM
              "EMP" "E" WHERE "JOB"='PRESIDENT'))
   3 - access("JOB"='PRESIDENT')
       filter("JOB"='PRESIDENT')

9번



  1  select  * from dept d
  2  where  deptno = (
  3     select /*+ index(e emp_x01) */ max(deptno)
  4     from  emp e
  5     where 1 = 1
  6     and   sal > 5000
  7*    and   job = 'PRESIDENT')
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 211460956

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    30 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | DEPT    |     1 |    30 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN            | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE              |         |     1 |    32 |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    32 |     2   (0)| 00:00:01 |
|*  5 |      INDEX FULL SCAN           | EMP_X01 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ MAX("DEPTNO") FROM
              "EMP" "E" WHERE "SAL">5000 AND "JOB"='PRESIDENT'))
   4 - filter("SAL">5000)
   5 - access("JOB"='PRESIDENT')
       filter("JOB"='PRESIDENT')

10번


  1  select  * from dept d
  2  where  deptno = (
  3     select /*+ index(e emp_x01) */ max(deptno)
  4     from  emp e
  5     where 1 = 1
  6*    and   job = 'PRESIDENT')
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3985888286

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    30 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    30 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN           | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE             |         |     1 |    19 |            |          |
|   4 |     FIRST ROW                 |         |     1 |    19 |     1   (0)| 00:00:01 |
|*  5 |      INDEX FULL SCAN (MIN/MAX)| EMP_X01 |     1 |    19 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ MAX("DEPTNO") FROM
              "EMP" "E" WHERE "JOB"='PRESIDENT'))
   5 - filter("JOB"='PRESIDENT')

참고 상황

상황1 아래 SQL에서 서브쿼리를 먼저 읽은 후, Nested Loops Join 으로 수행하도록 해보세요.
쿼리


SQL> select *
  2  from       emp a
  3  where      empno in (
  4     select  max(empno)
  5     from    emp x
  6     group by deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 3051006254

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    14 |  1400 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |    14 |  1400 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |    14 |  1218 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | EMP_PK   |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |    14 |   182 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      | VW_NSO_1 |    14 |   182 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |          |    14 |   364 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    14 |   364 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("EMPNO"="MAX(EMPNO)")
       filter("EMPNO"="MAX(EMPNO)")

시도1


  1  select /* use_nl(x) */ *
  2  from       emp a
  3  where      empno in (
  4     select  /*+ unnest push_subq */ max(empno)
  5     from    emp x
  6*    group by deptno)
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3051006254

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     3 |   153 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |     3 |   153 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | EMP_PK   |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     3 |    39 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      | VW_NSO_1 |     3 |    39 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |          |     3 |    21 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("EMPNO"="MAX(EMPNO)")
       filter("EMPNO"="MAX(EMPNO)")

시도2


  1  select /* leading(x@sub) qb_name(main) use_nl(a@main) */ *
  2  from       emp a
  3  where      empno in (
  4     select  /*+ unnest push_subq qb_name(sub) */ max(empno)
  5     from    emp x
  6*    group by deptno)
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3051006254

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     3 |   153 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |     3 |   153 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | EMP_PK   |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     3 |    39 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      | VW_NSO_1 |     3 |    39 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |          |     3 |    21 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("EMPNO"="MAX(EMPNO)")
       filter("EMPNO"="MAX(EMPNO)")

Q. 왜 실행계획이 의도한 대로 제어되지 않았을까??

--> Optimizer가 서브쿼리를 Inline View로 최적화 작업을 수행

> View의 이름이 VW_NSO_1이란 점에서 유추

--> QUERY BLOCK명이 변경되고 이로 인해 힌트가 무시됨

> A. Optimizer는 서브쿼리를 Inline View로 변경할 경우 From 절의 앞에 위치 시킨다.!!

> A. ORDERED 힌트는 Query Block Name과 상관없이 From절에 나열된 순서대로 수행한다는 특징이 있다.!!

최종 시도


  1  select /*+ ordered use_nl(a) */ *
  2  from       emp a
  3  where      empno in (
  4     select  /*+ unnest */ max(empno)
  5     from    emp x
  6*    group by deptno)
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2044576694

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     3 |   153 |     7  (15)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |     3 |   153 |     7  (15)| 00:00:01 |
|   3 |    VIEW                      | VW_NSO_1 |     3 |    39 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY            |          |     3 |    21 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL       | EMP      |    14 |    98 |     3   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | EMP_PK   |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    38 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   6 - access("EMPNO"="MAX(EMPNO)")

서브쿼리 Unnesting,. (by sesesejoa) [2014.06.13]

서브쿼리 Unnesting

서브쿼리란?

서브쿼리의 분류

서브쿼리의 동작 방식 이해하기

Filter 동작 방식

Q. Filter 동작방식은 항상 성능이 좋지 않다??

--> Input 값의 종류가 다양하지 않다면??

-> Filter 동작 방식이 유리할 수 있다.

Join 동작 방식

조인동작방식은 조인방법, 조인순서의 선택이 가능

--> 상황에 따른 유연한 처리가 가능함

-> 조인동작방식이 항상 유리하진 않다.

Q. 테이블 T1에 대해 C6 컬럼에 대해 인덱스 생성?

--> SQL의 수행빈도 조사(수행빈도 낮음)

-> 인덱스 생성 외의 개선방안

---> 조인동작방식 선택, 조인은 Hash Join으로

-> 반복적인 Full Table Scan을 줄여 성능 개선

책 예제 풀이

참고 상황

Q. 왜 실행계획이 의도한 대로 제어되지 않았을까??

--> Optimizer가 서브쿼리를 Inline View로 최적화 작업을 수행

> View의 이름이 VW_NSO_1이란 점에서 유추

--> QUERY BLOCK명이 변경되고 이로 인해 힌트가 무시됨

> A. Optimizer는 서브쿼리를 Inline View로 변경할 경우 From 절의 앞에 위치 시킨다.!!

> A. ORDERED 힌트는 Query Block Name과 상관없이 From절에 나열된 순서대로 수행한다는 특징이 있다.!!