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

8. 공통 표현식 제거




공통 표현식 제거(Common Subexpression Elimination) 정의

  1. 같은 조건식이 여러 곳에서 반복 사용 될 경우, 오라클은 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리 변환 수행
  2. _eliminate_common_subexpr 파라미터로 제어
  3. POINT : 인덱스를 탈 수 있음 (JOB='CLERK' 조건을 인덱스 조건으로 사용 가능)

테스트

  • _eliminate_common_subexpr = true : 쿼리 변환 가능
    Current SQL statement for this session:
    select * from emp e, dept d
    where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
           or
          (e.deptno = d.deptno and e.job = 'CLERK' and e.sal >= 1000) 
          
    Transformation SQL statement : 
    SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
           "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
           "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO", "D"."DEPTNO" "DEPTNO",
           "D"."DNAME" "DNAME", "D"."LOC" "LOC"
      FROM "HEAEUN"."EMP" "E", "HEAEUN"."DEPT" "D"
     WHERE "E"."JOB" = 'CLERK'
       AND "E"."DEPTNO" = "D"."DEPTNO"
       AND ("D"."LOC" = 'DALLAS' OR "E"."SAL" >= 1000)
    
    ============
    Plan Table
    ============
    -----------------------------------------------------+-----------------------------------+
    | Id  | Operation                       | Name       | Rows  | Bytes | Cost  | Time      |
    -----------------------------------------------------+-----------------------------------+
    | 0   | SELECT STATEMENT                |            |       |       |     5 |           |
    | 1   |  MERGE JOIN                     |            |     4 |   228 |     5 |  00:00:01 |
    | 2   |   TABLE ACCESS BY INDEX ROWID   | DEPT       |     4 |    80 |     2 |  00:00:01 |
    | 3   |    INDEX FULL SCAN              | DEPT_PK    |     4 |       |     1 |  00:00:01 |
    | 4   |   FILTER                        |            |       |       |       |           |
    | 5   |    SORT JOIN                    |            |     4 |   148 |     3 |  00:00:01 |
    | 6   |     TABLE ACCESS BY INDEX ROWID | EMP        |     4 |   148 |     2 |  00:00:01 |
    | 7   |      INDEX RANGE SCAN           | EMP_JOB_IDX|     4 |       |     1 |  00:00:01 |
    -----------------------------------------------------+-----------------------------------+
    Predicate Information:
    ----------------------
    4 - filter(("D"."LOC"='DALLAS' OR "E"."SAL">=1000))
    5 - access("E"."DEPTNO"="D"."DEPTNO")
    5 - filter("E"."DEPTNO"="D"."DEPTNO")
    7 - access("E"."JOB"='CLERK')
     
    Content of other_xml column
    ===========================
      db_version     : 10.2.0.3
      parse_schema   : HEAEUN
      plan_hash      : 3960057632
      Outline Data:
      /*+
        BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
          INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."JOB"))
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
          USE_MERGE(@"SEL$1" "E"@"SEL$1")
        END_OUTLINE_DATA
      */
    
  • /*+ NO_EXPAND */ 를 통해 OR_Expansion 으로 처리 되지 않도록 하기
    • 조건 : _eliminate_common_subexpr = true
/*+ NO_EXPAND */

조건절에 있는 or연산자 (or in 연산자) 조건이 실행계획으로 처리되지 않도록 함
USE_CONCAT의 반대 개념

Current SQL statement for this session:
select /*+ NO_EXPAND */ * from emp e, dept d
where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
       or
      (e.deptno = d.deptno and e.job = 'CLERK' and e.sal >= 1000) 

