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

제2절 쿼리변환




제 2 절 쿼리 변환

쿼리 변환?

  • 실행계획을 생성하고 비용을 계산하기에 앞서 사용자 SQL을 최적화에 유리한 형태로 재작성함

방식?

휴리스틱( Heuristic ) 쿼리 변환 : 일종의 규칙 기반의 최적화 기법( 불필요한 부분 제거 )

비용기반 쿼리 변환 : 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.

서브쿼리 Unnesting( SSU : Simple Subquery Unnesting ) ( 9i : Heuristic, 10g : Cost-Base )

  • 서브쿼리를 메인 쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가 할 수 있다.
  • 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아진다.
    
    SQL>  SELECT  *
      2     FROM EMP A
      3    WHERE EXISTS(SELECT 'X' FROM DEPT WHERE DEPTNO = A.DEPTNO)  -- JE( Join Elimination( hint : ELIMINATE_JOIN, NO_ELIMINATE_JOIN ) )
      4      AND SAL  > (SELECT AVG(SAL)
      5                    FROM EMP B
      6                   WHERE EXISTS(SELECT /*+   */ 'O'
      7                                  FROM SALGRADE S
      8                                 WHERE B.SAL BETWEEN LOSAL AND HISAL
      9                                   AND GRADE = 4) );
      
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |          |      1 |        |      4 |00:00:00.01 |      16 |    |  |          |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| EMP      |      1 |      1 |      4 |00:00:00.01 |      16 |    |  |          |
    |*  2 |   INDEX FULL SCAN           | FK_EMP   |      1 |     14 |     14 |00:00:00.01 |       2 |    |  |          |
    |   3 |   SORT AGGREGATE            |          |      1 |      1 |      1 |00:00:00.01 |      12 |    |  |          |
    |   4 |    MERGE JOIN SEMI          |          |      1 |     14 |      5 |00:00:00.01 |      12 |    |  |          |
    |   5 |     SORT JOIN               |          |      1 |     14 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
    |   6 |      TABLE ACCESS FULL      | EMP      |      1 |     14 |     14 |00:00:00.01 |       6 |    |  |          |
    |*  7 |     FILTER                  |          |     14 |        |      5 |00:00:00.01 |       6 |    |  |          |
    |*  8 |      SORT JOIN              |          |     14 |      1 |      6 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
    |   9 |       VIEW                  | VW_SQ_1  |      1 |      1 |      1 |00:00:00.01 |       6 |    |  |          |
    |* 10 |        TABLE ACCESS FULL    | SALGRADE |      1 |      1 |      1 |00:00:00.01 |       6 |    |  |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("SAL">)
       2 - filter("A"."DEPTNO" IS NOT NULL)
       7 - filter("B"."SAL"<="ITEM_2")
       8 - access(INTERNAL_FUNCTION("B"."SAL")>=INTERNAL_FUNCTION("ITEM_1"))
           filter(INTERNAL_FUNCTION("B"."SAL")>=INTERNAL_FUNCTION("ITEM_1"))
      10 - filter("GRADE"=4)
    
    

서브 쿼리의 처리의 옵티마이저의 선택..

  • 조인문으로 변환 후 최적화( Subquery Unnesting == Subquery Flattening ) ( 다양한 실행계획 )
  • 서브쿼리를 Unnesting 하지 않고 원래대로 둔 상태에서 최적화 한다.( Filter ) ( 제한적 실행계획 )

서브쿼리 Unnesting

FILTER

SQL> SELECT * FROM V$VERSION WHERE ROWNUM <= 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

-- JE( Join Elimination )ㅡㅡ^
SQL> SELECT COUNT(*)
  2    FROM EMP
  3   WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT );
-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  2 |   INDEX FULL SCAN| FK_EMP |      1 |     14 |     14 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------

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

   2 - filter("DEPTNO" IS NOT NULL)

SQL> ALTER TABLE EMP  DROP CONSTRAINT FK_DEPTNO;

테이블이 변경되었습니다.

-- FILTER
SQL> SELECT COUNT(*)
  2    FROM EMP
  3   WHERE DEPTNO IN ( SELECT /*+ NO_UNNEST */ DEPTNO FROM DEPT );
-----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  SORT AGGREGATE     |         |      1 |      1 |      1 |00:00:00.01 |       9 |
|*  2 |   FILTER            |         |      1 |        |     14 |00:00:00.01 |       9 |
|   3 |    TABLE ACCESS FULL| EMP     |      1 |     14 |     14 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------

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

   2 - filter( IS NOT NULL) <-- ??
   4 - access("DEPTNO"=:B1) <-- 별도의 서브플랜으로 최적화..

SQL>  EXPLAIN PLAN FOR
  2   SELECT COUNT(*)
  3    FROM EMP
  4   WHERE DEPTNO IN ( SELECT /*+ NO_UNNEST */ DEPTNO FROM DEPT );

SQL>  SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( NULL ) );
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |         |     1 |     3 |            |          |
|*  2 |   FILTER            |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPTNO"=:B1))
   4 - access("DEPTNO"=:B1)

Unnesting 과정


-- 1. VIEW
SQL> SELECT /*+ NO_MERGE( A ) */  COUNT(*)
  2    FROM (SELECT DEPTNO FROM DEPT) A, EMP B
  3   WHERE A.DEPTNO = B.DEPTNO;
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
|   2 |   NESTED LOOPS     |         |      1 |     14 |     14 |00:00:00.01 |       3 |      1 |
|   3 |    VIEW            |         |      1 |      4 |      4 |00:00:00.01 |       1 |      0 |
|   4 |     INDEX FULL SCAN| PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |      0 |
|*  5 |    INDEX RANGE SCAN| FK_EMP  |      4 |      4 |     14 |00:00:00.01 |       2 |      1 |
-------------------------------------------------------------------------------------------------

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

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

-- 2. MERGE
SQL> SELECT /*+  MERGE( A ) */  COUNT(*)
  2    FROM (SELECT DEPTNO FROM DEPT) A, EMP B
  3   WHERE A.DEPTNO = B.DEPTNO
  4  ;
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   NESTED LOOPS     |         |      1 |     14 |     14 |00:00:00.01 |       3 |
|   3 |    INDEX FULL SCAN | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |
|*  4 |    INDEX RANGE SCAN| FK_EMP  |      4 |      4 |     14 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------

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

   4 - access("DEPTNO"="B"."DEPTNO")

-- 3. Unnesting 
SQL> SELECT COUNT(*)
  2    FROM EMP
  3   WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT );
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
|   2 |   NESTED LOOPS     |         |      1 |     14 |     14 |00:00:00.01 |       3 |      1 |
|   3 |    INDEX FULL SCAN | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |      1 |
|*  4 |    INDEX RANGE SCAN| FK_EMP  |      4 |      4 |     14 |00:00:00.01 |       2 |      0 |
-------------------------------------------------------------------------------------------------

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

   4 - access("DEPTNO"="DEPTNO")

  • 최근 옵티마이저는 서브쿼리를 Unnesting했을 때 쿼리 수행 비용이 더 낮은지를 비교해 보고 적용 여부를 판단하는 쪽으로 발전하고 있다.
  • unnest : 서브쿼리를 Unnestiong 함으로써 조인방식으로 최적화하도록 유도한다.
  • no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.

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

  • (1) PK/Unique 제약 또는 Unique 인덱스가 없는 서브쿼리 쪽 테이블이 먼저 드라이빙된다면,
    먼저 Sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
  • (2) 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인( Semi Join ) 방식으로 조인한다.
    이것이 세미 조인(Semi Join)이 탄생하게 된 배경이다.
