2. 서브쿼리 Unnesting

조회 수 14943 추천 수 0 2011.05.24 21:40:14
darkbeom *.123.161.70

4.2 서브쿼리 Unnesting

4.2.1 서브쿼리의 분류

1. 인라인뷰       : from 절에 기술되는 서브쿼리

2. 중첩된 서브쿼리 : where 절에 기술되는 서브쿼리
                   결과집합을 한정하기 위해 특히 메인쿼리에 있는 컬럼을 참조하는 형태
                   즉, 상관관계 있는 쿼리, 종속적이고 계층적인 관계라 볼 수있다.

3. 스칼라 서브쿼리 : 주로 select-list 절에 기술되는 서브쿼리
                   한레코드당 정확히 하나의 컬럼 값만을 리턴하는것


- 쿼리 블록
   서브쿼리 및 메인쿼리 각각 쿼리블록으로 나뉜다.
   옵티마이져가 최적화를 수행하는 단위로 최적의 액세스 경로와 조인순서, 조인방식을 선택하는것
             
- 문제점
   각각의 서브쿼리 즉 쿼리블록단위로 최적화를 수행했다고 해도 쿼리 전체가 최적화 됐다고는 말할수 없다.
   옵티마이져가 숲을 바라보는 시각으로 쿼리를 이해하려면 먼저 서브쿼리를 풀어내어야만 한다.

- 해결방법
   서브쿼리 Unnesting : 중첩된 서브쿼리(Nested Subquery)
   뷰 Merging : 인라인 뷰, 저장된 뷰



4.2.2 서브쿼리 Unnesting의 의미


nest   : '상자 등을 차곡차곡 포개넣다' 즉 중첩의 의미. No-Unnesting (큰 단위의 정보 속에 작은 단위의 정보를) 끼워 넣다
unnest : '중첩된 상태를 풀어내다' 즉 중첩된 서브쿼리를 풀어낸다. Unnesting

-- 아래는 하나의 쿼리에 서브쿼리가 이중삼중으로 중첩(nest) 될 수 있다.


SELECT * FROM EMP A
WHERE  EXISTS (
           SELECT 'O' FROM DEPT
           WHERE DEPTNO = A.DEPTNO
           )
AND     SAL > (
           SELECT AVG(SAL) FROM EMP B
           WHERE  EXISTS (
                      SELECT 'O' FROM SALGRADE
                      WHERE  B.SAL BETWEEN LOSAL AND HISAL
                      AND      GRADE = 4
                      )
           )
 
-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |       |       |    11 (100)|          |
|   1 |  NESTED LOOPS SEMI       |          |     1 |    40 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL      | EMP      |     1 |    37 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE        |          |     1 |    30 |            |          |
|   4 |     MERGE JOIN SEMI      |          |     1 |    30 |     8  (25)| 00:00:01 |
|   5 |      SORT JOIN           |          |    14 |    56 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL  | EMP      |    14 |    56 |     3   (0)| 00:00:01 |
|*  7 |      FILTER              |          |       |       |            |          |
|*  8 |       SORT JOIN          |          |     1 |    26 |     4  (25)| 00:00:01 |
|   9 |        VIEW              |          |     1 |    26 |     3   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS FULL| SALGRADE |     1 |    10 |     3   (0)| 00:00:01 |
|* 11 |   INDEX UNIQUE SCAN      | PK_DEPT  |     4 |    12 |     0   (0)|          |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("SAL">)
   7 - filter("B"."SAL"<="HISAL")
   8 - access("B"."SAL">="LOSAL")
       filter("B"."SAL">="LOSAL")
  10 - filter("GRADE"=4)
  11 - access("DEPTNO"="A"."DEPTNO")



- 논리적 관점에서 그 처리과정은 IN, Exists를 불문하고 필터 방식이어야 한다.
  즉, 메인 쿼리의 레코드마다 서브쿼리를 반복수행하면서 조건에 따라 데이터를 골라내는것
  하지만 서브쿼리를 처리하는데 필터방식이 항상 최적의 수행속도를 보장하지는 않는다.
 
