행위

튜닝 쿼리 변형

DB CAFE

notifications_active 데이터베이스 전문기업 안내


1 쿼리 변환 종류[편집]

 filter_1 "서브쿼리 Unnesting" 과 "뷰 Merging" 이 비용기반 쿼리 변환으로 전환됨

 filter_2 조건절 Pushing 중 "조인 조건 PushDown" 도 비용기반 쿼리 변환으로 전환

 filter_3 나머지는 변환된 쿼리가 항상 더 나은 성능을 제공하므로 비용기반으로 전환이 불필요


1.1 서브쿼리 Unnesting[편집]

/*+ UNNEST */ /*+ NO_UNNEST */

Setting the UNNEST_SUBQUERY session parameter to TRUE enables subquery unnesting. Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.
  1. 옵티마이져가 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화 하는것 이를 '서브쿼리 Unnesting' 이라고 함.
  2. 서브쿼리를 메인쿼리로 내몰아서(unnesting) 내서 메인쿼리와 조인 하도록 유도하는 하는 힌트, 즉 중첩(NEST)을 풀어서(UNNEST) 해제 하는 힌트 임.
    1. UNNEST는 뷰/서브쿼리를 해체한다. NO_UNNEST는 반대로 뷰/서브쿼리를 해체하지 말아라.
    2. FILTER 동작방식을 선택하지 않고 조인 동작방식 으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용
  3. 서브쿼리(Sub Query)는 메인 쿼리문의 WHERE 절에 나타날 때 중첩 됨.
    1. Oracle Database가 중첩된 서브 쿼리가있는 문을 측정(평가)할 때 서브 쿼리 부분을 여러 번 평가해야하며 일부 효율적인 액세스 경로 또는 조인을 간과 할 수 있음.
  4. 서브쿼리 중첩해제(SubQuery UNNEST)는 서브쿼리의 본문을 포함하는 명령문의 본문에 중첩해제(Unnest) 와 병합(Merge) 하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 함께 고려할 수 있도록 합니다.
  5. 변환된 쿼리의 예상 비용이 더 낮을 때만 Unnesting된 버전을 사용, 그렇지 않을 때는 원본 쿼리 그대로 필터 방식으로 최적화 한다.


  • UNNEST_SUBQUERY 세션 매개변수를 TRUE로 설정하면 하위 쿼리 중첩 해제가 활성화됩니다.
  • 서브 쿼리의 중첩을 해제하여 메인쿼리에 병합토록 지시하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 이들을 함께 고려할 수 있도록 합니다.

1.1.1 UNNEST 힌트[편집]

/*+ UNNEST */
  • 서브쿼리를 Unnesting 함을써 조인방식으로 최적화하도록 유도.
  • 즉,서브쿼리를 메인쿼리절의 FROM절로 올리도록 쿼리를 변형한다.

1.1.2 NO_UNNEST 힌트[편집]

/*+ NO_UNNEST */
  • 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화 하도록 유도.

1.1.3 옵티마이저가 중첩해제를 못하는 경우[편집]

  1. ROWNUM 가상컬럼
  2. 집합 연산자 중 하나
  3. 중첩집계함수,서브쿼리의 외부(바깥) 쿼리 블록이 아닌 쿼리블록에 상호연관함수가 포함 된 계층적 서브쿼리(connect by ) 및 서브쿼리가 포함됩니다.