<사례1>

SELECT *                           
 FROM DEPT D                                                                
WHERE DEPTNO IN (SELECT DEPTNO  FROM EMP E ); 

--(1) EMP 테이블 드라이빙
SQL>  SELECT /*+ QB_NAME( MAIN )  LEADING( E@SUB ) */ *
  2    FROM DEPT D
  3   WHERE DEPTNO IN (SELECT /*+ QB_NAME( SUB ) UNNEST */  DEPTNO  FROM EMP E );
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      3 |00:00:00.01 |       6 |    |  |          |
|   1 |  NESTED LOOPS                |         |      1 |        |      3 |00:00:00.01 |       6 |    |  |          |
|   2 |   NESTED LOOPS               |         |      1 |      3 |      3 |00:00:00.01 |       3 |    |  |          |
|   3 |    SORT UNIQUE               |         |      1 |     14 |      3 |00:00:00.01 |       1 |  2048 |  2048 | 2048  (0)|
|   4 |     INDEX FULL SCAN          | FK_EMP  |      1 |     14 |     14 |00:00:00.01 |       1 |    |  |          |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       2 |    |  |          |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |00:00:00.01 |       3 |    |  |          |
-----------------------------------------------------------------------------------------------------------------------------

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

   5 - access("DEPTNO"="DEPTNO")

--(2) DEPT 테이블 드라이브
SQL>  SELECT /*+ QB_NAME( MAIN )  LEADING( D@SUB ) */ *
  2    FROM EMP E
  3   WHERE DEPTNO IN (SELECT  /*+ QB_NAME( SUB )  UNNEST */ DEPTNO  FROM DEPT D );
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                |                 |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   2 |   NESTED LOOPS               |                 |      1 |     14 |     14 |00:00:00.01 |       4 |       |       |          |
|   3 |    SORT UNIQUE               |                 |      1 |      4 |      4 |00:00:00.01 |       1 |  2048 |  2048 | 2048  (0)|
|   4 |     INDEX FULL SCAN          | DEPT_DEPTNO_IDX |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | FK_EMP          |      4 |      5 |     14 |00:00:00.01 |       3 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID| EMP             |     14 |      4 |     14 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("DEPTNO"="DEPTNO")


<사례2>

SELECT *                           
 FROM EMP E                                                         
WHERE DEPTNO IN (SELECT DEPTNO  FROM DEPT D ); 

-- (1) DEPT 테이블 드라이브
SQL> ALTER TABLE DEPT DROP PRIMARY KEY;

테이블이 변경되었습니다.

SQL> CREATE INDEX DEPT_DEPTNO_IDX ON DEPT( DEPTNO );

인덱스가 생성되었습니다.

SQL> SELECT /*+ QB_NAME( MAIN )  LEADING( D@SUB ) */ *
  2   FROM EMP E
  3  WHERE DEPTNO IN (SELECT  /*+ QB_NAME( SUB )  UNNEST */ DEPTNO  FROM DEPT D );
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                |                 |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   2 |   NESTED LOOPS               |                 |      1 |     14 |     14 |00:00:00.01 |       4 |       |       |          |
|   3 |    SORT UNIQUE               |                 |      1 |      4 |      4 |00:00:00.01 |       1 |  2048 |  2048 | 2048  (0)|
|   4 |     INDEX FULL SCAN          | DEPT_DEPTNO_IDX |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | FK_EMP          |      4 |      5 |     14 |00:00:00.01 |       3 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID| EMP             |     14 |      4 |     14 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("DEPTNO"="DEPTNO")


-- (1) 위 쿼리 QT
SQL>  SELECT /*+ LEADING( D ) */ *
  2   FROM (SELECT  /*+  NO_MERGE */ DISTINCT DEPTNO  FROM DEPT D ) D, EMP E
  3  WHERE E.DEPTNO = D.DEPTNO      ;
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |     14 |00:00:00.01 |      11 |    |  |          |
|   1 |  NESTED LOOPS                |        |      1 |        |     14 |00:00:00.01 |      11 |    |  |          |
|   2 |   NESTED LOOPS               |        |      1 |     14 |     14 |00:00:00.01 |       9 |    |  |          |
|   3 |    VIEW                      |        |      1 |      4 |      4 |00:00:00.01 |       6 |    |  |          |
|   4 |     HASH UNIQUE              |        |      1 |      4 |      4 |00:00:00.01 |       6 |  1518K|  1518K|  874K (0)|
|   5 |      TABLE ACCESS FULL       | DEPT   |      1 |      4 |      4 |00:00:00.01 |       6 |    |  |          |
|*  6 |    INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       3 |    |  |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| EMP    |     14 |      4 |     14 |00:00:00.01 |       2 |    |  |          |
----------------------------------------------------------------------------------------------------------------------------

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

   6 - access("E"."DEPTNO"="D"."DEPTNO")


--(2) EMP 테이블 드라이브
SQL>  SELECT /*+ QB_NAME( MAIN )  LEADING( E@MAIN ) */ *
  2    FROM EMP E
  3   WHERE DEPTNO IN (SELECT  /*+ QB_NAME( SUB )  UNNEST */ DEPTNO  FROM DEPT D );
------------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |      1 |        |     14 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS SEMI |                 |      1 |     14 |     14 |00:00:00.01 |       9 |
|   2 |   TABLE ACCESS FULL| EMP             |      1 |     14 |     14 |00:00:00.01 |       7 |
|*  3 |   INDEX RANGE SCAN | DEPT_DEPTNO_IDX |      3 |      4 |      3 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"="DEPTNO")

3. 뷰 Merging

<쿼리1>

SELECT *
  FROM (SELECT * FROM EMP WHERE JOB = 'SALESMAN') A
     , (SELECT * FROM DEPT WHERE LOC = 'CHICAGO') A
WHERE A.DEPTNO = B.DEPTNO

<쿼리2>

SELECT *
  FROM EMP A, DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'SALESMAN'
   AND B.LOC = 'CHICAGO'

VIEW


-- 뷰 생성
SQL> CREATE OR REPLACE VIEW EMP_SALESMAN
  2  AS
  3  SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
  4    FROM EMP
  5   WHERE JOB = 'SALESMAN';

-- NO_MERGE
SQL>  SELECT /*+ NO_MERGE( E )  LEADING( E ) */ E.EMPNO, E.ENAME, E.JOB, E.MGR, E.SAL, D.DNAME
  2    FROM EMP_SALESMAN E, DEPT D
  3   WHERE D.DEPTNO = E.DEPTNO
  4     AND E.SAL >= 1500;
----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |      2 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                |                 |      1 |        |      2 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS               |                 |      1 |      3 |      2 |00:00:00.01 |      10 |
|   3 |    VIEW                      | EMP_SALESMAN    |      1 |      3 |      2 |00:00:00.01 |       7 |
|*  4 |     TABLE ACCESS FULL        | EMP             |      1 |      3 |      2 |00:00:00.01 |       7 |
|*  5 |    INDEX RANGE SCAN          | DEPT_DEPTNO_IDX |      2 |      1 |      2 |00:00:00.01 |       3 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT            |      2 |      1 |      2 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------

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

   4 - filter(("JOB"='SALESMAN' AND "SAL">=1500))
   5 - access("D"."DEPTNO"="E"."DEPTNO")

