같은 조건식이 여러 곳에서 반복 사용될 경우, 오라클은 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리를 변환하는데,
이를 '공통표현식 제거(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.dpetno = d.deptno
and e.job = 'CLERK'
and (d.loc = 'DALLAS' or e.sal >= 1000)
비교연산을 덜하게 된것도 의미 있지만, 새로운 인덱스 엑세스 조건이 만들어 졌다는 사실이 더 중요
아래와 같이 job = 'CLERK' 조건을 인덱스 엑세스 조건으로 사용할수 있게 된것에 주목하기 바람
SQL> create table dept as select * from scott.dept;
테이블이 생성되었습니다.
SQL> create table emp as select * from scott.emp;
테이블이 생성되었습니다.
SQL> alter table dept add constraint dept_pk primary key(deptno);
테이블이 변경되었습니다.
SQL> create index emp_job_idx on emp(job);
인덱스가 생성되었습니다.
SQL> exec dbms_stats.gather_table_stats(user, 'dept');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec dbms_stats.gather_table_stats(user, 'emp');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set autotrace on
SQL> select * from emp e, dept d
2 where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
3 or
4 (e.deptno = d.deptno and e.job = 'CLERK' and e.sal >= 1000) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1965827697
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 171 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 171 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
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")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1141 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
이기능이 작동하지 못하도록 _eliminate_common_subexpr 파라미터를 false로 바꾸면 어떻게 실행되는지 살펴보자
옵티마이저는 두가지 선택이 가능한데,
그중 하나는 OR-Expansion 쿼리를 수행하는 것
그러면 job= 'CLERK'조건으로 emp 테이블을 두 번 읽고 dept 테이블과 조인도 두번하게 된다.
한번은 sal >= 1000인 사원을 찾기 위함이고, 또 한번은 loc = 'DALLAS'인 부서를 찾기 위함이다
아래의 실행계획을 통해 확인해보자
SQL> alter session set "_eliminate_common_subexpr" = false;
세션이 변경되었습니다.
SQL> select * from emp e, dept d
2 where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
3 or
4 (e.deptno = d.deptno and e.job = 'CLERK' and e.sal >= 1000) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 832419696
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 228 | 6 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | 3 | 171 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 57 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."SAL">=1000)
4 - access("E"."JOB"='CLERK')
6 - access("E"."DEPTNO"="D"."DEPTNO")
9 - access("E"."JOB"='CLERK')
10 - filter("D"."LOC"='DALLAS' AND (LNNVL("E"."DEPTNO"="D"."DEPTNO") OR
LNNVL("E"."JOB"='CLERK') OR LNNVL("E"."SAL">=1000)))
11 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1160 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
두번째 선택은, emp와 dept테이블을 모두 Full Scan하는 것이다. OR연산자로 묶인 조건식을 제외하면 인덱스에 활용할 만한 조건식이 아예 없기 때문이다
SQL> alter session set "_eliminate_common_subexpr" = false;
세션이 변경되었습니다.
SQL> select /*+ NO_EXPAND */ * from emp e, dept d
2 where (e.deptno = d.deptno and e.job = 'CLERK' and d.loc = 'DALLAS')
3 or
4 (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 | | 4 | 228 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 228 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 37 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
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)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1141 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed