08 공통 표현식 제거

• 같은 조건식이 여러 곳에서 반복 사용될 경우 해당 조건식이 각 로우당 한 번씩만 평가되도록 오라클이 쿼리를 변환
• _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")                                                      

(?) 이 기능이 작동하지 못하도록 _eliminate_common_subexpr 파라미터를 false로 바꾸면?
-> 옵티마이저는 두 가지 선택을 하게 됨

h5.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);


(실행계획은 책 참조)


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")

• emp 테이블을 두 번 읽고, dept 테이블과의 조인도 두 번 하게 됨
  
  

h5.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);

----------------------------------------------------------------
| 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)|
----------------------------------------------------------------

• OR 연산자로 묶인 조건식을 제외하면 인덱스 액세스에 활용할만한 조건식이 아예 없기 때문