SQL 전문가 가이드 (2013년)
쿼리변환 0 0 2,995

by 김정식 Subquery Unnesting View Merging Predicate Pushing 쿼리변환 [2013.04.15]


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


- 강좌 URL : http://www.gurubee.net/lecture/2401

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입