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

9. 쿼리 변환




목차

I. 시작하면서

II. 필터링

III. 일반적인 서브쿼리

서두 내용

옵티마이저는 주어진 SQL을 최적화하기 위해 Query를 재 구성하는데 이 때 Rule Base 기반으로 하는
RBQT(Rule Base Query Transformation)와 CBQT(Cost Base Query Transformation)이 있다.

9i까지는 이 RBQT를 사용였고 10g에 넘어와서는 CBQT를 사용하고 있으며 이에 대한 파라미터는
'_optimizer_cost_based_transformation'(Value : on, off)가 있다.

Query Transformation Life Cycle

Query Transformation에 대한 내부 구현코드의 생명주기(Life Cycle)을 보면 일반적으로 아래와 같은
패턴을 가지고 있다.

Close Beta 구현된 내부 코드가 이미 존재하고, 파라미터 또는 비공식 힌트를 사용하여
Query Transformation이 일어나게끔 할 수 있음.
Open Beta 내부 코드가 기본값으로 활성화되지만 Cost-Based는 아님.
따라서 항상 변환이 일어남.
Release 옵티마이저가 원래 SQL과 변환된 SQL의 양쪽 비용 모두를 계산해서 더 비용이
낮은 쪽을 선택함. 힌트는 deprecatted됨.

I. 시작하면서

아래에서 오라클 버전 별 실행계획을 비교하여 어떤 차이가 있는지 살펴보자.

1. 테스트 테이블 생성


-- 총 20,000명의 사원이 여섯 개 부서로 나뉘도록 테이블을 생성함.
-- 각 사원은 서로 다른 식별자(EMP_NO)를 가지며, 급여(SALARY)도 서로 중복되지 않는다.
CREATE TABLE EMP(DEPT_NO    NOT NULL,
                 SAL,
                 EMP_NO		NOT NULL,
                 PADDING,
                 CONSTRAINT E_PK PRIMARY KEY(EMP_NO))
AS
WITH GENERATOR AS (SELECT ROWNUM ID
                   FROM   ALL_OBJECTS
                   WHERE  ROWNUM <= 1000)
SELECT /*+ ORDERED USE_NL(V2) */
       MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 20000
;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'EMP',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

2. 오라클 버전 별 실행계획 비교

8i version 9i version 10g version
 ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';

EXPLAIN PLAN FOR
SELECT OUTER.*
FROM   EMP OUTER
WHERE  OUTER.SAL > (SELECT /*+ NO_UNNEST */
                           AVG(INNER.SAL)
                    FROM   EMP INNER
                    WHERE  INNER.DEPT_NO = OUTER.DEPT_NO);

@XPLAN3;                    
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 72000 |    35 |
|*  1 |  FILTER             |      |       |       |       |
|   2 |   TABLE ACCESS FULL | EMP  |  1000 | 72000 |    35 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    35 |
------------------------------------------------------------
                                                            
Outline Data                                                
-------------                                               
                                                            
  /*+                                                       
      BEGIN_OUTLINE_DATA                                    
      FULL(@"SEL$2" "INNER"@"SEL$2")                        
      FULL(@"SEL$1" "OUTER"@"SEL$1")                        
      OUTLINE_LEAF(@"SEL$1")                                
      OUTLINE_LEAF(@"SEL$2")                                
      OPT_PARAM('_fast_full_scan_enabled' 'false')          
      OPTIMIZER_FEATURES_ENABLE('8.1.7')                    
      IGNORE_OPTIM_EMBEDDED_HINTS                           
      END_OUTLINE_DATA                                      
  */                                                        
                                                            
Predicate Information (identified by operation id):         
---------------------------------------------------         
                                                            
   1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */       
              AVG("INNER"."SAL") 
              FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   4 - filter("INNER"."DEPT_NO"=:B1)                          
                                                              
Note                                                          
-----                                                         
   - cpu costing is off (consider enabling it)
; 
 ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '9.2.0.8';

EXPLAIN PLAN FOR
SELECT OUTER.*
FROM   EMP OUTER
WHERE  OUTER.SAL > (SELECT /*+ NO_UNNEST */
                           AVG(INNER.SAL)
                    FROM   EMP INNER
                    WHERE  INNER.DEPT_NO = OUTER.DEPT_NO);

@XPLAN3;            
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 72000 |    72 |
|*  1 |  FILTER             |      |       |       |       |
|   2 |   TABLE ACCESS FULL | EMP  |  1000 | 72000 |    36 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    36 |
------------------------------------------------------------
                                                            
Outline Data                                                
-------------                                               
                                                            
  /*+                                                       
      BEGIN_OUTLINE_DATA                                    
      FULL(@"SEL$2" "INNER"@"SEL$2")                        
      FULL(@"SEL$1" "OUTER"@"SEL$1")                        
      OUTLINE_LEAF(@"SEL$1")                                
      OUTLINE_LEAF(@"SEL$2")                                
      OPT_PARAM('_optim_peek_user_binds' 'false')           
      OPT_PARAM('_fast_full_scan_enabled' 'false')          
      OPT_PARAM('_b_tree_bitmap_plans' 'false')             
      OPTIMIZER_FEATURES_ENABLE('9.2.0.8')                  
      IGNORE_OPTIM_EMBEDDED_HINTS                           
      END_OUTLINE_DATA                                      
  */                                                        
                                                            
Predicate Information (identified by operation id):         
---------------------------------------------------         
                                                            
   1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */       
              AVG("INNER"."SAL") 
              FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   4 - filter("INNER"."DEPT_NO"=:B1)                          
                                                              
Note                                                          
-----                                                         
   - cpu costing is off (consider enabling it)
; 
 ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';

EXPLAIN PLAN FOR
SELECT OUTER.*
FROM   EMP OUTER
WHERE  OUTER.SAL > (SELECT /*+ NO_UNNEST */
                           AVG(INNER.SAL)
                    FROM   EMP INNER
                    WHERE  INNER.DEPT_NO = OUTER.DEPT_NO);

@XPLAN3;            
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   167 | 12024 |   452   (1)| 00:00:06 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    65   (2)| 00:00:01 |
----------------------------------------------------------------------------
                                                                            
Outline Data                                                                
-------------                                                               
                                                                            
  /*+                                                                       
      BEGIN_OUTLINE_DATA                                                    
      FULL(@"SEL$2" "INNER"@"SEL$2")                                        
      FULL(@"SEL$1" "OUTER"@"SEL$1")                                        
      OUTLINE(@"SEL$1")                                                     
      OUTLINE(@"SEL$2")                                                     
      OUTLINE_LEAF(@"SEL$1")                                                
      OUTLINE_LEAF(@"SEL$2")                                                
      ALL_ROWS                                                              
      OPT_PARAM('_optim_peek_user_binds' 'false')                           
      OPT_PARAM('_fast_full_scan_enabled' 'false')                          
      OPT_PARAM('_b_tree_bitmap_plans' 'false')                             
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                 
      IGNORE_OPTIM_EMBEDDED_HINTS                                           
      END_OUTLINE_DATA                                                      
  */                                                                        
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */                       
              AVG("INNER"."SAL") 
              FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   4 - filter("INNER"."DEPT_NO"=:B1)                                        
                                                                            

1) 8i, 9i에서 Driving Table인 EMP의 Cardinality 값을 1,000으로 계산했는데 그 이유는?

  • 그 이유는 아직 서브쿼리가 결과가 알려지지 않은 상태이기 때문에 'salary > :bind_variable'로
    계산을 하여 5%의 룰을 적용하였고 이로 인해 '20000 / 20 = 1000'의 값을 리턴하였다.

