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

by 구루비 쿼리변환 공통표현식제거 [2010.05.14]


같은 조건식이 여러 곳에서 반복 사용될 경우, 오라클은 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리를 변환하는데,
이를 '공통표현식 제거(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

문서에 대하여

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

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

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

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

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