by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

8. 공통 표현식 제거




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

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.