Transformation SQL statement : 
SELECT /*+ NO_EXPAND */
       "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO", "D"."DEPTNO" "DEPTNO",
       "D"."DNAME" "DNAME", "D"."LOC" "LOC"
  FROM "HEAEUN"."EMP" "E", "HEAEUN"."DEPT" "D"
 WHERE "E"."JOB" = 'CLERK'
   AND ("D"."LOC" = 'DALLAS' OR "E"."SAL" >= 1000)
   AND "E"."DEPTNO" = "D"."DEPTNO"
   
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name       | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |            |       |       |     5 |           |
| 1   |  MERGE JOIN                     |            |     4 |   228 |     5 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID   | DEPT       |     4 |    80 |     2 |  00:00:01 |
| 3   |    INDEX FULL SCAN              | DEPT_PK    |     4 |       |     1 |  00:00:01 |
| 4   |   FILTER                        |            |       |       |       |           |
| 5   |    SORT JOIN                    |            |     4 |   148 |     3 |  00:00:01 |
| 6   |     TABLE ACCESS BY INDEX ROWID | EMP        |     4 |   148 |     2 |  00:00:01 |
| 7   |      INDEX RANGE SCAN           | EMP_JOB_IDX|     4 |       |     1 |  00:00:01 |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - filter(("D"."LOC"='DALLAS' OR "E"."SAL">=1000))
5 - access("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."DEPTNO"="D"."DEPTNO")
7 - access("E"."JOB"='CLERK')
 
Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : HEAEUN
  plan_hash      : 3960057632
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."JOB"))
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_MERGE(@"SEL$1" "E"@"SEL$1")
    END_OUTLINE_DATA
  */
  • alter session set "_eliminate_common_subexpr" = false;
    select * from emp e, dept d
    where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
           or
          (e.deptno = d.deptno and e.job = 'CLERK' and e.sal >= 1000) 
    
    Transformation SQL statement : 
    SELECT "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
           "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
           "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO", "D"."DEPTNO" "DEPTNO",
           "D"."DNAME" "DNAME", "D"."LOC" "LOC"
      FROM "HEAEUN"."EMP" "E", "HEAEUN"."DEPT" "D"
     WHERE     "E"."DEPTNO" = "D"."DEPTNO"
           AND "E"."JOB" = 'CLERK'
           AND "D"."LOC" = 'DALLAS'
        OR "E"."DEPTNO" = "D"."DEPTNO" AND "E"."JOB" = 'CLERK'
           AND "E"."SAL" >= 1000
     
    ============
    Plan Table
    ============
    --------------------------------------+-----------------------------------+
    | Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
    --------------------------------------+-----------------------------------+
    | 0   | SELECT STATEMENT    |         |       |       |     8 |           |
    | 1   |  NESTED LOOPS       |         |     5 |   285 |     8 |  00:00:01 |
    | 2   |   TABLE ACCESS FULL | DEPT    |     4 |    80 |     3 |  00:00:01 |
    | 3   |   TABLE ACCESS FULL | EMP     |     1 |    37 |     1 |  00:00:01 |
    --------------------------------------+-----------------------------------+
    Predicate Information:
    ----------------------
    3 - filter((("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB"='CLERK' AND "D"."LOC"='DALLAS') OR ("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB"='CLERK' AND "E"."SAL">=1000)))
     
    Content of other_xml column
    ===========================
      db_version     : 10.2.0.3
      parse_schema   : HEAEUN
      plan_hash      : 4192419542
      Outline Data:
      /*+
        BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
          OPT_PARAM('_eliminate_common_subexpr' 'false')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          FULL(@"SEL$1" "D"@"SEL$1")
          FULL(@"SEL$1" "E"@"SEL$1")
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
          USE_NL(@"SEL$1" "E"@"SEL$1")
        END_OUTLINE_DATA
      */
    
    • /*+ USE_CONCAT */ : IN 이나 OR 조건이 있는 SQL에 USE_CONCAT 힌트를 사용하면 OR_Expansion(Union All 로 분리되는 쿼리변환) 발생
      /*+ USE_CONCAT (8) */ - 숫자 8의 의미

      Inlist 를 사용할수 있는 경우에는 Union All 로 분리하지 말것을 강제하는 힌트이다.
      인자없이 힌트를 USE_CONCAT(@MAIN)으로 사용 한다면 모든 경우의 수를 다 고려하여 가장 비용이 적게드는 Plan 을 선택 할것이다.
      심지어 USE_CONCAT 힌트를 사용 하였지만 분리되지 않는 경우가 있는데 이것은 힌트를 무시한 것이 아니라, 옵티마이져 입장에서 비용계산을
      기준으로 가장 저렴한 PLAN 을 선택한것이다.
      만약 힌트를 사용하였지만 Union ALL 로 분리가 안되며 이것 때문에 성능이 문제가 된다면, USE_CONCAT 힌트의 숫자 인자(1혹은 8)를 활용하면 된다.
      ※ 참고 사이트 : http://scidb.tistory.com/73