1.1.4 옵티마이저가 자동 중첩해제(UNNEST) 하는 경우[편집]

  1. 연관관계가 없는 IN 절의 하위 쿼리
  2. IN 과 EXISTS 의 연관된 서브쿼리, 집계 함수 또는 GROUP BY 절을 포함하지 않는 경우
  3. 옵티마이저에 추가서브쿼리 유형의 중첩을 해제하도록 지시하여 확장 서브쿼리 중첩해제를 활성화 할 수 있습니다.
    1. 서브쿼리에 HASH_AJ 또는 MERGE_AJ 힌트를 지정하여 상관되지 않은 NOT IN 서브쿼리의 중첩을 해제 할 수 있음.
    2. 서브쿼리에 UNNEST 힌트를 지정하여 다른 서브쿼리를 중첩 해제 할 수 있습니다.
  • 반대로 NO_UNNEST 힌트는 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립되도록 하는 힌트임.
  • WHERE절에 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 하며 IN, EXISTS 관계없이 메인쿼리에서 읽히는 FILTER방식으로 처리되어 메인레코드 하나 읽을 때마다 서브쿼리를 반복적으로 수행하면서 조건에 맞는 데이터를 추출하는 것이다.
  • 이러한 필터방식이 최적의 성능을 보장하지 않으므로 옵티마이저는 조인문으로 변경후 최적화(Unnesting) 하거나 메인과 서브쿼리를 별도의 SUB PLAN으로 분리하여 각각 최적화를 수행하는데 이때 서브쿼리에 FILTER 연산이 나타난다.
  • 서브 쿼리를 Unnesting 하지 않는다면 메인쿼리의 건 마다 서브쿼리를 반복 수행하는 FILTER 연산자를 사용하기에 Unnesting 힌트는 효율적으로 사용한다면 성능 향상을 가져온다.

1.1.5 UNNEST 같이 쓰는 힌트[편집]

  1. SWAP_JOIN_INPUTS : 해쉬테이블로 올릴 테이블 지정
  2. NO_SWAP_JOIN_INPUTS : 해쉬테이블로 올리지 않을 테이블 지정
  3. NL_SJ : NL SEMI JOIN 으로 수행 되도록 지시
  4. HASH_SJ SWAP_JOIN_INPUTS : HASH SEMI JOIN 으로 수행 되도록 지시 하고 서브쿼리를 먼저수행(해시테이블로) 되도록 지시함.
  5. HASH_SJ NO_SWAP_JOIN_INPUTS : HASH SEMI JOIN 으로 수행 되도록 지시 하고 서브쿼리를 먼저수행(해시테이블로) 되지 않도록 지시함.
  6. NL_AJ : NOT EXISTS 쿼리를 NL JOIN ANTI 로 수행 되도록 함.
  7. HASH_AJ : NOT EXISTS 쿼리를 HASH JOIN ANTI 로 수행 되도록 함.

2 뷰 머징(View Merging)[편집]

2.1 뷰 머징 이란?[편집]

 emoji_objects 옵티마이저는 최적화 쿼리 수행을 위해 서브 쿼리블록을 풀어서 메인 쿼리와 결합(MERGE) 하려는 특성이 있음


 arrow_downward SQL 원본

SELECT * 
  FROM ( SELECT * FROM EMP WHERE JOB = 'SALESMAN' ) A
     , ( SELECT * FROM DEPT WHERE LOC = 'CHICAGO' ) B
 WHERE A.DEPTNO = B.DEPTNO;
  • 서브쿼리나 인라인 뷰처럼 쿼리를 블록화 할 시, 가독성이 더 좋기 때문에 습관적으로 사용

 arrow_downward View Merging 으로 오라클 내부에서 아래 형태로 SQL 변환

SELECT *  
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'SALESMAN'
   AND B.LOC = 'CHICAGO';
  • View Merging 이유 : 옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼을 수 있음

2.2 View Merging 제어 힌트[편집]

  1. /*+ MERGE */
  2. /*+ NO_MERGE */

2.3 단순 뷰(Simple View) Merging[편집]

  1. 가능한 조건
    1. 조건절과 조인문만을 포함하는 단순 뷰(Simple View)일 경우, no_merge 힌트를 사용하지 않는 한 언제든 Merging 발생
    2. group by, distinct 연산을 포함하는 복합뷰(Complex View)는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 가능
  2. 불가능한 조건
    1. 집합 연산자, connect by, rownum 등을 포함한 복합 뷰(Non-mergeable Views)는 뷰 Merging 불가능
-- Simple View 예제 
create or replace view emp_salesman as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from   emp
where job = 'SALESMAN';

2.3.1 Simple View 뷰 No Merging 최적화[편집]

