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

by 구루비스터디 Subquery Unnesting View Merging Predicate Pushing 쿼리변환 [2013.09.01]


  1. 쿼리 변환?
  2. 방식?
  3. 서브쿼리 Unnesting( SSU : Simple Subquery Unnesting ) ( 9i : Heuristic, 10g : Cost-Base )
  4. 서브쿼리 Unnesting
  5. 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때
  6. 3. 뷰 Merging
  7. 뷰 Merging이 불가능한 경우
  8. 4. 조건절 Pushing
  9. 가. 조건절(Predicate) Pushdown
  10. 나. 조건절( Predicate ) Pullup
  11. 다. 조인 조건( Join Predicate ) Pushdown
  12. 5. 조건절 이행
  13. 6. 불필요한 조인 제거
  14. 7. OR 조건을 Union으로 변환
  15. 8. 기타 쿼리 변환
  16. 가. 집합연산을 조인으로 변환
  17. 조인 칼럼에 IS NOT NULL 조건 추가
  18. 필터 조건 추가


쿼리 변환?

  • 실행계획을 생성하고 비용을 계산하기에 앞서 사용자 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))


"구루비 데이터베이스 스터디모임" 에서 2013년에 "SQL 전문가 가이드" 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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