2) 그렇다면 10g에서의 Driving Table인 EMP의 Cardinality 값이 20,000인 이유는?

  • 우선 COST 값을 보면 최종 결과에서 452가 나왔고 EMP 테이블을 Driving할 때는 65값이 나왔는데
    이를 나누어 보면 '452 / 65 = 6.9(약 7)'의 결과가 나오는 것을 알 수 있다. 이는 맨 처음
    Driving Table을 읽은 것을 제외하면 6번을 읽었다는 얘기인데 EMP 테이블의 부서 숫자와 거의
    일치한다. 즉 10g 부터는 서브쿼리의 내용을 바인드 변수로 처리하는게 아니라 서브쿼리의 결과
    값을 IN-Memory 참조 테이블에 캐싱하는 메커니즘을 사용할 것을 알고 단지 여섯 번만 서브쿼리를
    수행하는 보다 현실적인 로직으로 처리하는 것을 알 수 있다.
    추가적으로 서브쿼리의 Cardinality 값인 3,333은 EMP 테이블 전체 로우수 '20,000 / 6'인 값과 같다.

3) 마지막으로 8i, 9i에서 최종 예측 Cardinality 값이 1,000인데 비해 10g가 167인 이유는?

  • 8i, 9i는 Base Cardinality에 5%의 룰을 단순히 적용하였지만 10g는 5%의 룰에 서브쿼리의 개수를
    나누어 개산하였기 때문이다. '20000 * 0.05 / 6 = 166.6(약 167)'

II. 필터링

1. 개요

옵티마이저가 발전하면서 오라클은 다양한 변환기법들을 사용해서 서브쿼리를 제거하는 쪽으로
많은 진화가 이루어졌기 때문에 필터 연산응 좀처럼 사용되지 않는 추세이지만 이 Operation이
정확히 어떤 작업을 하는것인지는 알아야 하므로 아래에서 이를 확인해보고자 한다.

1) 테스트 테이블 생성

CREATE TABLE PARENT(ID1        NUMBER NOT NULL,
                    SMALL_VC1  VARCHAR2(10),
                    SMALL_VC2  VARCHAR2(10),
                    PADDING    VARCHAR2(200),
                    CONSTRAINT PAR_PK PRIMARY KEY(ID1));

CREATE TABLE CHILD(ID1        NUMBER    NOT NULL,
                   ID2        NUMBER    NOT NULL,
                   SMALL_VC1  VARCHAR2(10),
                   SMALL_VC2  VARCHAR2(10),
                   PADDING	  VARCHAR2(200),
                   CONSTRAINT CHI_PK PRIMARY KEY (ID1,ID2));

CREATE TABLE SUBTEST (ID1        NUMBER NOT NULL,
                      SMALL_VC1	 VARCHAR2(10),
                      SMALL_VC2	 VARCHAR2(10),
                      PADDING    VARCHAR2(200),
                      CONSTRAINT SUB_PK PRIMARY KEY(ID1));

INSERT INTO PARENT
SELECT ROWNUM,
       TO_CHAR(ROWNUM),
       TO_CHAR(ROWNUM),
       RPAD(TO_CHAR(ROWNUM), 100)
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 3000;

COMMIT;

BEGIN
    FOR I IN 1..8 LOOP
        INSERT INTO CHILD
        SELECT ROWNUM,
               I,
               TO_CHAR(ROWNUM),
               TO_CHAR(ROWNUM),
               RPAD(TO_CHAR(ROWNUM), 100)
        FROM   PARENT;
	COMMIT;
	END LOOP;
END;
/

INSERT INTO SUBTEST
SELECT * FROM PARENT;
COMMIT;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'PARENT',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => NULL,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'CHILD',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => NULL,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'SUBTEST',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => NULL,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1');
END;
/

2) 8i 테스트


-- 부모-자식 관계를 갖는 두 테이블을 조인하는 쿼리문
-- 각 부모 레코드가 관련된 여덟 개의 자식 레코드를 갖도록 데이터를 구성함
-- 부모 테이블이 가진 값을 근거로 데이터 일부를 제거할 목적으로 서브쿼리를 사용
-- 액세스 순서가 PAR -> SUB -> CHI와 PAR -> CHI -> SUB일 경우 어떤 차이가 나는지 체크

-- 8i version. subquery postponed
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;

SELECT PAR.SMALL_VC1,
       CHI.SMALL_VC1
FROM   PARENT PAR,
       CHILD  CHI
WHERE  CHI.ID1 = PAR.ID1
AND    PAR.ID1 BETWEEN 100 
               AND     200
AND    EXISTS (SELECT NULL
               FROM   SUBTEST SUB
               WHERE  SUB.SMALL_VC1 = PAR.SMALL_VC1
               AND    SUB.ID1       = PAR.ID1
               AND    SUB.SMALL_VC2 >= '2');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('40t7w69g69dj7', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------
|*  1 |  FILTER                        |         |      8 |00:00:00.01 |    1224 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | CHILD   |    808 |00:00:00.01 |     921 |
|   3 |    NESTED LOOPS                |         |    910 |00:00:00.01 |     113 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PARENT  |    101 |00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN          | PAR_PK  |    101 |00:00:00.01 |       3 |
|*  6 |     INDEX RANGE SCAN           | CHI_PK  |    808 |00:00:00.01 |     107 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | SUBTEST |      1 |00:00:00.01 |     303 |
|*  8 |    INDEX UNIQUE SCAN           | SUB_PK  |    101 |00:00:00.01 |     202 |
----------------------------------------------------------------------------------
                         
-- 8i version. early subquery
-- PUSH_SUBQ, NO_UNNEST 힌트를 사용해도 PAR -> SUB -> CHI 액세스 순서로 유도가
-- 안되서 ROWNUM 방법을 사용함
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;

SELECT TEMP.SMALL_VC1,
       CHI.SMALL_VC1
FROM   (SELECT /*+ PUSH_SUBQ */
               PAR.SMALL_VC1,
               PAR.ID1,
               ROWNUM
        FROM   PARENT PAR
        WHERE  PAR.ID1 BETWEEN 100 
                       AND     200
        AND    EXISTS (SELECT /*+ NO_UNNEST */
                              NULL
                       FROM   SUBTEST SUB
                       WHERE  SUB.SMALL_VC1 = PAR.SMALL_VC1
                       AND    SUB.ID1       = PAR.ID1
                       AND    SUB.SMALL_VC2 >= '2')) TEMP,
       CHILD  CHI
WHERE  TEMP.ID1 = CHI.ID1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8qd5asmzzzytm', NULL, 'ALLSTATS COST LAST'));            
------------------------------------------------------------------------------------ 
| Id  | Operation                        | Name    | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------ 
|   1 |  TABLE ACCESS BY INDEX ROWID     | CHILD   |      8 |00:00:00.01 |     320 | 
|   2 |   NESTED LOOPS                   |         |     10 |00:00:00.02 |     312 | 
|   3 |    VIEW                          |         |      1 |00:00:00.01 |     309 | 
|   4 |     COUNT                        |         |      1 |00:00:00.01 |     309 | 
|*  5 |      FILTER                      |         |      1 |00:00:00.01 |     309 | 
|   6 |       TABLE ACCESS BY INDEX ROWID| PARENT  |    101 |00:00:00.01 |       6 | 
|*  7 |        INDEX RANGE SCAN          | PAR_PK  |    101 |00:00:00.01 |       3 | 
|*  8 |       TABLE ACCESS BY INDEX ROWID| SUBTEST |      1 |00:00:00.01 |     303 | 
|*  9 |        INDEX UNIQUE SCAN         | SUB_PK  |    101 |00:00:00.01 |     202 | 
|* 10 |    INDEX RANGE SCAN              | CHI_PK  |      8 |00:00:00.01 |       3 | 
------------------------------------------------------------------------------------ 
;         

'subquery postponed'에서는 'Buffers' 값이 1,224이나 'early subquery'에서는 320으로 줄어든것을 확인할 수 있음.

3) 10g 테스트