SQL> select /*+ no_merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp_salesman e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.sal >= 1500 ;
 
      EMPNO ENAME      JOB              MGR        SAL DNAME
---------- ---------- --------- ---------- ---------- --------------
      7844 TURNER     SALESMAN        7698       1500 SALES
      7499 ALLEN      SALESMAN        7698       1600 SALES

Execution Plan
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     2 |   156 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |       |       |            |          |
|   2 |   NESTED LOOPS                 |              |     2 |   156 |     4   (0)| 00:00:01 |
|   3 |    VIEW                        | EMP_SALESMAN |     2 |   130 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP          |     2 |    58 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_SAL_IDX  |     8 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | PK_DEPT      |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT         |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("JOB"='SALESMAN')
   5 - access("SAL">=1500)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

2.3.2 Simple View 뷰 Merging 최적화[편집]

SQL> select /*+ merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp_salesman e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.sal >= 1500 ;

Execution Plan
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='SALESMAN')
   4 - access("SAL">=1500)
   5 - access("D"."DEPTNO"="DEPTNO")

 arrow_downward 일반 조인문

SQL> select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.job = 'SALESMAN'
  5  and    e.sal >= 1500;
Execution Plan

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."JOB"='SALESMAN')
   4 - access("E"."SAL">=1500)
   5 - access("D"."DEPTNO"="E"."DEPTNO")
* 단순 뷰를 Merging 할 경우, 파라미터 or 힌트 설정을 하지 않을 경우 일반 조인문과 똑같은 형태로 변환 후 처리

2.4 복합 뷰(Complex View) Merging[편집]

  • group by절 , select-list에 distinct연산자 포함하는 복합 뷰
  • _complex_view_merging 파라미터 값이 true로 설정할 때만 Merging 발생
  • 10g에서는 복합 뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 Merging했을 때의 비용이 더 낮을 때만 그것을 채택 (비용기반 쿼리 변환)
  • 10g 이전 _complex_view_merging 파라미터 기본 값 (8i : false, 9i : true)
  • complex_view_merging 파라미터를 true로 설정해도 Merging 될 수 없는 복합 뷰
    • 집합(set)연산자( union, union all, intersect, minus )
    • connect by절
    • ROWNUM pseudo 컬럼
    • select-list에 집계 함수(avg, count, max, min, sum)사용 : group by 없이 전체를 집계하는 경우를 말함
    • 분석 함수
    • 복합뷰를 포함한 쿼리 (뷰 머징 발생 시)
SQL> select d.dname, avg_sal_dept
  2  from   dept d
  3        ,(select deptno, avg(sal) avg_sal_dept from emp group by deptno) e
  4  where  d.deptno = e.deptno
  5  and    d.loc='CHICAGO';

DNAME          AVG_SAL_DEPT
-------------- ------------
SALES            1566.66667
 
Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     3 |    81 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |     3 |    81 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |       |       |            |          |
|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="DEPTNO")



 arrow_downward 복합뷰를 일반 조인절로 변경한 쿼리

SQL> select d.dname,avg(sal)
  2  from   dept d,emp e
  3  where  d.deptno=e.deptno
  4  and    d.loc='CHICAGO'
  5  group by d.rowid,d.dname;

DNAME            AVG(SAL)
-------------- ----------
SALES          1566.66667
 
Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    27 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |     1 |    27 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |       |       |            |          |
|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="E"."DEPTNO")


  • 뷰머징이 발생 할 경우
    • dept테이블에서 loc컬럼이 'CHICAGO'인 데이터를 먼저 필터링하고 조인, 조인대상 집합만 group by 실행
  • 뷰머징이 발생되지 않을 경우
    • emp 테이블의 모든 테이블을 group by 한 후 필터링하게 되면서 불필요한 레코드 엑세스 발생

2.5 비용기반 쿼리 변환의 필요성[편집]

9i : 복합 뷰를 무조건 머징 => 대부분 더 나은 성능 제공하지만 복합뷰 머징 시 그렇지 못할 때가 많음

  • no_merge 힌트 등 뷰안에 rownum 을 넣어주는 튜닝 기법 활용

10g 이후 비용기반 쿼리 변환 방식으로 처리

  • _optimizer_cost_based_transformation 파라미터 사용 → 설정값 5가지 (on, off, exhaustive, linear, iteraive)

on  : 적절한 것을 스스로 선택 exhaustive : cost가 가장 저렴한 것 선택 linear  : 순차적 비교 후 선택 literation : 변환이 수행 유무에 따른 cost를 비교하기 위한 경우의 수로 listeration 정의

opt_param 힌트 이용으로 쿼리 레벨에서 파라미터 변경가능 (10gR2부터 제공)

2.6 Merging 되지 않은 뷰의 처리방식[편집]

  1. 1단계 : 뷰머징 시행 시 오히려 비용이 증가된다고 판단(10g이후) 되거나, 부정확한 결과 집합 가능성이 있을 시 뷰머징 포기
  2. 2단계 : 뷰머징이 포기 할 경우 조건절 Pushing 시도
  3. 3단계 : 뷰 쿼리 블록을 개별적으로 최적화된 개별 플랜을 전체 실행계획에 반영 (즉, 뷰 쿼리 수행 결과를 엑세스 쿼리에 전달)
SQL> select /*+ leading(e) use_nl(d) */ *
  2  from   dept d
  3       ,(select /*+ NO_MERGE */ * from emp) e
  4  where  e.deptno = d.deptno;

