같은 조건식이 여러 곳에서 반복 사용될 경우, 오라클은 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리를 변환하는데, 이를 '공통 표현식 제거'라고 한다.
_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)
select * from emp e, dept d
where e.deptno = d.deptno
and e.job = 'CLERK'
and (d.loc = 'DALLAS' or e.sal >= 1000)
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);
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 150 |
| 1 | NESTED LOOPS | | 3 | 150 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 96 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."JOB"='CLERK')
4 - filter("D"."LOC"='DALLAS' OR "E"."SAL">=1000)
5 - access("E"."DEPTNO"="D"."DEPTNO")
OR-Expansion 쿼리 변환을 수행
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)
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 200 |
| 1 | CONCATENATION | | | |
| 2 | NESTED LOOPS | | 3 | 150 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 96 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
| 7 | NESTED LOOPS | | 1 | 50 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 96 |
|* 9 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | |
|* 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 |
|* 11 | INDEX UNIQUE SCAN | DEPT_PK | 1 | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."SAL">=1000)
4 - access("E"."JOB"='CLERK')
6 - access("E"."DEPTNO"="D"."DEPTNO")
8 - filter(LNNVL("E"."SAL">=1000)) -> 위아래 집합 간 중복 제거
9 - access("E"."JOB"='CLERK')
10 - filter("D"."LOC"='DALLAS')
11 - access("E"."DEPTNO"="D"."DEPTNO")
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)
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 285 | 8 (0)|
| 1 | NESTED LOOPS | | 5 | 285 | 8 (0)|
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)|
| 3 | TABLE ACCESS FULL| EMP | 1 | 37 | 1 (0)|
----------------------------------------------------------------