- 해결점
   1. 서브쿼리 Unnesting : 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화
                           메인과 서브 간의 계층구조를 풀어 서로 같은 레벨로 만들어주므로 다향한 기법을 사용가능.
                           (2장에서 배웠던 조인의원리와 활용편을 적극적으로 활용한다는 측면으로 볼 수 있다.)
   2. Filter Operation  : 원래대로 둔 상태에서 각각의 쿼리블록단위별로 최적화(서브쿼리 No-Unnesting)
                           최적화시 고려 대상으로 삼을 만한 다양한 실행계획을 생성해내는 작업이

                           매우 제한적인 범위를 가지고 있다.
   3. 또다른 기법        : 메인쿼리와 상관관계가 없고, 단일 로우를 리턴하는 경우.

                           Subquery의 결과값을 메인 쿼리에 상수로 제공하는 방식
                           Stopkey조건(rownum <= 1) 이나 집계함수(max, min, avg)등을 사용해서

                           단일 로우 리턴으로 인한 'in'이 아닌 '='로 비교한다.


 -- 3번 또다른 기번                          
SELECT * FROM TAB1 WEHERE KEY1= (SELECT AVG(COL1) FROM TAB2);       
          
SELECT * FROM TAB1 WEHERE KEY1= :VALUE1;


SELECT * FROM TAB1 WHERE (KEY1, KEY2) =
           (SELECT  COL1, COL2 FROM TAB2 WHERE COL3 >5000 AND ROWNUM = 1);

SELECT * FROM TAB1 WHERE (KEY1, KEY2) = (:VALUE1, :VALUE2);



4.2.3 서브쿼리 Unnesting의 이점

- 9i  : 정확히 같은 결과집합임이 보장된다면 무조건 Unnesting 시도
        휴리스틱 쿼리 변환 방식 작동(옵티마이저의 특정 약 34개의 rule에 인한 최적화)
- 10g : 예상비용이 더 낮을 경우에만  Unnesting 시도하고 그외에는 원본 쿼리 그대로 필터 방식으로 최적화
        비용기반 쿼리 변환 방식 작동(Cost Estimator가 계산한 Cost값에 따라 최적화)


4.2.4 서브쿼리 Unnesting의 기본 예시

- /*+ unnest */    : 서브쿼리 Unnesting 작동. 동일레벨로 최적화

- /*+ no_unnest */ : Filter Operation 작동. 각각의 쿼리블록단위별로 최적화


-- 1. 옵티마이저가 서브쿼리 Unnesting을 선호하는 예
SELECT * FROM EMP
WHERE  DEPTNO IN (SELECT DEPTNO FROM DEPT);

SELECT * FROM EMP
WHERE  DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT);

-- 2. 서브쿼리 Unnesting 작동하고 같은 레벨로 만들어준다.
SELECT * FROM  (SELECT DEPTNO FROM DEPT) A, EMP B
WHERE  B.DEPTNO = A.DEPTNO;

-- 3. 마지막으로 다음장의 뷰 Merging 과정을 거쳐 최종적으로 아래와 같은 형태
SELECT EMP.* FROM DEPT, EMP
WHERE  EMP.DEPTNO = DEPT.DEPTNO;
 
------------------------------------------------------------------------------
| 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을 사용하지 못하도록 /*+ NO_UNNEST */
SELECT * FROM EMP
WHERE  DEPTNO IN (SELECT /*+ NO_UNNEST */ DEPTNO FROM DEPT);
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     5 |   173 |     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 |
------------------------------------------------------------------------------
-- Id 1 : Filter Operation 작동

Predicate Information (identified by operation id):
---------------------------------------------------
   -- Predicate 1 : 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화 한다는 사실
   -- IN -> EXISTS 변환
   -- Unnesting 하지 않은 서브쿼리는 메인쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복수행한다.
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B1))
   3 - access("DEPTNO"=:B1)




4.2.5 Unnesting된 쿼리의 조인 순서 조정

- Unnesting에 의해 일반 조인문으로 변환 된 후에는 emp, dept 어느쪽이든 드라이빙 집합으로 선택될 수 있다.
  즉, 데이터 분포를 포함한 통계정보를 활용해서 최적의 액세스 경로와 조인순서, 조인방식을 선택할 수 있다.

- LEADING, ORDERED, QB_NAME을 이용한 조인순서 조정(교재참조-해시조인 251p)


-- LEADING : EMP 즉 메인쿼리 집합을 먼저 드라이빙.
SELECT /*+ LEADING(EMP) */ * FROM EMP
WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| 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")


-- LEADING : DEPT 즉 서브 쿼리 집합을 먼저 드라이빙.
-- 서브쿼리의 결과집합을 BUILD INPUT으로 사용하고 메인쿼리의 집합에 PROBE INPUT 한다.
SELECT /*+ LEADING(DEPT) */ * FROM EMP
WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |         |    14 |   560 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
  
-- ORDERED : 서브쿼리쪽 테이블을 직접 참조하지 않아도 되므로 원하는 대로 조인순서 유도
SELECT /*+ ORDERED */ * FROM EMP
WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |         |    14 |   560 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
  
-- ORACLE 10G 부터 쿼리블록마다 QB_NAME(쿼리블록ID) 힌트제공 하므로 쉽고 정확하게 제어가능하다.
SELECT /*+ LEADING(DEPT@QB1) */ * FROM EMP
WHERE DEPTNO IN (SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |         |    14 |   560 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

--아래의 예제는 서브쿼리 Unnesting을 활용해서 여러 조인방식을 활용한 것을 개인적으로 테스트한것이다.
-- 해시조인을 유도 했으나 실제로 NL조인
SELECT /*+ LEADING(E) USE_HASH(DEPT@QB1) */ * FROM EMP E
WHERE E.DEPTNO IN (SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------
  

-- 해시조인 유도
SELECT /*+ LEADING(DEPT@QB1) USE_HASH(E) */ * FROM EMP E
WHERE E.DEPTNO IN (SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |         |    14 |   560 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
  
  
-- NL조인 유도
SELECT /*+ LEADING(DEPT@QB1) USE_NL(E) */ * FROM EMP E
WHERE E.DEPTNO IN (SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO FROM DEPT);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |    14 |   560 |     7   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP     |     4 |   148 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------


- 결과 :서브쿼리 Unnesting를 이용한 여러가지 조인방식에 대한 활용측면의 이점이 생긴다.


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

- 지금까지는 릴레이션에 의한 M:1 관계이기 때문에 일반조인문으로 바꿔도 쿼리결과가 보장
- 서브쿼리 쪽 테이블이 조인컬럼에 PK/Unique 제약 또는 Unique 인덱스가 없다면


-- EMP테이블 DEPTNO 컬럼에는 Unique 인덱스가 없다.
-- 즉, 이러한 상태에서 서브쿼리 Unnesting이 작동 한다면 아래와 같은 오류가 발생할 수 있다.

-- MERGE JOIN SEMI
SELECT * FROM DEPT
WHERE  DEPTNO IN (SELECT DEPTNO FROM EMP);

-- 정상 결과값
10    ACCOUNTING    NEW YORK
20    RESEARCH    DALLAS
30    SALES    CHICAGO

-- MERGE JOIN
SELECT * FROM  (SELECT DEPTNO FROM EMP) A, DEPT B
WHERE  B.DEPTNO = A.DEPTNO;

-- 오류 결과값
10    10    ACCOUNTING    NEW YORK
10    10    ACCOUNTING    NEW YORK
10    10    ACCOUNTING    NEW YORK
20    20    RESEARCH      DALLAS
20    20    RESEARCH      DALLAS
20    20    RESEARCH      DALLAS
20    20    RESEARCH      DALLAS
20    20    RESEARCH      DALLAS
30    30    SALES         CHICAGO
30    30    SALES         CHICAGO
30    30    SALES         CHICAGO
30    30    SALES         CHICAGO
30    30    SALES         CHICAGO
30    30    SALES         CHICAGO


- 결과적으로 메인과 서브간의 관계를 알 수 없고 결과를 확신할 수 없으니 일반 조인문으로의 쿼리 변환을 시도 하지

  않는다.

- 옵티마이저 시각
   1. 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, SORT UNIQUE 오퍼레이션 수행하고
      1쪽 집합으로 만든 다음에 조인
   2. 메인 쿼리 쪽 테이블이 드라이빙 된다면, 세미조인 방식으로 조인한다. 이것이 세미조인의 탄생 배경
  
  
4.2.6.1 SORT UNIQUE 오퍼레이션 수행

- DEPT 테이블의 PK제약 제거
- DEPTNO 컬럼에 Nonunique 인덱스를 생성
- 실행계획 확인


ALTER TABLE DEPT DROP PRIMARY KEY CASCADE;

DROP INDEX DEPT_DEPTNO_IDX;

CREATE INDEX DEPT_DEPTNO_IDX ON DEPT(DEPTNO);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT');

-- 교재와 달리 옵티마이저가 세미 조인 방식을 선택했다.
SELECT * FROM EMP
WHERE  DEPTNO IN (SELECT DEPTNO FROM DEPT);

--------------------------------------------------------------------------------------
| ID  | OPERATION          | NAME            | ROWS  | BYTES | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS SEMI |                 |    14 |   560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPT_DEPTNO_IDX |     4 |    12 |     0   (0)|          |
--------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------

   3 - ACCESS("DEPTNO"="DEPTNO")

-- 위의 쿼리를 옵티마이저가 Dept가 Unique 하다고 판단 했지만 확신 할 수 없어서 서브쿼리 Unnesting 을 거치면서

-- SORT UNIQUE 오퍼레이션이 아래와 같이 수행되었다.


SELECT B.*
FROM  (SELECT /*+ NO_MERGE */ DISTINCT DEPTNO FROM DEPT ORDER BY DEPTNO) A, EMP B
WHERE  B.DEPTNO = A.DEPTNO ;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   700 |     9  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |      |    14 |   700 |     9  (34)| 00:00:01 |
|   2 |   VIEW               |      |     4 |    52 |     5  (40)| 00:00:01 |
|   3 |    SORT UNIQUE       |      |     4 |    12 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| DEPT |     4 |    12 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   518 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("B"."DEPTNO"="A"."DEPTNO")

-- 교재와 달리 세미조인을 선택했다면 아래와 같이 힌트를 사용해 유도할 수 있다.
-- 오라클 9i (아래는 9i 환경이 없는 관계로 10g로 그냥 돌린겁니다. 이해바람.. )
SELECT /*+ ORDERED USE_NL(EMP) */ * FROM EMP
WHERE  DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT);
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    14 |   560 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS      |                 |    14 |   560 |     6  (17)| 00:00:01 |
|   2 |   SORT UNIQUE      |                 |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN | DEPT_DEPTNO_IDX |     4 |    12 |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| EMP             |     4 |   148 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
-- 오라클 10G 이후 QB_NAME 사용한 쿼리블록 지정
SELECT /*+ LEADING(DEPT@QB1) USE_NL(EMP) */ * FROM EMP
WHERE DEPTNO IN (SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO FROM DEPT);

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    14 |   560 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS      |                 |    14 |   560 |     6  (17)| 00:00:01 |
|   2 |   SORT UNIQUE      |                 |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN | DEPT_DEPTNO_IDX |     4 |    12 |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| EMP             |     4 |   148 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("DEPTNO"="DEPTNO")


  
4.2.6.2 세미 조인 방식으로 수행


SELECT /*+ LEADING(EMP) */ * FROM EMP
WHERE  DEPTNO IN (SELECT /*+ UNNEST NL_SJ */ DEPTNO FROM DEPT);
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    14 |   560 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |                 |    14 |   560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPT_DEPTNO_IDX |     4 |    12 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"="DEPTNO")
  
-- NL 세미 조인으로 수행할 때는 SORT UNIQUE 오퍼레이션을 수행하지 않고도
-- 결과집합이 M집으로 확장되는 것을 방지하는 알고리즘을 사용한다.
-- pseudo 코드
for(i = 0; i++){
    for(j = 0; j++){
       if(i==j)break;
    }
}
-- OUTTER(=DRIVING) 테이블의 한 로우가 INNER 테이블의 한로우와 조인에 성공하면

   순간 진행을 멈추고 다음 진행

SELECT /*+ LEADING(EMP) */ * FROM EMP
WHERE  DEPTNO IN (SELECT /*+ UNNEST HASH_SJ */ DEPTNO FROM DEPT);
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    14 |   560 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |                 |    14 |   560 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN  | DEPT_DEPTNO_IDX |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPTNO"="DEPTNO")

SELECT /*+ LEADING(EMP) */ * FROM EMP
WHERE  DEPTNO IN (SELECT /*+ UNNEST MERGE_SJ */ DEPTNO FROM DEPT);
 
---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |    14 |   560 |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN SEMI    |                 |    14 |   560 |     6  (34)| 00:00:01 |
|   2 |   SORT JOIN         |                 |    14 |   518 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP             |    14 |   518 |     3   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |                 |     4 |    12 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | DEPT_DEPTNO_IDX |     4 |    12 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("DEPTNO"="DEPTNO")
       filter("DEPTNO"="DEPTNO")



- 결론 : 세미 조인 방식으로 변환할 때 장점은 해시, 소트머지 세미 조인이 가능하다.
         앞에서 설명했던 조인 방식과 더불어 활용측면의 폭이 넓어진다는 장점이 생긴다.
  
  
4.2.7 필터 오퍼레이션과 세미조인의 캐싱효과

- 전체적인 시각에서 더 나은 실행계획을 수립할 가능성을 높이는데에 있다.
- 필터 최적화 기법 : 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면

                   저장된 값을 출력 (2장 6절 스칼라서브쿼리의 캐싱효과 285p)
- 조나단 루이스    : 서브쿼리와 조인되는 컬럼의 Distinct Value 개수가 캐시 상황선을 초과하지만 않는다
                   필터 오퍼레이션이 매우 효과적인 방식


-- EMP(14) x 100 = 1400 ROW
CREATE TABLE T_EMP
AS
SELECT *
FROM   SCOTT.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)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.01          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0         18          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.02          0         19          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=18 pr=0 pw=0 time=3258 us)
   1400   FILTER  (cr=18 pr=0 pw=0 time=9882 us) -- DEPT 테이블에 대한 필터링 1400번
   1400    TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=2840 us)
      3    TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=56 us)

           -- 테이블에서 블록수 6개 출력건수 3개
      3     INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=3 pr=0 pw=0 time=32 us)(object id 52919)

            -- 인덱스에서 블록수 3개
     
