오라클 성능 고도화 원리와 해법 II (2012년)
공통 표현식 제거 0 0 55,276

by 구루비스터디 쿼리변환 공통표현식제거 [2018.04.01]


  1. 공통 표현식 제거란
    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 |
---------------------------------------------------------------------------



"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3291

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입