-- 10g version. subquery postponed
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;

SELECT /*+ LEADING(PAR CHI) USE_NL(PAR CHI) */
       PAR.SMALL_VC1,
       CHI.SMALL_VC1
FROM   PARENT PAR,
       CHILD  CHI
WHERE  CHI.ID1 = PAR.ID1
AND    PAR.ID1 BETWEEN 100 
               AND     200
AND    EXISTS (SELECT /*+ NO_UNNEST */
                      NULL
               FROM   SUBTEST SUB
               WHERE  SUB.SMALL_VC1 = PAR.SMALL_VC1
               AND    SUB.ID1       = PAR.ID1
               AND    SUB.SMALL_VC2 >= '2');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1h7kqmbwjcf2s', NULL, 'ALLSTATS COST LAST'));   
----------------------------------------------------------------------------------
| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------
|*  1 |  FILTER                        |         |      8 |00:00:00.01 |    1224 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | CHILD   |    808 |00:00:00.01 |     921 |
|   3 |    NESTED LOOPS                |         |    910 |00:00:00.01 |     113 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PARENT  |    101 |00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN          | PAR_PK  |    101 |00:00:00.01 |       3 |
|*  6 |     INDEX RANGE SCAN           | CHI_PK  |    808 |00:00:00.01 |     107 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | SUBTEST |      1 |00:00:00.01 |     303 |
|*  8 |    INDEX UNIQUE SCAN           | SUB_PK  |    101 |00:00:00.01 |     202 |
----------------------------------------------------------------------------------
                         
-- 10g version. early subquery
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;

SELECT /*+ LEADING(PAR) */
       PAR.SMALL_VC1,
       CHI.SMALL_VC1
FROM   PARENT PAR,
       CHILD  CHI
WHERE  CHI.ID1 = PAR.ID1
AND    PAR.ID1 BETWEEN 100 
               AND     200
AND    EXISTS (SELECT /*+ USE_NL(SUB) */
                      NULL
               FROM   SUBTEST SUB
               WHERE  SUB.SMALL_VC1 = PAR.SMALL_VC1
               AND    SUB.ID1       = PAR.ID1
               AND    SUB.SMALL_VC2 >= '2');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4wv1jvg8nra6v', NULL, 'ALLSTATS COST LAST'));            
---------------------------------------------------------------------------------- 
| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers | 
---------------------------------------------------------------------------------- 
|   1 |  TABLE ACCESS BY INDEX ROWID   | CHILD   |      8 |00:00:00.01 |     221 | 
|   2 |   NESTED LOOPS                 |         |     10 |00:00:00.01 |     213 | 
|   3 |    NESTED LOOPS SEMI           |         |      1 |00:00:00.01 |     210 | 
|   4 |     TABLE ACCESS BY INDEX ROWID| PARENT  |    101 |00:00:00.01 |       6 | 
|*  5 |      INDEX RANGE SCAN          | PAR_PK  |    101 |00:00:00.01 |       3 | 
|*  6 |     TABLE ACCESS BY INDEX ROWID| SUBTEST |      1 |00:00:00.01 |     204 | 
|*  7 |      INDEX UNIQUE SCAN         | SUB_PK  |    101 |00:00:00.01 |     103 | 
|*  8 |    INDEX RANGE SCAN            | CHI_PK  |      8 |00:00:00.01 |       3 | 
---------------------------------------------------------------------------------- 
;         

서브쿼리를 NO_UNNEST 힌트처럼 Filter가 아닌 UNNEST의 조인 형태로 처리함.

2. 스칼라 서브쿼리

스칼라 서브쿼리를 사용할때도 FILTER 실행계획이 나올 수 있다.

1) 테스트 데이터 생성

DROP TABLE EMP;
DROP TABLE GENERATOR;

CREATE TABLE GENERATOR AS
SELECT ROWNUM ID
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 1000;

CREATE TABLE EMP(DEPT_NO    NOT NULL,
                 SAL,
                 EMP_NO     NOT NULL,
                 PADDING,
                 CONSTRAINT E_PK PRIMARY KEY(EMP_NO)
)
AS
SELECT /*+ ORDERED USE_NL(V2) */
       MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 20000
;

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'EMP',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

2) 스칼라 서브쿼리 FILTER 실행계획 확인

EXPLAIN PLAN FOR
SELECT COUNT(AV_SAL)
FROM   (SELECT /*+ NO_MERGE */
              OUTER.DEPT_NO,
              OUTER.SAL,
              OUTER.EMP_NO,
              OUTER.PADDING,
              (SELECT AVG(INNER.SAL)
               FROM   EMP
               INNER  WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
        FROM   EMP OUTER)
WHERE  SAL > AV_SAL
;

@XPLAN;
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |   129   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE       |      |     1 |    26 |            |          |
|   2 |   VIEW                |      | 20000 |   507K|   129   (1)| 00:00:02 |
|*  3 |    FILTER             |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL | EMP  | 20000 |   156K|    65   (2)| 00:00:01 |
|   5 |     SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  6 |      TABLE ACCESS FULL| EMP  |  3333 | 26664 |    65   (2)| 00:00:01 |
------------------------------------------------------------------------------
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   3 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP"            
              "INNER" WHERE "INNER"."DEPT_NO"=:B1))                           
   6 - filter("INNER"."DEPT_NO"=:B1)                                          

실행계획을 보면 인라인 뷰 안의 스칼라 서브쿼리 AV_SAL 컬럼을 인라인 뷰 밖에서 조건으로
사용을 하고 있기 때문에 실행계획에서 FILTER 부분에서 확인을 할 수 있다.

만약 스칼라 서브쿼리 부분이 조건에 활용되지 않는다면 이 부분은 실행계획에서 확인하기 힘들것이다.

