2.1_CSE_(Common_Subexpression_Elimination)
2011.09.20 09:14
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임을 기억하자.