-- MERGE
SQL> SELECT /*+ MERGE( E )  LEADING( E )  USE_NL( D ) */ E.EMPNO, E.ENAME, E.JOB, E.MGR, E.SAL, D.DNAME
  2   FROM EMP_SALESMAN E, DEPT D
  3  WHERE D.DEPTNO = E.DEPTNO
  4     AND E.SAL >= 1500;
----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |      2 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                |                 |      1 |        |      2 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS               |                 |      1 |      3 |      2 |00:00:00.01 |      10 |
|*  3 |    TABLE ACCESS FULL         | EMP             |      1 |      3 |      2 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | DEPT_DEPTNO_IDX |      2 |      1 |      2 |00:00:00.01 |       3 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT            |      2 |      1 |      2 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------

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

   3 - filter(("JOB"='SALESMAN' AND "SAL">=1500))
   4 - access("D"."DEPTNO"="DEPTNO")

  • 위 같은 단순한 뷰는 Merging하더라도 성능이 나빠지지 않는다.
  • 복잡한 연산을 포함하는 뷰는 Merging하면 오히려 성능이 더 나빠질 수 도있다.( group by 절, select-list에 distinct 연산자 포함 )

뷰 Merging이 불가능한 경우

  • 집합 연산자( union, union all, intersect, minus )
  • connect by절
  • rownum pseudo 칼럼
  • select-list에 집계 함수( avg, count, max, min, sum) 사용
    SQL> SELECT /*+   OPTIMIZER_FEATURES_ENABLE('10.2.0.3')  */ *
      2    FROM DEPT D
      3           , (SELECT DEPTNO, SUM( SAL ) AS SAL
      4                FROM EMP E
      5              GROUP BY DEPTNO ) E
      6  WHERE D.LOC = 'DALLAS'
      7       AND D.DEPTNO = E.DEPTNO ;
    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       8 |    |  |          |
    |   1 |  HASH GROUP BY               |        |      1 |      1 |      1 |00:00:00.01 |       8 |   817K|   817K|  484K (0)|
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      5 |      5 |00:00:00.01 |       8 |    |  |          |
    |   3 |    NESTED LOOPS              |        |      1 |      5 |      7 |00:00:00.01 |       7 |    |  |          |
    |*  4 |     TABLE ACCESS FULL        | DEPT   |      1 |      1 |      1 |00:00:00.01 |       6 |    |  |          |
    |*  5 |     INDEX RANGE SCAN         | FK_EMP |      1 |      5 |      5 |00:00:00.01 |       1 |    |  |          |
    ----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("D"."LOC"='DALLAS')
       5 - access("D"."DEPTNO"="DEPTNO")
    
    
  • 분석 함수( Analytic Function )

4. 조건절 Pushing

  • 옵티마지어가 뷰를 처리함에 있어 1차적으로 뷰 Merging을 고려하지만, 조건절 Pushing을 시도할 수도 있다.

조건절(Predicate) Pushdown : 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함

조건절(Prediate) Pullup : 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 PushDown 하는 데 사용함

조인 조건( Join Predicate) Pushdown : NL Join 수쟁 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽( =right side) 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함

가. 조건절(Predicate) Pushdown

  • 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함
    
    
    -- CASE 1
    SQL> SELECT /*+ NO_MERGE( E ) */ DEPTNO, AVG_SAL
      2    FROM (SELECT DEPTNO, AVG(SAL) AS AVG_SAL
      3            FROM EMP
      4      GROUP BY DEPTNO ) E
      5   WHERE DEPTNO = 30;
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |      1 |        |      1 |00:00:00.01 |       2 |
    |   1 |  VIEW                         |        |      1 |      1 |      1 |00:00:00.01 |       2 |
    |   2 |   SORT GROUP BY NOSORT        |        |      1 |      1 |      1 |00:00:00.01 |       2 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      6 |      6 |00:00:00.01 |       2 |
    |*  4 |     INDEX RANGE SCAN          | FK_EMP |      1 |      6 |      6 |00:00:00.01 |       1 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("DEPTNO"=30)
    
    -- CASE 2
    SQL> SELECT D.DEPTNO, D.DNAME, E.AVG_SAL
      2    FROM (SELECT DEPTNO, AVG(SAL) AS AVG_SAL
      3            FROM EMP
      4      GROUP BY DEPTNO) E
      5        , DEPT D
      6   WHERE D.DEPTNO = E.DEPTNO
      7     AND D.DEPTNO = 30
          --AND E.DEPTNO = 30 <-- 조건절 이행
      8  ;
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                 |      1 |        |      1 |00:00:00.01 |    5 |          |       |          |
    |   1 |  NESTED LOOPS                  |                 |      1 |      1 |      1 |00:00:00.01 |    5 |          |       |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT            |      1 |      1 |      1 |00:00:00.01 |    3 |          |       |          |
    |*  3 |    INDEX RANGE SCAN            | DEPT_DEPTNO_IDX |      1 |      1 |      1 |00:00:00.01 |    2 |          |       |          |
    |   4 |   VIEW                         |                 |      1 |      1 |      1 |00:00:00.01 |    2 |          |       |          |
    |   5 |    SORT GROUP BY               |                 |      1 |      1 |      1 |00:00:00.01 |    2 |  2048 |  2048 | 2048  (0)|
    |   6 |     TABLE ACCESS BY INDEX ROWID| EMP             |      1 |      6 |      6 |00:00:00.01 |    2 |          |       |          |
    |*  7 |      INDEX RANGE SCAN          | FK_EMP          |      1 |      6 |      6 |00:00:00.01 |    1 |          |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("D"."DEPTNO"=30)
       7 - access("DEPTNO"=30)
    
    

나. 조건절( Predicate ) Pullup

  • 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내는것을 말함.
    조건절( Predicate ) Pullup
    
    SQL> SELECT *
      2    FROM (SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO ) e1
      3           , (SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO ) e2
      4   WHERE E1.DEPTNO = E2.DEPTNO;
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |        |      1 |        |      1 |00:00:00.01 |       4 |    |          |          |
    |*  1 |  HASH JOIN                     |        |      1 |      1 |      1 |00:00:00.01 |       4 |   963K|   963K|  382K (0)|
    |   2 |   VIEW                         |        |      1 |      1 |      1 |00:00:00.01 |       2 |    |          |          |
    |   3 |    HASH GROUP BY               |        |      1 |      1 |      1 |00:00:00.01 |       2 |   899K|   899K|  720K (0)|
    |   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      3 |00:00:00.01 |       2 |    |          |          |
    |*  5 |      INDEX RANGE SCAN          | FK_EMP |      1 |      3 |      3 |00:00:00.01 |       1 |    |          |          |
    |   6 |   VIEW                         |        |      1 |      1 |      1 |00:00:00.01 |       2 |    |          |          |
    |   7 |    HASH GROUP BY               |        |      1 |      1 |      1 |00:00:00.01 |       2 |   838K|   838K|  722K (0)|
    |   8 |     TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      3 |00:00:00.01 |       2 |    |          |          |
    |*  9 |      INDEX RANGE SCAN          | FK_EMP |      1 |      3 |      3 |00:00:00.01 |       1 |    |          |          |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("E1"."DEPTNO"="E2"."DEPTNO")
       5 - access("DEPTNO"=10)
       9 - access("DEPTNO"=10)
    
    
    QT
    
    SELECT *                                                                        
      FROM (SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO ) e1 
             , (SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO ) e2     
     WHERE E1.DEPTNO = E2.DEPTNO;  
     
    