-- 즉, 서브쿼리를 3번만 수행했고 그 결과를 캐시에 저장한 상태에서 반복적으로 재사용했다.

-- 9i의 필터캐싱효과는 나타나지 않았다.
-- 하지만 10g 에서는 아래와 같이 NL 세미 조인도 캐싱효과를 갖는다.
   곧 필터오퍼레이션의 장점이라 할 수 있는 캐싱효과를 동일하게 갖게되었다.

SELECT COUNT(*) FROM T_EMP T
WHERE EXISTS (SELECT /*+ UNNEST NL_SJ */ 'X' FROM DEPT
              WHERE DEPTNO = T.DEPTNO AND LOC IS NOT NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.01       0.00          0          1          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.01          0         68          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.01       0.01          0         69          0           4

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=17 pr=0 pw=0 time=1969 us)
   1400   NESTED LOOPS SEMI (cr=17 pr=0 pw=0 time=9935 us)
   1400    TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=2867 us)
      3    TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=88 us)
      3     INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=2 pr=0 pw=0 time=38 us)(object id 52919)



4.2.8 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 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    60 |     9   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE "DEPTNO"=:B1))
   3 - filter("DEPTNO"=:B1)


-- 기본 처리루틴은 exists 필터와 동일

- exists     필터 : 조인에 성공하는 (서브)레코드를 만나는 순간 결과집합에 담고 다른 (메인)레코드로 이동
- not exists 필터 : 조인에 성공하는 (서브)레코드를 만나는 순간 버리고          다른 (메인)레코드로 이동
                    조인에 성공하는 (서브)레코드가 하나도 없을 때만 결과집합에 포함
                   