EXPLAIN PLAN FOR
SELECT COUNT(AV_SAL)
FROM   (SELECT /*+ NO_MERGE */
              OUTER.DEPT_NO,
              OUTER.SAL,
              OUTER.EMP_NO,
              OUTER.PADDING,
              (SELECT AVG(INNER.SAL)
               FROM   EMP
               INNER  WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
        FROM   EMP OUTER)
--WHERE  SAL > AV_SAL
;

@XPLAN;
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |    65   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    13 |            |          |
|   2 |   VIEW              |      | 20000 |   253K|    65   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  | 20000 | 60000 |    65   (2)| 00:00:01 |
----------------------------------------------------------------------------

이렇게 스칼라 서브쿼리가 실행계획에서 보여야 함에도 불구하고 안보이는 이유는 스칼라 서브쿼리 집합
밖에서 그룹함수를 사용하였기 때문이다. 일반적으로 View Depth가 깊거나 View 밖에서 그룹함수를 사용할경우
View 안의 스칼라 서브쿼리는 가려서 안보이게 된다.


-- 인라인 뷰 안의 내용만 확인할 경우
EXPLAIN PLAN FOR
SELECT /*+ NO_MERGE */
      OUTER.DEPT_NO,
      OUTER.SAL,
      OUTER.EMP_NO,
      OUTER.PADDING,
      (SELECT AVG(INNER.SAL)
       FROM   EMP
       INNER  WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM   EMP OUTER
;

@XPLAN;
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 20000 |  1406K|    65   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |  3333 | 26664 |    65   (2)| 00:00:01 |
|   3 |  TABLE ACCESS FULL | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   2 - filter("INNER"."DEPT_NO"=:B1)                                       

3. 서브쿼리 팩토링

서브쿼리 팩토링이란 9i부터 제공된 구문으로 WITH절을 사용하여 SQL을 사용할 때 집합을 미리 만드는 방법을 일컷는다.
WITH 절을 쉽게 이해한다면 인라인 뷰의 내용을 WITH절에 선언하고 SELECT 절에서는 테이블처럼 사용한다고 보면 된다.
이 방법에는 2가지 종류가 있는데 다음과 같다.

  • 1) WITH절에 /*+ INLINE */ 힌트를 사용
    • 위에서 설명한 것처럼 WITH절에 선언한 내용을 인라인 뷰처럼 사용하고자 할때 선언
    • 일반적으로 이 힌트를 사용하지 않아도 Default로 사용됨
  • 2) WITH절에 /*+ MATERIALIZE */ 힌트를 사용
    • WITH절에서 선언한 집합을 TEMP TABLE을 사용하도록 강제하는 방법
    • 이를 사용하는 이유는 WITH절의 내용과 SELECT절 이하 FROM절의 테이블 사이에 View Merging이 일어나지 않도록
      할 때 주로 사용한다.

1) /*+ INLINE */ 힌트 사용 예제

DROP TABLE T1;

CREATE TABLE T1 
AS
SELECT	*
FROM	ALL_OBJECTS
WHERE   ROWNUM <= 3000
;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'T1',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

EXPLAIN PLAN FOR
WITH GENERATOR AS 
(
SELECT /*+ INLINE */
       ROWNUM ID
FROM   T1
WHERE  ROWNUM <= 1000
)
SELECT /*+ ORDERED USE_NL(V2) */
       MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 20000
;

@XPLAN;
----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 20000 |   262   (1)| 00:00:04 |
|*  1 |  COUNT STOPKEY        |      |       |            |          |
|   2 |   NESTED LOOPS        |      | 20000 |   262   (1)| 00:00:04 |
|   3 |    VIEW               |      |    20 |     1   (0)| 00:00:01 |
|*  4 |     COUNT STOPKEY     |      |       |            |          |
|   5 |      TABLE ACCESS FULL| T1   |  3000 |    13   (0)| 00:00:01 |
|   6 |    VIEW               |      |  1000 |    13   (0)| 00:00:01 |
|*  7 |     COUNT STOPKEY     |      |       |            |          |
|   8 |      TABLE ACCESS FULL| T1   |  3000 |    13   (0)| 00:00:01 |
----------------------------------------------------------------------
                                                                      
Predicate Information (identified by operation id):                   
---------------------------------------------------                   
                                                                      
   1 - filter(ROWNUM<=20000)                                          
   4 - filter(ROWNUM<=1000)                                           
   7 - filter(ROWNUM<=1000)                                                                                                                       

2) /*+ MATERIALIZE */ 힌트 사용 예제

DROP TABLE T1;

CREATE TABLE T1 
AS
SELECT	*
FROM	ALL_OBJECTS
WHERE   ROWNUM <= 3000
;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'T1',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

EXPLAIN PLAN FOR
WITH GENERATOR AS 
(
SELECT /*+ MATERIALIZE */
       ROWNUM ID
FROM   T1
WHERE  ROWNUM <= 1000
)
SELECT /*+ ORDERED USE_NL(V2) */
       MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 20000
;

@XPLAN;
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             | 20000 |       |  2030   (1)| 00:00:25 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|*  3 |    COUNT STOPKEY           |                             |       |       |            |          |
|   4 |     TABLE ACCESS FULL      | T1                          |  3000 |       |    13   (0)| 00:00:01 |
|*  5 |   COUNT STOPKEY            |                             |       |       |            |          |
|   6 |    NESTED LOOPS            |                             |  1000K|       |  2017   (1)| 00:00:25 |
|   7 |     VIEW                   |                             |  1000 |       |     2   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6602_EC10A807 |  1000 | 13000 |     2   (0)| 00:00:01 |
|   9 |     VIEW                   |                             |  1000 |       |     2   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6602_EC10A807 |  1000 | 13000 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
                                                                                                          
Predicate Information (identified by operation id):                                                       
---------------------------------------------------                                                       
                                                                                                          
   3 - filter(ROWNUM<=1000)                                                                               
   5 - filter(ROWNUM<=20000)                                                                              

4. Complex View Merging

그룹 함수가 포함된 뷰 또는 인라인 뷰와 그룹 함수가 없는 뷰 또는 인라인 뷰 각각 1개가 조인할 때
이를 Complex View Merging이라고 한다.

만약 이 조인이 될 경우 아래의 2 경우를 생각해 볼 수 있다.
1) 그룹함수가 있는 집합을 먼저 생성하고 그룹함수가 없는 집합과 조인을 함.
2) 그룹함수가 있는 집합 안의 내용과 그룹함수가 없는 집합을 먼저 조인하게 한 후
그 뒤에 그룹함수를 사용하는 방법

1) 그룹함수 집합 먼저 생성 -> 조인

DROP TABLE T;

CREATE TABLE T AS
SELECT MOD(LEVEL, 10) GUBN,
       LEVEL          VAL
FROM   DUAL
CONNECT BY LEVEL <= 1000;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'T',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

EXPLAIN PLAN FOR
SELECT *
FROM   (SELECT GUBN, SUM(VAL)
        FROM   T
        GROUP BY GUBN) T_1,
       T T_2
WHERE  T_1.GUBN = T_2.GUBN
;       

@XPLAN;
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1000 | 32000 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |  1000 | 32000 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |    10 |   260 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |    10 |    60 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T    |  1000 |  6000 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | T    |  1000 |  6000 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
                                                                             
Predicate Information (identified by operation id):                          
---------------------------------------------------                          
                                                                             
   1 - access("T_1"."GUBN"="T_2"."GUBN")                                     

실행계획에서 보는 것처럼 인라인 뷰 T_1을 먼저 액세스한 뒤 'HASH GROUP BY'를 통해
먼저 집합을 만들과 그 뒤에 T_2 테이블을 HASH JOIN 한 것을 볼 수 있다.

2) 조인 -> 그룹함수

EXPLAIN PLAN FOR
SELECT /*+ MERGE(T_1) */
       *
FROM   (SELECT GUBN, SUM(VAL)
        FROM   T
        GROUP BY GUBN) T_1,
       T T_2
WHERE  T_1.GUBN = T_2.GUBN
;       

@XPLAN;
------------------------------------------------------------------------------------ 
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT    |      | 50000 |   585K|       |   323   (4)| 00:00:04 | 
|   1 |  HASH GROUP BY      |      | 50000 |   585K|  3552K|   323   (4)| 00:00:04 | 
|*  2 |   HASH JOIN         |      |   100K|  1171K|       |     8  (25)| 00:00:01 | 
|   3 |    TABLE ACCESS FULL| T    |  1000 |  6000 |       |     3   (0)| 00:00:01 | 
|   4 |    TABLE ACCESS FULL| T    |  1000 |  6000 |       |     3   (0)| 00:00:01 | 
------------------------------------------------------------------------------------ 
                                                                                     
