꿈꾸는 개발자, DBA 커뮤니티 구루비

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

같은 조건식이 여러 곳에서 반복 사용 될 경우, 오라클은 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리 변환 수행
_eliminate_common_subexpr 파라미터로 제어
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 로 분리되는 쿼리변환) 발생
  {info:title=/*+ 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
  {info}


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 를 반환하는 함수

8. 공통 표현식 제거 (by joang77) [2010.10.16]

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

테스트