메뉴 건너뛰기

bysql.net

2.1_CSE_(Common_Subexpression_Elimination)

AskZZang 2011.09.20 00:44 조회 수 : 1812

2.1. CSE (Common Subexpression Elimination)

Where 절에서 or 사용시 중첩된 조건절은 제거하라!

 

CSE란 중첩되는(혹은 중복되는) Where 조건을 찾아서 제거하는 것이다.

 

예제)

           SELECT DEPARTMENT_ID, SALARY

           FROM  EMPLOYEES

           WHERE (DEPARTMENT_ID = 60 AND SALARY = 4200)

           OR    (DEPARTMENT_ID = 60);

 

WHERE 조건이 OR로 분리되어 있으므로 중첩될 경우 범위가 넓은 조건만 있으면 된다.

다시 말해 WHERE 절은 DEPARTMENT_ID = 60만 있으면 된다.

이 경우에 (DEPARTMENT_ID = 60 AND SALARY = 4200) 조건을 삭제해버린다.

 

-------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time   |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                  |     5 |    35 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     5 |    35 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |     5 |     |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("DEPARTMENT_ID"=60)

 

Predicate Information을 보면 DEPARTMENT_ID = 60 만이 남아있고 나머지 조건들은 삭제된 것을 알 수 있다.

 

아래의 10053 Trace 내용도 이것을 증명해 준다.

 

*************************

Common Subexpression elimination (CSE)

*************************

CSE:     CSE not performed on query block SEL$1 (#0).

SU: Considering subquery unnesting in query block SEL$1 (#0)

********************

Subquery Unnest (SU)

********************

SJC: Considering set-join conversion in query block SEL$1 (#0)

*************************

Set-Join Conversion (SJC)

*************************

SJC: not performed

PM: Considering predicate move-around in query block SEL$1 (#0)

**************************

Predicate Move-Around (PM)

**************************

PM:     PM bypassed: Outer query contains no views.

PM:     PM bypassed: Outer query contains no views.

query block SEL$1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$1 (#0)

"EMPLOYEES"."DEPARTMENT_ID"=60 AND ("EMPLOYEES"."SALARY"=4200 OR 0=0)

try to generate transitive predicate from check constraints for query block SEL$1 (#0)

constraint: "EMPLOYEES"."SALARY">0

 

finally: "EMPLOYEES"."DEPARTMENT_ID"=60 AND ("EMPLOYEES"."SALARY"=4200 OR 0=0)

 

apadrv-start sqlid=4718331633295708078

  :

    call(in-use=912, alloc=16360), compile(in-use=63400, alloc=65000), execution(in-use=80892, alloc=81624)

 

위의 내용을 해석해보면 OR 부분이 (SALARY = 4200 OR 0 = 0)로 재작성 되었다. 0 = 0 조건은 항상 TRUE이므로 괄호 안의 모든 조건은 삭제되어도 무방한 것이다. 바로 이것이 CSE의 원리이다.

 

만약 CSE 기능이 없다면 어떻게 될까?

 

ALTER SESSION SET "_eliminate_common_subexpr" = FALSE;

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       department_id, salary

  FROM employees

 WHERE (department_id = 60 AND salary = 4200)

    OR (department_id = 60);

 

SELECT *

  FROM TABLE (DBMS_XPlan.display_cursor (NULL, NULL, 'allstats last'));

 

--------------------------------------------------------------------------------------------------

| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time  |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                 |     6 |    42 |     4   (0)| 00:00:01 |

|   1 |  CONCATENATION             |                 |      |      |            |         |

|*  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |     7 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |     5 |    |     1   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     5 |    35 |     2   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |     5 |    |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("SALARY"=4200)

   3 - access("DEPARTMENT_ID"=60)

   4 - filter(LNNVL("SALARY"=4200) OR LNNVL("DEPARTMENT_ID"=60))

   5 - access("DEPARTMENT_ID"=60)

 

만일 CSE 기능이 없다면 OR 조건을 옵티마이저가 Full Table Scan 혹은 Union으로 평가하여 성능향상의 효과는 물거품이 될 것이다.

 

그렇다고 해서 CSE 기능을 믿고 중복된 조건을 가지는 SQL을 작성하면 안된다.

중복된 조건절을 없애는 것이 가장 최적의 SQL임을 기억하자.