Predicate Information (identified by operation id):                                  
---------------------------------------------------                                  
                                                                                     
   2 - access("GUBN"="T_2"."GUBN")                                                   

이번에는 MERGE 힌트를 사용하여 2개 테이블을 먼저 HASH JOIN하게 한 뒤 그 다음 HASH GROUP BY가
된 것을 확인할 수 있다.

일반적으로 2개 이상의 집합이 조인될 때 1개까지는 그룹함수가 있는 집합이 있을 경우
'Complex View Merging'이 가능하지만 그룹함수가 2개 이상일 경우는 'Complex View Merging'이
안되는 경우가 대부분이다. 그 이유는 옵티마이저가 2개 이상인 집합에 대해 먼저 조인을 하고
이후에 그룹함수를 사용할 경우 데이터를 보존할 가능성이 희박하다고 보기 때문에 먼저 집합을
보존하고자 'Complex View Merging'을 대부분 하지 않는다.

3) 그룹함수가 2개 이상일 경우

EXPLAIN PLAN FOR
SELECT /*+ MERGE(T_1) MERGE(T_2) */
       *
FROM   (SELECT GUBN, SUM(VAL)
        FROM   T
        GROUP BY GUBN) T_1,
       (SELECT GUBN, SUM(VAL)
        FROM   T
        GROUP BY GUBN) T_2,        
       T T_3
WHERE  T_1.GUBN = T_2.GUBN
AND    T_1.GUBN = T_3.GUBN
;       

@XPLAN;
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  1000 | 58000 |    12  (25)| 00:00:01 |
|*  1 |  HASH JOIN            |      |  1000 | 58000 |    12  (25)| 00:00:01 |
|*  2 |   HASH JOIN           |      |    10 |   520 |     9  (34)| 00:00:01 |
|   3 |    VIEW               |      |    10 |   260 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |      |    10 |    60 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T    |  1000 |  6000 |     3   (0)| 00:00:01 |
|   6 |    VIEW               |      |    10 |   260 |     4  (25)| 00:00:01 |
|   7 |     HASH GROUP BY     |      |    10 |    60 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL| T    |  1000 |  6000 |     3   (0)| 00:00:01 |
|   9 |   TABLE ACCESS FULL   | T    |  1000 |  6000 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   1 - access("T_1"."GUBN"="T_3"."GUBN")                                      
   2 - access("T_1"."GUBN"="T_2"."GUBN")                                                                         

결국 이를 해결하기 위해서는 가급적 그룹함수가 있는 집합이 1개만 유지되도록 해야 한다는 것을 의미하며
2개 이상일 경우 다른 그룹함수 집합을 스칼라 서브쿼리나 펑션으로 대체해야 한다.

5. Pushing Predicates

이 Operation은 그룹함수가 없는 뷰 또는 인라인 뷰와 다른 집합 사이에 조인을 하게 될 ? 다른 집합에서
뷰 또는 인라인 뷰로 조건이 침투가 되도록 하는 방법이다. 만약 당신이 특정 SQL을 튜닝하게 된다면
이 Operation을 유도하느냐 못하느냐에 따라 수행속도가 아주 많이 차이나는 것을 확인할 수 있을 것이다.

1) 테스트 데이터 생성

DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;

CREATE TABLE T1 AS
SELECT ROWNUM - 1 ID1,
       TRUNC((ROWNUM - 1) / 10) N1,
       LPAD(ROWNUM, 10, '0') SMALL_VC,
       RPAD('X', 100) PADDING
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 5000;

ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY(ID1);

CREATE TABLE T2 AS
SELECT TRUNC((ROWNUM - 1) / 5) ID1,
       ROWNUM ID2,
       LPAD(ROWNUM, 10, '0') SMALL_VC,
       RPAD('X', 100) PADDING
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 25000;

ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY(ID1, ID2);

CREATE TABLE T3 AS
SELECT TRUNC((ROWNUM - 1) / 5) ID1,
       ROWNUM ID2,
       LPAD(ROWNUM, 10, '0') SMALL_VC,
       RPAD('X', 100) PADDING
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 25000;

ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY(ID1, ID2);

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          =>'T1',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => NULL,
                                  METHOD_OPT 	   => 'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          =>'T2',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => NULL,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1');
END;
/


BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          =>'T3',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => NULL,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1');
END;
/

CREATE OR REPLACE VIEW V1 AS
SELECT T2.ID1,
       T2.ID2,
       T3.SMALL_VC,
       T3.PADDING
FROM   T2,
       T3
WHERE  T3.ID1 = T2.ID1
AND    T3.ID2 = T2.ID2
;

2) 조건 침투가 안될 경우

ALTER SESSION SET STATISTICS_LEVEL = ALL ;

SELECT /*+ NO_PUSH_PRED(V1) */
       T1.*,
       V1.*
FROM   T1,
       V1
WHERE  V1.ID1(+) = T1.ID1
AND    T1.N1     = 5
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2hswhprr2pp84', NULL, 'ALLSTATS LAST'));      
           
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER     |       |      1 |     50 |     50 |00:00:00.30 |   25545 |   752K|   752K| 1083K (0)|
|*  2 |   TABLE ACCESS FULL  | T1    |      1 |     10 |     10 |00:00:00.01 |      91 |       |       |          |
|   3 |   VIEW               | V1    |      1 |  25000 |  25000 |00:00:00.25 |   25454 |       |       |          |
|   4 |    NESTED LOOPS      |       |      1 |  25000 |  25000 |00:00:00.23 |   25454 |       |       |          |
|   5 |     TABLE ACCESS FULL| T3    |      1 |  25000 |  25000 |00:00:00.03 |     448 |       |       |          |
|*  6 |     INDEX UNIQUE SCAN| T2_PK |  25000 |      1 |  25000 |00:00:00.15 |   25006 |       |       |          |
-------------------------------------------------------------------------------------------------------------------
                                                                                                                   
Predicate Information (identified by operation id):                                                                
---------------------------------------------------                                                                
                                                                                                                   
   1 - access("V1"."ID1"="T1"."ID1")                                                                               

/*+ NO_PUSH_PRED(V1) */ 힌트를 사용하여 V1 뷰에 조건이 침투가 안되도록 유도하였다.
위에서 보는 것처럼 조건이 침투가 안되서 T3 테이블을 25000건 액세스 한것을 확인할 수 있다.

3) 조건 침투가 될 경우

SELECT T1.*,
       V1.*
FROM   T1,
       V1
WHERE  V1.ID1(+) = T1.ID1
AND    T1.N1     = 5
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gh71wnftc6m85', NULL, 'ALLSTATS LAST'));                 

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER            |       |      1 |     50 |     50 |00:00:00.01 |     182 |
|*  2 |   TABLE ACCESS FULL            | T1    |      1 |     10 |     10 |00:00:00.01 |      95 |
|   3 |   VIEW PUSHED PREDICATE        | V1    |     10 |      1 |     50 |00:00:00.01 |      87 |
|   4 |    NESTED LOOPS                |       |     10 |      1 |     50 |00:00:00.01 |      87 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T3    |     10 |      5 |     50 |00:00:00.01 |      32 |
|*  6 |      INDEX RANGE SCAN          | T3_PK |     10 |      5 |     50 |00:00:00.01 |      18 |
|*  7 |     INDEX UNIQUE SCAN          | T2_PK |     50 |      1 |     50 |00:00:00.01 |      55 |
--------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id):                                               
---------------------------------------------------                                               
                                                                                                  
   2 - filter("T1"."N1"=5)                                                                        
   6 - access("T3"."ID1"="T1"."ID1")                                                              
   7 - access("T2"."ID1"="T1"."ID1" AND "T3"."ID2"="T2"."ID2")                                    
       filter("T3"."ID1"="T2"."ID1")                                                              