14 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |  1498 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |    14 |  1498 |    17   (0)| 00:00:01 |
|   3 |    VIEW                      |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("E"."DEPTNO"="D"."DEPTNO")
     ※ 실행계획의 "VIEW" 로 표시된 오퍼레이션 단계가 추가 되었을 시, 실제로 다음 단계로 넘어가기 전 중간집합을 생성하는 것은 아님

2.8 조건절 이행[편집]

  • “A=B=C 조건 조인이면 (A=B) 이고 (A=C) 이다” 는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리변환
  • ”(A>B) 이고 (B>C) 이면 (A>C)이다“ 추론도 가능


  • 새로운 필터 조건이 추가되면서 조인 조건이 사라진다.
    • 새로운 필터 조건으로 인하여 조인 조건이 필요가 없어졌다고 생각하고 옵티마이저가 중복 산정하는 것을 방지하기 위함
  • 만약 조건절 이행이 작용해 조인 조건이 사라지고 이로 인해 비용이 잘못 계산되는 문제가 발생되면 사용자가 명시적으로 새로운 필터조건을 추가하거나 조인문을 가공하는 방법 사용
  • 조인문 가공 방법 예시 )
d.deptno = e.deptno + 0
  • 조건절 이행이 효과적인 사례

- 내부적으로 조건절 이행이 여러 곳에서 일어나고 있다. - 선분 이력을 between 조건으로 조회할 때는 인덱스 구성과 검색 범위에 따라 인덱스 스캔 효율에 많은 차이가 생긴다. 아래와 같은 쿼리에서도 범위를 더 제한적으로 줄일 수 있다.

  • SQL 예시)
select * 
        from 상품이력 a. 주문 b 
      where b.거래일자 between '20090101' and '20090131'
         and a.상품번호 = b.상품번호 
         and b.거래일자 between a.시작일자 and a.종료일자
- 위의 쿼리에서는 아래와 같이 조건절을 명시적으로 추가하여 튜닝 진행 
    ● 상품이력.시작일자 <= '20090131' 
    ● 상품이력.종료일자 >= '20090101'
- 보통 옵티마이저가 이들 조건을 묵시적으로 추가하고 최적화를 수행하지만 명시적으로 조건절을 추가하여 튜닝하는 방법도 고려 할것
  1. 튜닝사례 1
    1. 조인 조건은 아니지만 컬럼 간 관계 정보를 통해 추가적인 조건절이 생성되었다.
    2. 옵티마이저에게 많은 정보를 제공할수록 SQL 성능이 더 좋아진다.
  2. 튜닝사례 2
    1. 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해주는 것이 매우 중요함

