08. 공통 표현식 제거
- 같은 조건식이 여러 곳에서 반복 사용될 경우, 오라클은 해당 조건식이 가 로우당 한 번씩만 평가 되도록 쿼리를 변환하는데 이를 "공통 표현식 제거(Common subexpression elimination)" 한다.
- 파라미터 : _eliminate_common_subexpr
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)
- deptno , job 컬럼에 대한 조건을 중복 기술하면 비교 연산도 두번씩 일어나는데 이를 피하려고 옵티마이저는 쿼리를 아래와 같은 형태로 변환한다.
select * from emp e, dept d
where e.deptno = d.deptno
and e.job = 'CLERK'
and (d.loc='DALLAS' or e.sal >= 1000)
- 여기서 job 컬럼에 인덱스 액세스 조건으로 사용할 수 있게 된다.
create index emp_job_idx on emp(job);
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);
Execution Plan
----------------------------------------------------------
Plan hash value: 3325482559
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 232 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 4 | 232 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 4 | 152 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 152 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("D"."LOC"='DALLAS' OR "E"."SAL">=1000)
5 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
7 - access("E"."JOB"='CLERK')
- eliminate_common_subexpr 기능이 작동 못하도록 하면 옵티마이저는 두가지 선택을 하게 된다.
1 OR-Expansion 쿼리 변환을 수행
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);
Execution Plan
----------------------------------------------------------
Plan hash value: 3107164459
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 232 | 9 (12)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | MERGE JOIN | | 3 | 174 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 3 | 114 | 3 (34)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | EMP_JOB_IDX | 4 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
6 - filter("E"."SAL">=1000)
7 - access("E"."JOB"='CLERK')
10 - filter("D"."LOC"='DALLAS')
11 - access("E"."JOB"='CLERK')
12 - filter("E"."DEPTNO"="D"."DEPTNO" AND (LNNVL("E"."DEPTNO"="D"."DEPTNO") OR
LNNVL("E"."JOB"='CLERK') OR LNNVL("E"."SAL">=1000)))
2 emp 와 dept 테이블을 모두 full Scan
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);
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 290 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 290 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 38 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------