- Anti 조인 방식으로 처리


SELECT * FROM DEPT D
WHERE  NOT EXISTS
  (SELECT /*+ UNNEST NL_AJ */'X' FROM EMP WHERE DEPTNO = D.DEPTNO);
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |      |     1 |    23 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    11 |    33 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
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 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    23 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN ANTI    |      |     1 |    23 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    80 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |      |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
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 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     1 |    23 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPTNO"="D"."DEPTNO")


      
- NL 과 MERGE ANTI 조인은 기본 처리루틴이 같지만
- HASH ANTI조인은 DEPT를 BUILD INPUT으로 EMP를 PROBE 탐색하면서 조인에 성공한 엔트리에만 표시한다.
  마지막으로 해시테이블을 스캔하면서 표시가 없는 엔트리만 결과집합에 담는 방식이다.(교재 274p 참조)
  

4.2.9 집계 서브쿼리 제거

- 10g 추가기능 : 집계 함수를 포함하는 서브쿼리를 Unnesting 하고 이를 다시 분석함수로 대체하는 쿼리변환


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) ;
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    10 |   620 |     6  (17)|
|*  1 |  VIEW                          | VW_WIF_1        |    10 |   620 |     6  (17)|
|   2 |   WINDOW BUFFER                |                 |    10 |   440 |     6  (17)|
|   3 |    MERGE JOIN                  |                 |    10 |   440 |     6  (17)|
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT            |     4 |    52 |     2   (0)|
|   5 |      INDEX FULL SCAN           | DEPT_DEPTNO_IDX |     4 |       |     1   (0)|
|*  6 |     SORT JOIN                  |                 |    14 |   434 |     4  (25)|
|   7 |      TABLE ACCESS FULL         | EMP             |    14 |   434 |     3   (0)|
--------------------------------------------------------------------------------------------------