이번에는 힌트를 제거하여 V1 뷰에 조건이 침투가 되도록 유도하였고 이로 인해 T3 테이블을 50건만
액세스하는 것을 확인할 수 있다.

III. 일반적인 서브쿼리

1. 개요

1) 서브쿼리(IN, EXISTS, ANY, ...)는 Driving Operation(공급자)과 Filter Operation(확인자)으로 나눌 수 있다.

  • 'SELECT * FROM TAB WHERE Z_CODE IN (Other Query);' 일 경우
  • Driving Operation : 서브쿼리(OTHER QUERY)가 먼저 수행되고 그 결과값을 Main절인 TAB에 공급하는 경우
  • Filter Operation : MAIN절인 TAB이 먼저 수행되고 그 결과값을 서브쿼리(Other Query)에서 체크로 푸는 경우

2) 서브쿼리를 Driving과 Filter로 사용하는 기준은 어느쪽이 작은 로우의 집합인지 파악

  • 기본적인 접근 방법은 Main절과 서브쿼리절의 집합 크기가 작은쪽을 드라이빙하고 나머지를 액세스하는 방법이다.
  • 예를들어 Main절의 집합 크기가 작다면 Main절을 먼저 액세스하고 서브쿼리를 EXISTS 구문을 사용하여 Filter로 푸는것이 좋고
    서브쿼리 집합이 작다면 IN 구문을 사용하여 Driving으로 사용하는 것이 좋다.

-- 1. oracle version
SELECT *
FROM   V$VERSION;

BANNER                                                           
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit 

-- 2. create table & index & statistics 
DROP TABLE TAB_A PURGE;
DROP TABLE TAB_B PURGE;

CREATE TABLE TAB_A AS
SELECT LEVEL CNT,
       MOD(LEVEL, 10) GUBN
FROM   DUAL
CONNECT BY LEVEL <= 100
;

CREATE TABLE TAB_B AS
SELECT LEVEL CNT,
       MOD(LEVEL, 10) GUBN
FROM   DUAL
CONNECT BY LEVEL <= 100
;

CREATE UNIQUE INDEX TAB_A_U1 ON TAB_A (CNT) COMPUTE STATISTICS;
CREATE INDEX TAB_A_N1 ON TAB_A (GUBN) COMPUTE STATISTICS;
CREATE UNIQUE INDEX TAB_B_U1 ON TAB_B (CNT) COMPUTE STATISTICS;
CREATE INDEX TAB_B_N1 ON TAB_B (GUBN) COMPUTE STATISTICS;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_A', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_B', CASCADE => TRUE);

-- 3-1) Main절에 Unique 조건을 주어 Filter로 푸는 경우를 살펴봄
-- 보는 것처럼 Main절을 1건 읽은 뒤 서브쿼리를 한건만 읽어서 최적으로 액세스 함
SELECT A.*
FROM   TAB_A A
WHERE  A.CNT = 1
AND    EXISTS (SELECT B.GUBN
               FROM   TAB_B B
               WHERE  B.GUBN = A.GUBN)
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          3          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.003          0          3          0          1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   NESTED LOOPS SEMI (cr=3 pr=0 pw=0 time=109 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_A (cr=2 pr=0 pw=0 time=75 us)
      1     INDEX UNIQUE SCAN TAB_A_U1 (cr=1 pr=0 pw=0 time=41 us)(Object ID 10438011)
      1    INDEX RANGE SCAN TAB_B_N1 (cr=1 pr=0 pw=0 time=32 us)(Object ID 10438014)
      
-- 3-2) Main절에 Unique 조건을 주어 Filter가 더 ?음에도 불구하고 Driving으로 사용할 경우
-- 서브쿼리를 강제로 Driving하기 위해 QB_NAME 힌트를 사용
-- 보는 것처럼 서브쿼리를 Driving하기 때문에 불필요하게 100건을 읽은 뒤 Main절에서 Filter를 하고 있음 
SELECT /*+ LEADING(B@SUB) */ 
       A.*
FROM   TAB_A A
WHERE  A.CNT = 1
AND    A.GUBN IN (SELECT /*+ QB_NAME(SUB) */ 
                         B.GUBN
                  FROM   TAB_B B
                  WHERE  B.GUBN = A.GUBN)
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.003          0          3          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.004          0          3          0          1

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   HASH JOIN  (cr=3 pr=0 pw=0 time=3362 us)
     10    SORT UNIQUE (cr=1 pr=0 pw=0 time=218 us)
    100     INDEX FULL SCAN TAB_B_N1 (cr=1 pr=0 pw=0 time=150 us)(Object ID 10438014)
      1    TABLE ACCESS BY INDEX ROWID TAB_A (cr=2 pr=0 pw=0 time=68 us)
      1     INDEX UNIQUE SCAN TAB_A_U1 (cr=1 pr=0 pw=0 time=36 us)(Object ID 10438011)
      
-- 4-1) 서브쿼리에 Unique 조건을 주어 Driving으로 푸는 경우를 살펴봄
-- 보는 것처럼 서브쿼리를 1건 읽은 뒤 Main절을 10건 읽어서 최적으로 액세스 함
SELECT A.*
FROM   TAB_A A
WHERE  A.GUBN IN (SELECT B.GUBN
                  FROM   TAB_B B
                  WHERE  B.GUBN = A.GUBN
                  AND    B.CNT  = 1)
;                  

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          6          0         10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.004          0          6          0         10

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   NESTED LOOPS  (cr=6 pr=0 pw=0 time=137 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_B (cr=2 pr=0 pw=0 time=73 us)
      1     INDEX UNIQUE SCAN TAB_B_U1 (cr=1 pr=0 pw=0 time=41 us)(Object ID 10438013)
     10    TABLE ACCESS BY INDEX ROWID TAB_A (cr=4 pr=0 pw=0 time=55 us)
     10     INDEX RANGE SCAN TAB_A_N1 (cr=2 pr=0 pw=0 time=242 us)(Object ID 10438012)
;

-- 4-2) 서브쿼리에 Unique 조건을 주어 Driving으로 푸는것이 더 ?음에도 불구하고 Filter로 사용할 경우
-- Main을 강제로 Driving하기 위해 힌트를 사용
-- 보는 것처럼 Main절을 Driving 때문에 불필요하게 100건을 읽은 뒤 서브쿼리에서 Filter를 하고 있음
SELECT /*+ LEADING(A) */
       A.*
FROM   TAB_A A
WHERE  EXISTS (SELECT /*+ NO_UNNEST */
                      B.GUBN
               FROM   TAB_B B
               WHERE  B.GUBN = A.GUBN
               AND    B.CNT  = 1)
