4.8 공통 표현식 제거

- 같은 조건식이 여러 곳에서 반복 사용될 경우, 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리 변환
- _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) ;

-- job 컬럼에대한 필터조건이 중복기술하면 이에대한 비교연산도 두번씩 일어난다.

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) ;
      
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=3 Bytes=171)
   1    0   MERGE JOIN (Cost=5 Card=3 Bytes=171)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   3    2       INDEX (FULL SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
   4    1     FILTER
   5    4       SORT (JOIN) (Cost=3 Card=3 Bytes=111)
   6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=3 Bytes=111)
   7    6           INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=1 Card=3)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   4 - filter("D"."LOC"='DALLAS' OR "E"."SAL">=1000)
   5 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("E"."DEPTNO"="D"."DEPTNO")
   7 - access("E"."JOB"='CLERK')
-----------------------------------------------------------

-- 기능이 작동하지 못하도록 "_eliminate_common_subexpr = false"

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

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=3 Bytes=171)
   1    0   NESTED LOOPS (Cost=8 Card=3 Bytes=171)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=37)
-----------------------------------------------------------

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

SELECT /*+ USE_CONCAT */ * 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
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=4 Bytes=228)
   1    0   CONCATENATION
   2    1     MERGE JOIN (Cost=5 Card=3 Bytes=171)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Card=4 Bytes=80)
   4    3         INDEX (FULL SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
   5    2       SORT (JOIN) (Cost=3 Card=3 Bytes=111)
   6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=3 Bytes=111)
   7    6           INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=1 Card=3)
   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=37)
   9    8       NESTED LOOPS (Cost=4 Card=1 Bytes=57)
  10    9         TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=20)
  11    9         INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=0 Card=3)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   5 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("E"."DEPTNO"="D"."DEPTNO")
   6 - filter("E"."SAL">=1000)
   7 - access("E"."JOB"='CLERK')
   8 - filter((LNNVL("E"."DEPTNO"="D"."DEPTNO")

                OR LNNVL("E"."JOB"='CLERK') OR LNNVL("E"."SAL">=1000)) AND "E"."DEPTNO"="D"."DEPTNO")
  10 - filter("D"."LOC"='DALLAS')
  11 - access("E"."JOB"='CLERK')
-----------------------------------------------------------


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

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=3 Bytes=171)
   1    0   NESTED LOOPS (Cost=8 Card=3 Bytes=171)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=37)
-----------------------------------------------------------

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

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 6월  5일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^