꿈꾸는 개발자, DBA 커뮤니티 구루비

공통 표현식 제거란
1. 공통 표현식 제거 (_eliminate_common_subexpr =true)
2. 공통 표현식 제거 (_eliminate_common_subexpr =false)

공통 표현식 제거란

같은 조건식이 여러곳에 반복되어 사용도경우, 오라클은 해당 조건식을 각 로우당 한번씩만 평가되도록 쿼리를 반환하는 기능
관련 파라미터 : _eliminate_common_subexpr

공통 표현식 제거 (_eliminate_common_subexpr =true)

 

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

-- 위와 같이 detpno에 대한 조인조건과 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='CLERK'조건을 인덱스 조건으로 사용할수 있게 된것에 주목하길 바람

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

 
-- _eliminate_common_subexpr 기능이 작동 못하도록 하면  옵티마이저는 두가지 선택을 하게 된다.

SQL> alter session set "_eliminate_common_subexpr" = false;

세션이 변경되었습니다.

1. OR-Expansion 쿼리 변환 수행 

SQL> select  * from emp e, dept d
  2  where (e.deptno=d.deptno and e.job='CLERK' and d.loc='DALLAS')
  3  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 
--  - OR 연산자로 묶인 조건식을 제외하면 인덱스 엑세스에 활용할 만한 조건식이 없으므로 FULL Scan 

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