-- 위의 쿼리를 서브쿼리 Unnesting 하면 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            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    56 |     9  (23)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT            |     1 |    13 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                 |     1 |    56 |     9  (23)| 00:00:01 |
|*  3 |    HASH JOIN                |                 |     1 |    43 |     8  (25)| 00:00:01 |
|   4 |     VIEW                    |                 |     3 |    78 |     4  (25)| 00:00:01 |
|   5 |      SORT GROUP BY          |                 |     3 |    21 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL     | EMP             |    14 |    98 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL       | EMP             |    14 |   238 |     3   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN         | DEPT_DEPTNO_IDX |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

-- 옵티마이저는 한번더 쿼리 변환을 시도해 인라인 뷰를 MERGING 하거나 그대로 둔채 최적화할 수 있다.
-- 즉, 위의 Unnesting 실행계획이 옵티마이저가 한번 더 최적화 시킨 아래의 실행계획이라 볼 수 있다.

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    10 |   620 |     6  (17)|
|*  1 |  VIEW                          | VW_WIF_1        |    10 |   620 |     6  (17)|
|   2 |   WINDOW BUFFER                |                 |    10 |   440 |     6  (17)|
|   3 |    MERGE JOIN                  |                 |    10 |   440 |     6  (17)|
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT            |     4 |    52 |     2   (0)|
|   5 |      INDEX FULL SCAN           | DEPT_DEPTNO_IDX |     4 |       |     1   (0)|
|*  6 |     SORT JOIN                  |                 |    14 |   434 |     4  (25)|
|   7 |      TABLE ACCESS FULL         | EMP             |    14 |   434 |     3   (0)|
--------------------------------------------------------------------------------------------------

- '_REMOVE_AGGR_SUBQUERY' 파라메터(10g부터 기본값은 True)
  서브쿼리로부터 전환된 인라인 뷰를 제거하고 아래와 같이 메인 쿼리에 분석함수를 사용하는 형태로 변환

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            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    10 |   620 |     6  (17)|
|*  1 |  VIEW                          |                 |    10 |   620 |     6  (17)|
|   2 |   WINDOW BUFFER                |                 |    10 |   370 |     6  (17)|
|   3 |    MERGE JOIN                  |                 |    10 |   370 |     6  (17)|
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT            |     4 |    52 |     2   (0)|
|   5 |      INDEX FULL SCAN           | DEPT_DEPTNO_IDX |     4 |       |     1   (0)|
|*  6 |     SORT JOIN                  |                 |    14 |   336 |     4  (25)|
|   7 |      TABLE ACCESS FULL         | EMP             |    14 |   336 |     3   (0)|
--------------------------------------------------------------------------------------------------

-- 10g에서 집계서브쿼리 제거 기능이 작동했을 때의 실행계획

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

-- 예측 : EMP 테이블은 두번 참조했지만 실행계획 상으로는 한번만 액세스 했고
          대신 WINDOW BUFFER 오퍼레이션 추가

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    10 |   620 |     6  (17)|
|*  1 |  VIEW                          | VW_WIF_1        |    10 |   620 |     6  (17)|
|   2 |   WINDOW BUFFER                |                 |    10 |   440 |     6  (17)|
|   3 |    MERGE JOIN                  |                 |    10 |   440 |     6  (17)|
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT            |     4 |    52 |     2   (0)|
|   5 |      INDEX FULL SCAN           | DEPT_DEPTNO_IDX |     4 |       |     1   (0)|
|*  6 |     SORT JOIN                  |                 |    14 |   434 |     4  (25)|
|   7 |      TABLE ACCESS FULL         | EMP             |    14 |   434 |     3   (0)|
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("VW_COL_6" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")

-- 실측
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |       |       |    11 (100)|         
|*  1 |  HASH JOIN                    |                 |     1 |    56 |    10  (20)| 00:00:01
|   2 |   MERGE JOIN                  |                 |     2 |    78 |     7  (29)| 00:00:01
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT            |     4 |    52 |     2   (0)| 00:00:01
|   4 |     INDEX FULL SCAN           | DEPT_DEPTNO_IDX |     4 |       |     1   (0)| 00:00:01
|*  5 |    SORT JOIN                  |                 |     3 |    78 |     5  (40)| 00:00:01
|   6 |     VIEW                      |                 |     3 |    78 |     4  (25)| 00:00:01
|   7 |      SORT GROUP BY            |                 |     3 |    21 |     4  (25)| 00:00:01
|   8 |       TABLE ACCESS FULL       | EMP             |    14 |    98 |     3   (0)| 00:00:01
|   9 |   TABLE ACCESS FULL           | EMP             |    14 |   238 |     3   (0)| 00:00:01
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."DEPTNO"="E"."DEPTNO" AND "E"."SAL"="VW_COL_1")
   5 - access("DEPTNO"="D"."DEPTNO")
       filter("DEPTNO"="D"."DEPTNO")

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