2.9 공통 표현식 제거[편집]

  1. 같은 조건식이 여러 곳에서 반복 사용될 경우 해당 조건식이 각 로우당 한 번씩만 평가되도록 오라클이 쿼리를 변환
  2. _eliminate_common_subexpr 파라미터로 제어
    1. 예시) 필터조건이 중복으로 기술 되어 비교연산이 두 번씩 일어나는 경우
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)

===> 옵티마이져가 아래와 같이 변환

select * from emp e, dept d
 where e.deptno = d.deptno
   and e.job = 'CLERK'
   and (d.loc='DALLAS' or e.sal >= 1000)

 filter_1 비교 연산을 줄여서 성능개선

 filter_2 새로운 인덱스 엑세스 조건을 사용할수 있도록 함.

-- 인덱스 생성 
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);                                    
                                                                                              
                                                                                              
----------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     3 |   150 |
|   1 |   NESTED LOOPS                 |             |     3 |   150 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP         |     3 |    96 |
|*  3 |    INDEX RANGE SCAN            | EMP_JOB_IDX |     3 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID  | DEPT        |     1 |    18 |
|*  5 |    INDEX UNIQUE SCAN           | DEPT_PK     |     1 |       |
----------------------------------------------------------------------
                                                                                              
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")

2.10 outer 조인을 Inner 조인으로 변환[편집]

2.11 실체화 뷰 쿼리로 재작성[편집]

2.12 star 변환[편집]

2.13 outer 조인 뷰에 대한 조인 조건 Pushdown[편집]

2.14 OR 절 튜닝 (OR-Expansion)[편집]

  1. WHERE절에서 OR 구문 사용시 옵티마이저가 UNION ALL로 분리 하는 작업을 대신해 주는 경우를 'OR-Expansion'이라 함.
  2. OR절에 사용된 컬럼이 인덱스에 있어야 함
  3. 힌트
    1. USE_CONCAT(OR-Expansion 유도)
    2. NO_EXPAND(OR-Expansion 방지)
  • OR-Expansion 일어났을 때의 실행계획과 Predicate 정보
-----------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     7 |   224 |
|   1 |  CONCATENATION   <== 여기            |                |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     3 |    96 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     3 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   128 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)

2.14.1 NVL()/DECODE() 조건식에 대한 OR-Expansion[편집]

select * from emp
where deptno = nvl(:deptno, deptno)
and ename like :ename || '%'

또는 

select * from emp
where deptno = decode(:deptno, null, deptno, :deptno)
and ename like :ename || '%'
  •  :deptno 변수 값 입력 여부에 따라 다른 인덱스를 사용 함
  •  :deptno에 null값을 입력했을 때(위쪽 브랜치)는 EMP_ENAME_IDX 사용, null값이 아닌 경우(아래쪽 브랜치) EMP_DEPTNO_IDX 사용
----------------------------------------------------------------------
| Id | Operation                       |Name           |Rows | Bytes |
----------------------------------------------------------------------
| 0  | SELECT STATEMENT                |               |   3 |   111 |
| 1  |  CONCATENATION                  |               |     |       |
| 2  |   FILTER                        |               |     |       |
| 3  |    TABLE ACCESS (BY INDEX ROWID)|EMP            |   2 |    74 |
| 4  |     INDEX (RANGE SCAN)          |EMP_ENAME_IDX  |   2 |       |
| 5  |   FILTER                        |               |     |       |
| 6  |    TABLE ACCESS (BY INDEX ROWID)|EMP            |   1 |    37 |
| 7  |     INDEX (RANGE SCAN)          |EMP_DEPTNO_IDX |   5 |       |
----------------------------------------------------------------------
  • _or_expand_nvl_predicate 파라미터로 제어
  • nvl,decode를 여러 컬럼에 사용한 경우 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어나기 때문에 옵션조건이 복잡할 때는 수동으로 union all 분기를 해 줘야만 한다.

3 쿼리 변환 두 가지 방식[편집]

3.1 휴리스틱 쿼리 변환[편집]

 : 결과만 보장된다면 무조건 쿼리 변환을 수행 

3.2 비용기반 쿼리 변환[편집]

 : 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.