다. 조인 조건( Join Predicate ) Pushdown

  • 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것( NL JOIN )
    Oracle 11g
    
    SQL> SELECT /*+ LEADING( D )  USE_NL( E )  NO_MERGE( E )  PUSH_PRED( E )  */ D.DEPTNO, D.DNAME, E.AVG_SAL
      2    FROM DEPT D
      3       , (SELECT DEPTNO, AVG(SAL)  AVG_SAL FROM EMP GROUP BY DEPTNO ) E
      4   WHERE E.DEPTNO(+) = D.DEPTNO;
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |        |      1 |        |      4 |00:00:00.01 |      11 |    |          |          |
    |   1 |  NESTED LOOPS OUTER            |        |      1 |      4 |      4 |00:00:00.01 |      11 |    |          |          |
    |   2 |   TABLE ACCESS FULL            | DEPT   |      1 |      4 |      4 |00:00:00.01 |       7 |    |          |          |
    |   3 |   VIEW PUSHED PREDICATE        |        |      4 |      1 |      3 |00:00:00.01 |       4 |    |          |          |
    |   4 |    SORT GROUP BY               |        |      4 |      1 |      3 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
    |   5 |     TABLE ACCESS BY INDEX ROWID| EMP    |      4 |      5 |     14 |00:00:00.01 |       4 |    |          |          |
    |*  6 |      INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       2 |    |          |          |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - access("DEPTNO"="D"."DEPTNO")
    
    
    Oracle 10g
    
    SQL>  SELECT /*+   OPTIMIZER_FEATURES_ENABLE('10.2.0.3') LEADING( D )  USE_NL( E )  NO_MERGE( E )  PUSH_PRED( E )  */ D.DEPTNO, D.DNAME, E.AVG_SAL
      2    FROM DEPT D
      3       , (SELECT DEPTNO, AVG(SAL)  AVG_SAL FROM EMP GROUP BY DEPTNO ) E
      4   WHERE E.DEPTNO(+) = D.DEPTNO;
    ------------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |      1 |        |      4 |00:00:00.01 |      13 |       |       |          |
    |   1 |  NESTED LOOPS OUTER  |      |      1 |      4 |      4 |00:00:00.01 |      13 |       |       |          |
    |   2 |   TABLE ACCESS FULL  | DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
    |*  3 |   VIEW               |      |      4 |      1 |      3 |00:00:00.01 |       6 |       |       |          |
    |   4 |    SORT GROUP BY     |      |      4 |      3 |     12 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
    |   5 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       6 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("E"."DEPTNO"="D"."DEPTNO")
    
    
    Oracle 10g 스칼라 서브쿼리로 부분범위 처리
    
    SQL> SELECT /*+   OPTIMIZER_FEATURES_ENABLE('10.2.0.3') */D.DEPTNO, D.DNAME
      2           , (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AS AVG_SAL
      3    FROM DEPT D;
    -------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |      1 |        |      4 |00:00:00.01 |       7 |
    |   1 |  SORT AGGREGATE              |        |      4 |      1 |      4 |00:00:00.01 |       4 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      4 |      5 |     14 |00:00:00.01 |       4 |
    |*  3 |    INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       2 |
    |   4 |  TABLE ACCESS FULL           | DEPT   |      1 |      4 |      4 |00:00:00.01 |       7 |
    -------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("DEPTNO"=:B1)
    
    -- 집계합수가 여러개일때 문제 쿼리
    SQL> SELECT /*+  OPTIMIZER_FEATURES_ENABLE('10.2.0.3')    */ D.DEPTNO, D.DNAME
      2            , (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AS AVG_SAL
      3             , (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AS AVG_SAL
      4              , (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AS AVG_SAL
      5    FROM DEPT D;
    -------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |      1 |        |      4 |00:00:00.01 |       7 |
    |   1 |  SORT AGGREGATE              |        |      4 |      1 |      4 |00:00:00.01 |       4 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      4 |      5 |     14 |00:00:00.01 |       4 |
    |*  3 |    INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       2 |
    |   4 |  SORT AGGREGATE              |        |      4 |      1 |      4 |00:00:00.01 |       4 |
    |   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |      4 |      5 |     14 |00:00:00.01 |       4 |
    |*  6 |    INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       2 |
    |   7 |  SORT AGGREGATE              |        |      4 |      1 |      4 |00:00:00.01 |       4 |
    |   8 |   TABLE ACCESS BY INDEX ROWID| EMP    |      4 |      5 |     14 |00:00:00.01 |       4 |
    |*  9 |    INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       2 |
    |  10 |  TABLE ACCESS FULL           | DEPT   |      1 |      4 |      4 |00:00:00.01 |       7 |
    -------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("DEPTNO"=:B1)
       6 - access("DEPTNO"=:B1)
       9 - access("DEPTNO"=:B1)
    
    -- 위 쿼리 효율적을 Trace 로 확인 가능..
    SQL> SELECT /*+   OPTIMIZER_FEATURES_ENABLE('10.2.0.3') */ DEPTNO, DNAME
      2           , TO_NUMBER( SUBSTR( SAL, 1, 7 ) ) AVG_SAL
      3           , TO_NUMBER( SUBSTR( SAL, 8, 7 ) ) MIN_SAL
      4           , TO_NUMBER( SUBSTR( SAL, 15 ) ) MAX_SAL
      5   FROM (SELECT /*+ NO_MERGE */ D.DEPTNO, D.DNAME
      6                        , (SELECT LPAD( AVG(SAL), 7 ) || LPAD(MIN(SAL), 7) || MAX(SAL)
      7                              FROM EMP WHERE DEPTNO = D.DEPTNO) AS SAL
      8                FROM DEPT D
      9             )  ;
    -------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |      1 |        |      4 |00:00:00.01 |      11 |
    |   1 |  SORT AGGREGATE              |        |      4 |      1 |      4 |00:00:00.01 |       4 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      4 |      5 |     14 |00:00:00.01 |       4 |
    |*  3 |    INDEX RANGE SCAN          | FK_EMP |      4 |      5 |     14 |00:00:00.01 |       2 |
    |   4 |  VIEW                        |        |      1 |      4 |      4 |00:00:00.01 |      11 |
    |   5 |   TABLE ACCESS FULL          | DEPT   |      1 |      4 |      4 |00:00:00.01 |       7 |
    -------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("DEPTNO"=:B1)
    
    
    

5. 조건절 이행

  • ( A = B ) 이고 ( B = C ) 이면 (A = C)이다.
    
    
    -- BATCH I/O
    SQL> SELECT *
      2    FROM DEPT D
      3           , EMP E
      4   WHERE E.JOB = 'MANAGER'
      5       AND E.DEPTNO = 10
      6       AND D.DEPTNO = E.DEPTNO;
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |    7 |
    |   1 |  NESTED LOOPS                 |                 |      1 |        |      1 |00:00:00.01 |    7 |
    |   2 |   NESTED LOOPS                |                 |      1 |      1 |      3 |00:00:00.01 |    5 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT            |      1 |      1 |      1 |00:00:00.01 |    3 |
    |*  4 |     INDEX RANGE SCAN          | DEPT_PK         |      1 |      1 |      1 |00:00:00.01 |    2 |
    |*  5 |    INDEX RANGE SCAN           | FK_EMP          |      1 |      3 |      3 |00:00:00.01 |    2 |
    |*  6 |   TABLE ACCESS BY INDEX ROWID | EMP             |      3 |      1 |      1 |00:00:00.01 |    2 |
    -----------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("D"."DEPTNO"=10)
       5 - access("E"."DEPTNO"=10)
       6 - filter("E"."JOB"='MANAGER')
    
     --
     SQL>  SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.3')   NO_NLJ_BATCHING(@SEL$1  E@SEL$1)  */ *
      2    FROM DEPT D
      3           , EMP E
      4   WHERE E.JOB = 'MANAGER'
      5       AND E.DEPTNO = 10
      6       AND D.DEPTNO = E.DEPTNO;
           -- AND D.DEPTNO = 10
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       6 |
    |   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |       6 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
    |*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      1 |      1 |00:00:00.01 |       4 |
    |*  5 |    INDEX RANGE SCAN          | FK_EMP  |      1 |      3 |      3 |00:00:00.01 |       2 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("D"."DEPTNO"=10)
       4 - filter("E"."JOB"='MANAGER')
       5 - access("E"."DEPTNO"=10)
    
    
    

6. 불필요한 조인 제거

  • PK/FK 존재시 조인제거( Join Elimination ) 함
    SQL>      SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDATE
      2         FROM DEPT D, EMP E
      3       WHERE D.DEPTNO = E.DEPTNO;
    -------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         |      1 |        |     14 |00:00:00.04 |      11 |      1 |
    |   1 |  NESTED LOOPS      |         |      1 |     14 |     14 |00:00:00.04 |      11 |      1 |
    |   2 |   TABLE ACCESS FULL| EMP     |      1 |     14 |     14 |00:00:00.04 |       7 |      1 |
    |*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     14 |      1 |     14 |00:00:00.01 |       4 |      0 |
    -------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("D"."DEPTNO"="E"."DEPTNO")
    
    ALTER TABLE SCOTT.EMP ADD ( 
    CONSTRAINT FK_DEPTNO 
    FOREIGN KEY (DEPTNO) 
    REFERENCES SCOTT.DEPT (DEPTNO));
    
    SQL>      SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDATE
      2         FROM DEPT D, EMP E
      3       WHERE D.DEPTNO = E.DEPTNO;
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |      1 |        |     14 |00:00:00.01 |       4 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |     14 |     14 |00:00:00.01 |       4 |
    |*  2 |   INDEX FULL SCAN           | FK_EMP |      1 |     14 |     14 |00:00:00.01 |       2 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("E"."DEPTNO" IS NOT NULL)
    
    
  • EMP의 DEPTNO 칼럼이 Null 허용 컬럼이면 결과가 틀리게 될 수 있다. ( null 조인 실패 )
  • filter("E"."DEPTNO" IS NOT NULL) <- 옵티마이저
    Outer 10g
    
    SQL>  SELECT /*+  OPTIMIZER_FEATURES_ENABLE('10.2.0.3') */ E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDATE
      2     FROM DEPT D, EMP E
      3   WHERE D.DEPTNO(+) = E.DEPTNO;
    ----------------------------------------------------------------------------------------
    | Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         |      1 |        |     14 |00:00:00.01 |      11 |
    |   1 |  NESTED LOOPS OUTER|         |      1 |     14 |     14 |00:00:00.01 |      11 |
    |   2 |   TABLE ACCESS FULL| EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |
    |*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     14 |      1 |     14 |00:00:00.01 |       4 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("D"."DEPTNO"="E"."DEPTNO")                        
     
    
    Outer 11g
    
    SQL> SELECT /*+  OPTIMIZER_FEATURES_ENABLE('11.2.0.3') */ E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDA
      2    FROM DEPT D, EMP E
      3  WHERE D.DEPTNO(+) = E.DEPTNO;
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       7 |
    |   1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
    ------------------------------------------------------------------------------------                                                                    
    
    
    SQL Server
    
    SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDA   
      FROM DOB.DEPT D RIGTH OUTER JOIN DBO.EMP E                                                                               
        ON D.DEPTNO = E.DEPTNO;                                                                      
    
    EMP 테이블, 스캔 수 1, 논리적 읽기 수 2, 물리적 읽 수 0, 미리 읽기 수 0.
    
    @,.@ ( P.587 )
    
    

7. OR 조건을 Union으로 변환

  • INDEX COMBINE 으로 풀리수도 있다.
    
    SQL> SELECT *
      2    FROM EMP
      3   WHERE JOB = 'CLERK' OR DEPTNO = 20;
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      4 |00:00:00.01 |       7 |
    |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      5 |      4 |00:00:00.01 |       7 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(("JOB"='CLERK' OR "DEPTNO"=2))
    
    SQL>      CREATE INDEX EMP_JOB_IDX ON EMP ( JOB );
    
    인덱스가 생성되었습니다.
    
    SQL> SELECT *
      2    FROM EMP
      3   WHERE JOB = 'CLERK' OR DEPTNO = 20;
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      7 |00:00:00.01 |       7 |
    |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      8 |      7 |00:00:00.01 |       7 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(("DEPTNO"=20 OR "JOB"='CLERK'))
    
    SQL>  SELECT /*+ USE_CONCAT */  *
      2     FROM EMP
      3   WHERE JOB = 'CLERK' OR DEPTNO = 20;
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |      1 |        |      7 |00:00:00.01 |       6 |
    |   1 |  CONCATENATION               |             |      1 |        |      7 |00:00:00.01 |       6 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      4 |      4 |00:00:00.01 |       4 |
    |*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX |      1 |      4 |      4 |00:00:00.01 |       2 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      4 |      3 |00:00:00.01 |       2 |
    |*  5 |    INDEX RANGE SCAN          | FK_EMP      |      1 |      5 |      5 |00:00:00.01 |       1 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("JOB"='CLERK')
       4 - filter(LNNVL("JOB"='CLERK'))
       5 - access("DEPTNO"=20)
    
    SQL> SELECT *
      2    FROM EMP
      3   WHERE JOB = 'CLERK'
      4   UNION ALL
      5  SELECT *
      6    FROM EMP
      7   WHERE DEPTNO = 20
      8     AND LNNVL( JOB = 'CLERK' );
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |      1 |        |      7 |00:00:00.01 |       6 |
    |   1 |  UNION-ALL                   |             |      1 |        |      7 |00:00:00.01 |       6 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      4 |      4 |00:00:00.01 |       4 |
    |*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX |      1 |      4 |      4 |00:00:00.01 |       2 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      4 |      3 |00:00:00.01 |       2 |
    |*  5 |    INDEX RANGE SCAN          | FK_EMP      |      1 |      5 |      5 |00:00:00.01 |       1 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("JOB"='CLERK')
    
    SQL>  SELECT /*+ NO_EXPAND */  *
      2    FROM EMP
      3   WHERE JOB = 'CLERK' OR DEPTNO = 20;
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      7 |00:00:00.01 |       7 |
    |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      8 |      7 |00:00:00.01 |       7 |
    ------------------------------------------------------------------------------------
    
       1 - filter(("DEPTNO"=20 OR "JOB"='CLERK'))
    
    
  • LNNVL( JOB = 'CLERK' ) : JOB < > 'CLERK' && JOB IS NULL ( 조건식이 false 이거나 Unknown 값일 때 true를 리턴한다.

8. 기타 쿼리 변환

가. 집합연산을 조인으로 변환

  • Intersect나 Minus값은 집합(Set) 연산을 조인 형태로 변환하는 것을 말한다.
    집합 연산
    
    SQL> SELECT JOB, MGR FROM EMP
      2  MINUS
      3  SELECT JOB, MGR FROM EMP
      4  WHERE DEPTNO = 10;
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |      1 |        |      5 |00:00:00.01 |       8 |    |  |          |
    |   1 |  MINUS                        |        |      1 |        |      5 |00:00:00.01 |       8 |    |  |          |
    |   2 |   SORT UNIQUE                 |        |      1 |     14 |      8 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
    |   3 |    TABLE ACCESS FULL          | EMP    |      1 |     14 |     14 |00:00:00.01 |       6 |    |  |          |
    |   4 |   SORT UNIQUE                 |        |      1 |      3 |      3 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
    |   5 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      3 |00:00:00.01 |       2 |    |  |          |
    |*  6 |     INDEX RANGE SCAN          | FK_EMP |      1 |      3 |      3 |00:00:00.01 |       1 |    |  |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - access("DEPTNO"=10)
    
    SQL> ;
      1  select ksppinm name,
      2         ksppstvl value,
      3         ksppstdf is_default,
      4         decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
      5         decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable, ksppdesc descrition
      6  from   sys.x$ksppi i, sys.x$ksppcv v
      7  where i.indx = v.indx
      8* and   i.ksppinm like '%&1%'
    SQL> /
    Enter value for 1: convert_set_to_join
    old   8: and   i.ksppinm like '%&1%'
    new   8: and   i.ksppinm like '%convert_set_to_join%'
    
    NAME                               VALUE           IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRITION
    ---------------------------------- --------------- --------------- --------------- --------------- ------------------------------
    _convert_set_to_join               FALSE           TRUE            true            immediate       enables conversion of set oper
                                                                                                       ator to join
    SQL>   SELECT   /*+ opt_param('_convert_set_to_join','true'  )  */  JOB, MGR FROM EMP
      2    MINUS
      3    SELECT JOB, MGR FROM EMP
      4    WHERE DEPTNO = 10;
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |      1 |        |      5 |00:00:00.01 |       8 |    |  |          |
    |   1 |  HASH UNIQUE                  |        |      1 |      6 |      5 |00:00:00.01 |       8 |  1115K|  1115K|  866K (0)|
    |*  2 |   HASH JOIN ANTI              |        |      1 |      6 |      9 |00:00:00.01 |       8 |   947K|   947K|  800K (0)|
    |   3 |    TABLE ACCESS FULL          | EMP    |      1 |     14 |     14 |00:00:00.01 |       6 |    |  |          |
    |   4 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      3 |00:00:00.01 |       2 |    |  |          |
    |*  5 |     INDEX RANGE SCAN          | FK_EMP |      1 |      3 |      3 |00:00:00.01 |       1 |    |  |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("JOB") AND
                  SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("MGR"))
       5 - access("DEPTNO"=10)
    
    SQL> SELECT DISTINCT JOB, MGR
      2   FROM EMP  E
      3   WHERE NOT EXISTS(SELECT 'X'
      4                      FROM EMP
      5                    WHERE DEPTNO = 10
      6                       AND SYS_OP_MAP_NONNULL(JOB)=SYS_OP_MAP_NONNULL(E.JOB)
      7                       AND SYS_OP_MAP_NONNULL(MGR)=SYS_OP_MAP_NONNULL(E.MGR));
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |      1 |        |      5 |00:00:00.01 |       8 |    |  |          |
    |   1 |  HASH UNIQUE                  |        |      1 |      6 |      5 |00:00:00.01 |       8 |  1115K|  1115K|  843K (0)|
    |*  2 |   HASH JOIN ANTI              |        |      1 |      6 |      9 |00:00:00.01 |       8 |   947K|   947K|  767K (0)|
    |   3 |    TABLE ACCESS FULL          | EMP    |      1 |     14 |     14 |00:00:00.01 |       6 |    |  |          |
    |   4 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      3 |00:00:00.01 |       2 |    |  |          |
    |*  5 |     INDEX RANGE SCAN          | FK_EMP |      1 |      3 |      3 |00:00:00.01 |       1 |    |  |          |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("E"."JOB") AND
                  SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("E"."MGR"))
       5 - access("DEPTNO"=10)
    
    -- SYS_OP_MAP_NONNULL
    SQL> SELECT 1
      2    FROM DUAL
      3  WHERE NULL = NULL;
    
    선택된 레코드가 없습니다.
    
    SQL> SELECT 1
      2    FROM DUAL
      3  WHERE SYS_OP_MAP_NONNULL( NULL ) = SYS_OP_MAP_NONNULL( NULL )
      4  ;
    
             1
    ----------
             1
    

조인 칼럼에 IS NOT NULL 조건 추가

  • NULL 조인시 실패하기 때문에 필터 조건을 추가하여 불필요한 테이블 엑세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.
  • FTS : Oracle의 경우 null값 비중이 5% 이상일 때) 내부적으로 추가해준다.
  • Single Column Index Scan : 싱글 컬럼일때는 널값을 가지고 있지 않기 때문에 조인 칼럼에 IS NOT NULL 조건 추가 불필요..
  • Multi Column Index Scan : 글쎄.. 테스트 해봐야할듯 ;;
    
    SQL> SELECT COUNT(*), COUNT(DEPTNO)
      2    FROM EMP
      3  WHERE SAL <= 2900;
    
      COUNT(*) COUNT(DEPTNO)
    ---------- -------------
            10            10
    
    SQL> INSERT INTO EMP VALUES( 7940, 'MILLER', 'CLERK', '7782', '1982-01-23', 2900, NULL, NULL, NULL );
    SQL> SELECT COUNT(*), COUNT(DEPTNO)
      2    FROM EMP
      3  WHERE SAL <= 2900;
    
      COUNT(*) COUNT(DEPTNO)
    ---------- -------------
            11            10
    
    -- 널값이 5% 이상인 상태에서 컬럼 통계정보가 존재하면 넣어주지만..
    SQL> SELECT /*+ LEADING( E ) USE_NL( D ) */ COUNT(E.EMPNO), COUNT(D.DNAME)
      2   FROM EMP E, DEPT D
      3  WHERE E.DEPTNO = D.DEPTNO
      4      AND SAL <= 2900  ;
    
    COUNT(E.EMPNO) COUNT(D.DNAME)
    -------------- --------------
                10             10
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |      20 |
    |   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.01 |      20 |
    |   2 |   NESTED LOOPS                |         |      1 |        |     10 |00:00:00.01 |      20 |
    |   3 |    NESTED LOOPS               |         |      1 |     10 |     10 |00:00:00.01 |      10 |
    |*  4 |     TABLE ACCESS FULL         | EMP     |      1 |     10 |     11 |00:00:00.01 |       6 |
    |*  5 |     INDEX UNIQUE SCAN         | DEPT_PK |     11 |      1 |     10 |00:00:00.01 |       4 |
    |   6 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     10 |      1 |     10 |00:00:00.01 |      10 |
    ---------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("SAL"<=2900)
       5 - access("E"."DEPTNO"="D"."DEPTNO")
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'EMP', METHOD_OPT=>'FOR ALL COLUMNS', NO_INVALIDATE=>FALSE);
    
    SQL> SELECT /*+ LEADING( E ) USE_NL( D ) */ COUNT(E.EMPNO), COUNT(D.DNAME)
      2   FROM EMP E, DEPT D
      3  WHERE E.DEPTNO = D.DEPTNO
      4      AND SAL <= 2900  ;
    
    COUNT(E.EMPNO) COUNT(D.DNAME)
    -------------- --------------
                10             10
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |         |      1 |        |      1 |00:00:00.01 |      16 |
    |   1 |  SORT AGGREGATE                |         |      1 |      1 |      1 |00:00:00.01 |      16 |
    |   2 |   NESTED LOOPS                 |         |      1 |        |     10 |00:00:00.01 |      16 |
    |   3 |    NESTED LOOPS                |         |      1 |     10 |     10 |00:00:00.01 |       6 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |     10 |     10 |00:00:00.01 |       2 |
    |*  5 |      INDEX FULL SCAN           | FK_EMP  |      1 |     14 |     14 |00:00:00.01 |       1 |
    |*  6 |     INDEX UNIQUE SCAN          | DEPT_PK |     10 |      1 |     10 |00:00:00.01 |       4 |
    |   7 |    TABLE ACCESS BY INDEX ROWID | DEPT    |     10 |      1 |     10 |00:00:00.01 |      10 |
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("SAL"<=2900)
       5 - filter("E"."DEPTNO" IS NOT NULL)
       6 - access("E"."DEPTNO"="D"."DEPTNO")
    
    
    27 개의 행이 선택되었습니다.
    
    
    
  • no_invalidate == false: 통계 정보 갱신 후 관련된 SQL Cursor들을 즉시 invalidate한다. 9i에서의 기본값이다.
  • no_invalidate == true: 통계 정보 갱신 후 관련된 SQL Cursor들을 invalidate하지 않는다. SQL Cursor들이 Shared Pool에서 age-out 된 후 reaload될 때 갱신된 통계 정보가 사용된다.

필터 조건 추가


SQL> SELECT *
  2   FROM EMP
  3  WHERE SAL BETWEEN 1000 AND 2000;
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      6 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      6 |      6 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------

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

   1 - filter(("SAL"<=2000 AND "SAL">=1000))

SQL> SELECT *
  2   FROM EMP
  3  WHERE SAL BETWEEN 2000 AND 1000
  4  ;
---------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |
|*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL| EMP  |      0 |      1 |      0 |00:00:00.01 |
---------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)
   2 - filter(("SAL"<=1000 AND "SAL">=2000))

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 4월 15, 2013

    이재현 says:

    아싸.. 벌금이다.. ㅠ

    아싸.. 벌금이다.. ㅠ

  2. 4월 18, 2013

    기민용 says:

    서브쿼리 UnNest 의 (사례2) 에서 (SELECT /*+ NO_MERGE */ DISTINCT DEPTNO FROM DEPT D ) ...

    서브쿼리 UnNest 의 (사례2) 에서
    (SELECT /*+ NO_MERGE */ DISTINCT DEPTNO FROM DEPT D ) D
    이부분이 왜 인덱스스캔이 아닌 테이블스캔일까?

    1. 4월 19, 2013

      이재현 says:

      해당 인덱스가 유니크 인덱스나 PK 인덱스라면... 디스틴트가 필요없으니 인덱스 풀스캔을 선택했을것이고, 해당 인덱스가 일반 인덱스라서 옵티마...

      해당 인덱스가 유니크 인덱스나 PK 인덱스라면... 디스틴트가 필요없으니 인덱스 풀스캔을 선택했을것이고,

      해당 인덱스가 일반 인덱스라서 옵티마이저가 풀스캔을 선택한것으로 사료됩니다.

      – PK 인덱스

      SQL> SELECT /*+ LEADING( D ) USE_NL( E ) */ *
        2    FROM (SELECT /*+ NO_MERGE */ DISTINCT DEPTNO FROM DEPT D ) D, EMP E
        3   WHERE E.DEPTNO = D.DEPTNO
        4  ;
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |         |      1 |        |     14 |00:00:00.01 |       7 |
      |   1 |  NESTED LOOPS                |         |      1 |        |     14 |00:00:00.01 |       7 |
      |   2 |   NESTED LOOPS               |         |      1 |     14 |     14 |00:00:00.01 |       5 |
      |   3 |    VIEW                      |         |      1 |      4 |      4 |00:00:00.01 |       2 |
      |   4 |     INDEX FULL SCAN          | DEPT_PK |      1 |      4 |      4 |00:00:00.01 |       2 |
      |*  5 |    INDEX RANGE SCAN          | FK_EMP  |      4 |      5 |     14 |00:00:00.01 |       3 |
      |   6 |   TABLE ACCESS BY INDEX ROWID| EMP     |     14 |      4 |     14 |00:00:00.01 |       2 |
      --------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - access("E"."DEPTNO"="D"."DEPTNO")
             filter("E"."DEPTNO" IS NOT NULL)
      
  3. 4월 18, 2013

    기민용 says:

    뷰머징이 불가능한 경우의 예시는 뷰머징 된 예시네???

    뷰머징이 불가능한 경우의 예시는 뷰머징 된 예시네???

    1. 4월 19, 2013

      이재현 says:

      네, 책에는 복합뷰는 머징이 안된다구 나와있는데.. 제가 알기로 되는걸로 알고있어서 테스트해보니 되더군요.. 음.. 혹시 뷰에서 SELECT...

      네, 책에는 복합뷰는 머징이 안된다구 나와있는데..

      제가 알기로 되는걸로 알고있어서 테스트해보니 되더군요..

      음.. 혹시 뷰에서 SELECT-LIST 가 아니구 메인 테이블에 SELECT-LIST일수도있으니 테스트해서 올려드릴게요..

      1. 4월 19, 2013

        기민용 says:

        기본적으로는 안되는게 맞지. 되는 경우는 특수한 경우이겠지. 위의 경우는 Group By 항목인 emp(deptno) 에 인덱스가 있어서 되는 ...

        기본적으로는 안되는게 맞지.
        되는 경우는 특수한 경우이겠지.
        위의 경우는 Group By 항목인 emp(deptno) 에 인덱스가 있어서 되는 경우인듯.

        1. 4월 19, 2013

          이재현 says:

          CVM 제약사항 1. Rownum을 사용한 경우 2. 인라이뷰 내부에 UNION/UNION ALL/MINUS 등의 집합연산자를 사용한 경우 3...

          CVM 제약사항

          1. Rownum을 사용한 경우
          2. 인라이뷰 내부에 UNION/UNION ALL/MINUS 등의 집합연산자를 사용한 경우
          3. CONNECT BY 절이 사용된 경우
          4. 집합 함수가 사용된 경우
          5. 인라이뷰에 Outer 조인이 걸린 경우
          6. 인라인뷰에 Order by가 사용된 경우
          7. Analytic 함수를 사용한 경우
          8. View Megingdl 불가능한 쿼리블럭이 있는 경우

          라고 나오는군요 ;;

          1. 4월 19, 2013

            이가혜 says:

            11g 부터는 집합연산자도 VIEW MERGING 됩니다.

            11g 부터는 집합연산자도 VIEW MERGING 됩니다.

            1. 4월 19, 2013

              기민용 says:

              그냥 된다고 하면 안될듯 한데. 되는 조건이 있고 안되는 조건이 있을 듯 한데. 논리적으로 될 수 있어야 머징이 되지 안되는걸 억지로 되게...

              그냥 된다고 하면 안될듯 한데.

              • 되는 조건이 있고 안되는 조건이 있을 듯 한데.
              • 논리적으로 될 수 있어야 머징이 되지 안되는걸 억지로 되게 하지는 않을거야.
              • 10g에서도 특정조건을 만족하면 되는걸로 보이고
        2. 4월 19, 2013

          이재현 says:

          SQL> SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.3') */ * 2 FROM...
          SQL> SELECT /*+   OPTIMIZER_FEATURES_ENABLE('10.2.0.3')  */ *
            2    FROM DEPT D
            3           ,(SELECT COMM, SUM( SAL ) AS SAL
            4              FROM EMP E
            5             GROUP BY COMM ) E
            6    WHERE D.LOC = 'DALLAS'
            7      AND D.DEPTNO = E.COMM ;
          ------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
          ------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT              |         |      1 |        |      0 |00:00:00.01 |       8 |    |          |          |
          |   1 |  HASH GROUP BY                |         |      1 |      1 |      0 |00:00:00.01 |       8 |   869K|   869K|          |
          |   2 |   MERGE JOIN                  |         |      1 |      1 |      0 |00:00:00.01 |       8 |    |          |          |
          |*  3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |    |          |          |
          |   4 |     INDEX FULL SCAN           | DEPT_PK |      1 |      4 |      4 |00:00:00.01 |       1 |    |          |          |
          |*  5 |    SORT JOIN                  |         |      1 |      4 |      0 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
          |*  6 |     TABLE ACCESS FULL         | EMP     |      1 |      4 |      4 |00:00:00.01 |       6 |    |          |          |
          ------------------------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             3 - filter("D"."LOC"='DALLAS')
             5 - access("D"."DEPTNO"="COMM")
                 filter("D"."DEPTNO"="COMM")
             6 - filter("COMM" IS NOT NULL)
          
          
          28 개의 행이 선택되었습니다.
          
          
          1. 4월 19, 2013

            기민용 says:

            이건 좀 억지스러운데 deptno 랑 comm이랑 조인하다니... 일단 결과는 뷰머징이 되어부렀네.. 집계함수를 사용했음에도 뷰머징이 되는 이유...

            이건 좀 억지스러운데 deptno 랑 comm이랑 조인하다니...
            일단 결과는 뷰머징이 되어부렀네..
            집계함수를 사용했음에도 뷰머징이 되는 이유는...

            • 앞서 추측한. emp(deptno) 에 인덱스가 있기 때문은 아니네
            • 새로운 추측. dept(deptno) 가 Unique 하기 때문인듯 하네...
            1. 4월 19, 2013

              이재현 says:

              예.. dept(deptno) 집합이 유닉크하니 가능한거같아요..

              예.. dept(deptno) 집합이 유닉크하니 가능한거같아요..

  4. 4월 18, 2013

    기민용 says:

    조건절 이행은 상수조건만 이행이 되지. 컬럼끼리의 조인조건은 이행이 안되지. 이부분 주의해야 함.

    조건절 이행은 상수조건만 이행이 되지.
    컬럼끼리의 조인조건은 이행이 안되지.
    이부분 주의해야 함.

    1. 4월 19, 2013

      안길환 says:

      잘 알겠습니다.

      잘 알겠습니다.

  5. 4월 19, 2013

    배성환 says:

    너무 흰트들이 난무해 어려움 ㅠㅠ

    너무 흰트들이 난무해 어려움 ㅠㅠ

    1. 4월 19, 2013

      이재현 says:

      난무는 아니구 ;; 책에나온대루 실행계획이안타니 어쩔수 없이 태운거지 ;;

      난무는 아니구 ;;

      책에나온대루 실행계획이안타니 어쩔수 없이 태운거지 ;;

      1. 4월 20, 2013

        변진석 says:

        cost에서는 정말 돌려봐야.. 추측으로 어렵네요

        cost에서는 정말 돌려봐야.. 추측으로 어렵네요

        1. 4월 25, 2013

          이재현 says:

          그래서 튜너가 필요한게 아닐까?? 고스트는 예측일뿐이니..

          그래서 튜너가 필요한게 아닐까??

          고스트는 예측일뿐이니..

  6. 4월 29, 2013

    이재현 says:

    EMP 기본키 생성( 인덱스 X, PK X ) SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM U...

    EMP 기본키 생성( 인덱스 X, PK X )

    
    SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS;
    
    선택된 레코드가 없습니다.
    
    SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES;
    
    선택된 레코드가 없습니다.
    
    SQL> ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY(EMPNO);
    
    테이블이 변경되었습니다.
    
    SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS;
    
    TABLE_NAME                                                   CONSTRAINT_NAME
    ------------------------------------------------------------ ------------------------------------------------------------
    EMP                                                          PK_EMP
    
    SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES;
    
    TABLE_NAME                                                   INDEX_NAME
    ------------------------------------------------------------ ------------------------------------------------------------
    EMP                                                          PK_EMP
    
    SQL> SELECT * FROM V$VERSION WHERE ROWNUM <= 1;
    
    BANNER
    --------------------------------------------------------------------------------------------------------------------------------
    Personal Oracle Database 10g Release 10.2.0.4.0 - 64bit Producti
    
    SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
      2  WHERE TABLE_NAME = 'DEPT';
    
    선택된 레코드가 없습니다.
    
    SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES
      2  WHERE TABLE_NAME = 'DEPT';
    
    선택된 레코드가 없습니다.
    
    SQL> ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO);
    
    테이블이 변경되었습니다.
    
    SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
      2  WHERE TABLE_NAME = 'DEPT'
      3  ;
    
    TABLE_NAME                                                   CONSTRAINT_NAME
    ------------------------------------------------------------ ------------------------------------------------------------
    DEPT                                                         PK_DEPT
    
    SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES
      2  WHERE TABLE_NAME = 'DEPT'
      3  ;
    
    TABLE_NAME                                                   INDEX_NAME
    ------------------------------------------------------------ ------------------------------------------------------------
    DEPT                                                         PK_DEPT
    
    SQL> ALTER TABLE EMP ADD CONSTRAINT FK_DEPT FOREIGN KEY(DEPTNO) references DEPT( DEPTNO );
    
    테이블이 변경되었습니다.
    
    SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
      2  WHERE TABLE_NAME = 'EMP';
    
    TABLE_NAME                                                   CONSTRAINT_NAME
    ------------------------------------------------------------ ------------------------------------------------------------
    EMP                                                          PK_EMP
    EMP                                                          FK_DEPT
    
    SQL> SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES
      2  WHERE TABLE_NAME = 'EMP';
    
    TABLE_NAME                                                   INDEX_NAME
    ------------------------------------------------------------ ------------------------------------------------------------
    EMP                                                          PK_EMP
    
    SQL>