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

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