;                  

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.004          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.001          0         24          0         10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.010        0.004          0         24          0         10

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   FILTER  (cr=24 pr=0 pw=0 time=197 us)
    100    TABLE ACCESS FULL TAB_A (cr=4 pr=0 pw=0 time=1084 us)
      1    TABLE ACCESS BY INDEX ROWID TAB_B (cr=20 pr=0 pw=0 time=196 us)
     10     INDEX UNIQUE SCAN TAB_B_U1 (cr=10 pr=0 pw=0 time=107 us)(Object ID 10438013)
;

3) IN이 서브쿼리를 Driving으로, 또는 EXISTS가 서브쿼리를 Filter로 반드시 풀지 않음

  • 일반적으로 서브쿼리에 IN을 사용할 경우 서브쿼리가 Driving이 되고 EXISTS를 사용할경우 Filter로 옵티마이저가 풀지만
    반드시 그렇지는 않다.
  • 그러므로 반드시 실행계획을 확인하여 유도하는대로 옵티마이저가 오는지 확인해야 한다.
    
    -- 4-2) 예제 사용
    -- EXISTS 구문을 사용하였기 때문에 TAB_A가 Driving이 될 것 같지만 옵티마이저는 CBQT(Cost Based Query Transformation)
    -- 를 사용하기 때문에 서브쿼리가 Driving이 되도록 변경하여 플랜을 작성한다.
    SELECT A.*
    FROM   TAB_A A
    WHERE  EXISTS (SELECT B.GUBN
                   FROM   TAB_B B
                   WHERE  B.GUBN = A.GUBN
                   AND    B.CNT  = 1)
    ;                  
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.000          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.000        0.000          0          6          0         10
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.000        0.000          0          6          0         10
    
    Misses in library cache during parse: 0
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
         10   NESTED LOOPS  (cr=6 pr=0 pw=0 time=131 us)
          1    TABLE ACCESS BY INDEX ROWID TAB_B (cr=2 pr=0 pw=0 time=74 us)
          1     INDEX UNIQUE SCAN TAB_B_U1 (cr=1 pr=0 pw=0 time=42 us)(Object ID 10438013)
         10    TABLE ACCESS BY INDEX ROWID TAB_A (cr=4 pr=0 pw=0 time=54 us)
         10     INDEX RANGE SCAN TAB_A_N1 (cr=2 pr=0 pw=0 time=143 us)(Object ID 10438012)
    

2. 서브쿼리 파라미터

1) 서브쿼리 관련 파라미터의 버전별 변화 과정

이름 8i 9i 10g 설명
_unnest_notexists_sq n/a single n/a 하나 이상의 테이블을 가지는 NOT EXISTS 서브쿼리를 가능하면 UNNEST
_unnest_subqerty false true true 상관 서브쿼리 unnesting을 가능하게 함
_ordered_semi-join true true true 정렬된 세미 조인(EXISTS) 서브쿼리를 가능하게 함
_cost_equality_semi_join n/a true true 등식(=equality) 세미 조인(EXISTS)의 비용계산을 활성화
_always_anti_join nested_loops choose choose 가능하면 anti-JOIN(NOT EXISTS) 방식을 항상 사용
_always_semi_join standard choose choose 가능하면 semi-JOIN(EXISTS) 방식을 항상 사용
_optimizer_correct_sq_selectivity n/a n/a true 서브쿼리 선택도의 정확한 계산을 강제
_optimizer_squ_bottomup n/a n/a true Bottom-up 방식으로 서브쿼리 unnesting을 가능하게 함
_distinct_view_unnesting n/a n/a false in subquery를 'select distinct' 뷰로 unnesting 가능하게 함
_right_outer_hash_enable n/a n/a true right outer(semi와 anti 해시 조인을 포함해서) 해시 조인을 가능하게 함
_remove_aggr_subquery n/a n/a true 포함된 집계 서브쿼리의 제거를 가능하게 함

3. 서브쿼리의 분류

카테고리 특징
상관/비상관 상관 서브쿼리는 outer 쿼리 블록에 있는 컬럼을 참조한다. 상관 서브쿼리는 대개 조인으로 변환될 수 있다. 비상관 서브쿼리는 선행 서브쿼리가 될 가능성이 있다.
단순/복잡 단순한 서브쿼리는 단일 테이블만을 포함한다. 복잡한 서브쿼리는 여러 개의 테이블을 포함하며, 조인형태 또는 서브쿼리 안에 또 다른 서브쿼리를 갖는 형태이다. 서브쿼리가 복잡할 때는 적용되지 않고 단순할 때만 옵티마이저가 수행하는 기능들이 있다.
집계 단순한(단일 테이블) 서브쿼리일지라도 그 안에 어떤 집계연산을 포함한다면 옵티마이저가 그들을 변환하지 못하게 하는 제약이 있을 수 있다.
단일행 (기껏해야) 단일 로우를 리턴하는 서브쿼리. 이는 대개 그 서브쿼리가 쿼리 전체의 선행 포인트가 될 수 있음을 의미한다.
IN / EXISTS NOT IN 서브쿼리는 NOT EXISTS 서브쿼리로 재작성될 수 있다. 그런 후에 어떤 제약 하에서 안티 조인으로 변환될 수 있다. 'NOT IN'이 'IN'의 반대말이 아니며, 특히 NULL 컬럼인 경우 이 점에 주의해야 한다.

4. Semi JOIN

세미 조인이란 선행 테이블의 한 로우가 후행 테이블에서 한 로우와 조인에 성공하면
그 선행 로우에 대해서 더는 후행 테이블로의 프로세싱을 진행하지 않고 멈추기 때문에
리소스 사용을 절약하는 효과가 있으며 IN 또는 EXISTS 같은 구문의 효율적인 처리를
위해 나온 방식이다.

9i까지는 SQL 구문에서 선행 테이블이 먼저 드라이빙 되도록 구성되어 있다면 옵티마이저가
선행 테이블의 데이터가 많다 할지라도 실행계획을 그렇게 구성하였지만 10g로 오면서
선행 테이블의 데이터가 많다면 후행 테이블을 먼저 드라이빙하고 실행계획에서는 'SEMI'에서
'RIGHT SEMI' 문구가 보인다.


-- 1. 테이블 생성 및 통계정보 생성
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;

CREATE TABLE EMP (DEPT_NO NUMBER	NOT NULL,
                  SAL     NUMBER,
                  EMP_NO  NUMBER,
                  PADDING VARCHAR2(60),
                  CONSTRAINT E_PK PRIMARY KEY(EMP_NO))
;

INSERT INTO EMP
SELECT MOD(ROWNUM, 6),
       ROWNUM,
       ROWNUM,
       RPAD('X', 60)
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 20000
;


CREATE TABLE DEPT (DEPT_NO    NUMBER(6),
                   DEPT_GROUP NUMBER)
;

INSERT INTO DEPT VALUES(0, 1);
INSERT INTO DEPT VALUES(1, 1);
INSERT INTO DEPT VALUES(2, 1);
INSERT INTO DEPT VALUES(3, 2);
INSERT INTO DEPT VALUES(4, 2);
INSERT INTO DEPT VALUES(5, 2);
COMMIT;


ALTER TABLE DEPT ADD CONSTRAINT D_UK UNIQUE (DEPT_NO);
ALTER TABLE DEPT MODIFY DEPT_NO NOT NULL;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          => 'EMP',
                                  CASCADE	       => TRUE,
                                  ESTIMATE_PERCENT => NULL, 
                                  METHOD_OPT       =>'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'DEPT',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

-- 2. 9i Semi Join
SELECT * FROM V$VERSION;

BANNER                                                                
----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '9.2.0.8';