1. /*+ USE_CONCAT */
※ 조건 : _eliminate_common_subexpr = false

Current SQL statement for this session:
select /*+ USE_CONCAT */ * from emp e, dept d
where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
       or
      (e.deptno = d.deptno and e.job = 'CLERK' and e.sal >= 1000) 

Transformation SQL statement : 
SELECT /*+ USE_CONCAT (8) */
       "E"."EMPNO" "EMPNO", "E"."ENAME" "ENAME", "E"."JOB" "JOB",
       "E"."MGR" "MGR", "E"."HIREDATE" "HIREDATE", "E"."SAL" "SAL",
       "E"."COMM" "COMM", "E"."DEPTNO" "DEPTNO", "D"."DEPTNO" "DEPTNO",
       "D"."DNAME" "DNAME", "D"."LOC" "LOC"
  FROM "HEAEUN"."EMP" "E", "HEAEUN"."DEPT" "D"
 WHERE     "E"."DEPTNO" = "D"."DEPTNO"
       AND "E"."JOB" = 'CLERK'
       AND "D"."LOC" = 'DALLAS'
    OR "E"."DEPTNO" = "D"."DEPTNO" AND "E"."JOB" = 'CLERK'
       AND "E"."SAL" >= 1000

============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name       | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |            |       |       |     9 |           |
| 1   |  CONCATENATION                  |            |       |       |       |           |
| 2   |   MERGE JOIN                    |            |     3 |   171 |     5 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID  | DEPT       |     4 |    80 |     2 |  00:00:01 |
| 4   |     INDEX FULL SCAN             | DEPT_PK    |     4 |       |     1 |  00:00:01 |
| 5   |    SORT JOIN                    |            |     3 |   111 |     3 |  00:00:01 |
| 6   |     TABLE ACCESS BY INDEX ROWID | EMP        |     3 |   111 |     2 |  00:00:01 |
| 7   |      INDEX RANGE SCAN           | EMP_JOB_IDX|     4 |       |     1 |  00:00:01 |
| 8   |   TABLE ACCESS BY INDEX ROWID   | EMP        |     1 |    37 |     1 |  00:00:01 |
| 9   |    NESTED LOOPS                 |            |     1 |    57 |     4 |  00:00:01 |
| 10  |     TABLE ACCESS FULL           | DEPT       |     1 |    20 |     3 |  00:00:01 |
| 11  |     INDEX RANGE SCAN            | EMP_JOB_IDX|     4 |       |     0 |           |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."DEPTNO"="D"."DEPTNO")
6 - filter("E"."SAL">=1000)
7 - access("E"."JOB"='CLERK')
8 - filter(("E"."DEPTNO"="D"."DEPTNO" AND (LNNVL("E"."DEPTNO"="D"."DEPTNO") OR LNNVL("E"."JOB"='CLERK') OR LNNVL("E"."SAL">=1000))))
10 - filter("D"."LOC"='DALLAS')
11 - access("E"."JOB"='CLERK')
 
Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : HEAEUN
  plan_hash      : 3259475847
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_eliminate_common_subexpr' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8)
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$1_1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_1" "E"@"SEL$1" ("EMP"."JOB"))
      FULL(@"SEL$1_2" "D"@"SEL$1_2")
      INDEX(@"SEL$1_2" "E"@"SEL$1_2" ("EMP"."JOB"))
      LEADING(@"SEL$1_1" "D"@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1_2" "D"@"SEL$1_2" "E"@"SEL$1_2")
      USE_MERGE(@"SEL$1_1" "E"@"SEL$1")
      USE_NL(@"SEL$1_2" "E"@"SEL$1_2")
    END_OUTLINE_DATA
  */

※ LNNVL(condition) : 조건(condition) 을 체크하여 조건 결과 값이 FALSE 나 UNKNOWN 이면 TRUE 를, TRUE 이면 FALSE 를 반환하는 함수

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.