-- ALTER SESSION SET "_REMOVE_AGGR_SUBQUERY" = FALSE;
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                    |         |      1 |      1 |      4 |      20 |  565K (0)|
|   2 |   NESTED LOOPS                |         |      1 |      3 |      3 |      12 |          |
|   3 |    VIEW                       | VW_SQ_1 |      1 |      3 |      3 |       7 |          |
|   4 |     HASH GROUP BY             |         |      1 |      3 |      3 |       7 |          |
|   5 |      TABLE ACCESS FULL        | EMP     |      1 |     14 |     14 |       7 |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |       5 |          |
|*  7 |     INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |       2 |          |
|   8 |   TABLE ACCESS FULL           | EMP     |      1 |     14 |     14 |       8 |          |
-------------------------------------------------------------------------------------------------


-- ALTER SESSION SET "_REMOVE_AGGR_SUBQUERY" = TRUE;
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|*  1 |  VIEW                          | VW_WIF_1 |      1 |     14 |      4 |      23 |          |
|   2 |   WINDOW SORT                  |          |      1 |     14 |     14 |      23 | 2048  (0)|
|   3 |    NESTED LOOPS                |          |      1 |     14 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP      |      1 |     14 |     14 |       7 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     14 |      1 |     14 |      16 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT  |     14 |      1 |     14 |       2 |          |
---------------------------------------------------------------------------------------------------



4.2.10 Pushing 서브쿼리

- 조건 : 서브쿼리 Unnesting 되지않고 필터 오퍼레이션으로서 필터방식이 대개 실행계획상 맨 마지막 단계에서 처리된다.
- 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리 되도록 강제 지정하는 방식
- 10g에서는 다수의 서브쿼리의 경우 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')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.25       0.29          0        392          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.25       0.29          0        392          0           2

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=196 pr=0 pw=0 time=158282 us)
   3000   FILTER  (cr=196 pr=0 pw=0 time=126138 us)
  14000    HASH JOIN  (cr=190 pr=0 pw=0 time=184783 us)
  14000     TABLE ACCESS FULL EMP2 (cr=95 pr=0 pw=0 time=14080 us)
  14000     TABLE ACCESS FULL EMP1 (cr=95 pr=0 pw=0 time=42345 us)
      1    TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=175 us)
      3     INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=83 us)(object id 53005)
     
     
-- 교재 487p 참고 (아래의 트레이스 결과에 오차가 있습니다. 다시 실험해서 올리도록 하겠습니다.)
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')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.26       0.30          0        392          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.26       0.30          0        392          0           2

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=196 pr=0 pw=0 time=158077 us)
   3000   FILTER  (cr=196 pr=0 pw=0 time=99998 us)
  14000    HASH JOIN  (cr=190 pr=0 pw=0 time=164862 us)
  14000     TABLE ACCESS FULL EMP2 (cr=95 pr=0 pw=0 time=28096 us)
  14000     TABLE ACCESS FULL EMP1 (cr=95 pr=0 pw=0 time=42232 us)
      1    TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=71 us)
      3     INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=34 us)(object id 53005)


서브쿼리 Unnesting을 활용하여 쿼리변환으로 다향한 실행계획을 유도 할 수 있었습니다.
그럼 이제 모두 퀴즈 풀어보세요~^^




오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 5월 15일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^