EXPLAIN PLAN FOR
SELECT EMP.*
FROM   EMP
WHERE  EXISTS (SELECT 1
               FROM   DEPT
               WHERE  EMP.DEPT_NO = DEPT.DEPT_NO
               AND    DEPT.DEPT_GROUP = 1)
;

@XPLAN;
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   751K|    54 |
|*  1 |  HASH JOIN SEMI    |      | 10000 |   751K|    54 |
|   2 |   TABLE ACCESS FULL| EMP  | 20000 |  1406K|    36 |
|*  3 |   TABLE ACCESS FULL| DEPT |     3 |    15 |     4 |
-----------------------------------------------------------
;

-- 3. 10g Semi Join
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';

EXPLAIN PLAN FOR
SELECT EMP.*
FROM   EMP
WHERE  EXISTS (SELECT 1
               FROM   DEPT
               WHERE  EMP.DEPT_NO = DEPT.DEPT_NO
               AND    DEPT.DEPT_GROUP = 1)
;

@XPLAN;
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 10000 |   751K|    70   (2)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|      | 10000 |   751K|    70   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | DEPT |     3 |    15 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 |
-----------------------------------------------------------------------------
;

5. Anti Join

안티 조인은 세미 조인의 부정형으로 보면 된다.

EXPLAIN PLAN FOR
SELECT EMP.*
FROM   EMP
WHERE  EMP.DEPT_NO NOT IN (SELECT DEPT.DEPT_NO
                           FROM   DEPT
                           WHERE  DEPT.DEPT_GROUP = 2)
;

@XPLAN;
----------------------------------------------------------------------------- 
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |      | 10000 |   751K|    70   (2)| 00:00:01 | 
|*  1 |  HASH JOIN RIGHT ANTI|      | 10000 |   751K|    70   (2)| 00:00:01 | 
|*  2 |   TABLE ACCESS FULL  | DEPT |     3 |    15 |     5   (0)| 00:00:01 | 
|   3 |   TABLE ACCESS FULL  | EMP  | 20000 |  1406K|    65   (2)| 00:00:01 | 
----------------------------------------------------------------------------- 

6. NULL과 NOT IN 그리고 NOT EXISTS(reference : http://www.ocmkorea.com)

서브쿼리에서 NOT EXISTS를 NOT IN으로 변환 시 조인키에 NULL값이 허용되어 있고
NULL값이 들어가 있다면 데이터가 다르게 나올 수 있다.

1) NOT IN


-- 1. 테이블 생성 및 통계정보 생성
DROP TABLE TAB1 PURGE;
DROP TABLE TAB2 PURGE;

CREATE TABLE TAB1 AS
SELECT '1111' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '2222' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '3333' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '4444' COL1, '9802' COL2 FROM DUAL UNION ALL
SELECT '4444' COL1, '9804' COL2 FROM DUAL UNION ALL
SELECT '5555' COL1, NULL   COL2 FROM DUAL UNION ALL
SELECT '6666' COL1, '9807' COL2 FROM DUAL UNION ALL
SELECT NULL   COL1, '9809' COL2 FROM DUAL
;

CREATE TABLE TAB2 AS
SELECT '1111' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '2222' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '3333' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT 'KKKK' COL1, NULL   COL2 FROM DUAL UNION ALL
SELECT '5555' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT NULL   COL1, '9809' COL2 FROM DUAL
;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                  TABNAME          => 'TAB1',
                                  CASCADE	       => TRUE,
                                  ESTIMATE_PERCENT => NULL, 
                                  METHOD_OPT       =>'FOR ALL COLUMNS SIZE 1');
END;
/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME           => USER,
                                  TABNAME           => 'TAB2',
                                  CASCADE           => TRUE,
                                  ESTIMATE_PERCENT  => NULL, 
                                  METHOD_OPT        =>'FOR ALL COLUMNS SIZE 1');
END;
/

-- 2. NOT IN 테스트
SELECT *
FROM   TAB1
WHERE  COL2 NOT IN (SELECT COL2 
                    FROM   TAB2)
;

no rows selected

TAB1

COL1 COL2
1111 9801
2222 9801
3333 9801
4444 9802
4444 9804
5555 NULL
6666 9807
NULL 9809

TAB2

COL1 COL2
1111 9801
2222 9801
3333 9801
KKK NULL
5555 9801
NULL 9809

이렇게 구성되어 있는 테이블을 COL2 컬럼으로 NOT IN인 것들을 찾을 때 데이터를 한건도 찾을 수 없었다.
예상대로라면 TAB2의 DISTICT.COL2인 값을 제외한 TAB1.COL1을 찾으면 되므로 '9802, 9804, NULL, 9807'
4개의 로우가 나와야 하나 한건도 나오지 않았는데 그 이유는 아래와 같다.

옵티마이저는 TAB2.COL2 컬럼의 NOT IN 조건에 만족하는 값을 찾기 위해 먼저 DISTINCT를 해서
'9801, NULL, 9809' 값을 가져온다. 그 다음 그 값을 가지고 TAB1.COL2 값과 아래처럼 비교를 한다.

  • COL2 <> '9801' AND COL2 <> NULL AND COL2 '9809'

여기서 COL2 <> '9801' 조건은 TRUE이나 COL2 <> NULL 조건은 FALSE이므로 이 조건은 모두 FALSE가 되고
이로 인해 데이터를 한건도 가져오지 못한다.

그러므로 값을 취하려면 서브쿼리에서 COL2 값이 IS NOT NULL인 값들만 취하도록 변경해 주어야 한다.

SELECT *
FROM   TAB1
WHERE  COL2 NOT IN (SELECT COL2 
                    FROM   TAB2
                    WHERE  COL2 IS NOT NULL)
;

COL1     COL2    
-------- --------
4444     9802    
4444     9804    
6666     9807    

TAB2.COL2에서 IS NOT NULL값을 취했기 때문에 TAB1.COL1에서도 NULL값은 가져오지 않는다.

2) NOT EXISTS

EXISTS는 드라이빙이 아닌 필터 방식으로 처리하므로 데이터를 가져올 수 있다.

SELECT *
FROM TAB1
WHERE NOT EXISTS (SELECT 1
FROM TAB2
WHERE TAB2.COL2 = TAB1.COL2)
ORDER BY COL1
;

COL1 COL2
-------- --------
4444 9804
4444 9802
5555
6666 9807

3) 정리

그러므로 NOT IN을 NOT EXISTS로 변경 시 이 점을 반드시 유의하여 접근해야 한다.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 7월 20, 2009

    허용운 says:

    아 난 형이 작성한 문서가 너무 좋아 완전 이해 쏙쏙에 테스트 하기도 좋게 만들어 주자나.. 형은 문서의 대가로 인정하겠삼!!! ㅋㅋ

    아 난 형이 작성한 문서가 너무 좋아 완전 이해 쏙쏙에
    테스트 하기도 좋게 만들어 주자나..
    형은 문서의 대가로 인정하겠삼!!! ㅋㅋ

    1. 7월 23, 2009

      강정식 says:

      할룽 용운아.. 오랜만이네? ^^ 형이 언제나 얘기하자나.. 문서 작업은 힘들다고.. ㅋㅋ 빨리 도망치고 싶은 생각 뿐이야..

      할룽 용운아..
      오랜만이네? ^^ 형이 언제나 얘기하자나.. 문서 작업은 힘들다고.. ㅋㅋ
      빨리 도망치고 싶은 생각 뿐이야..