"튜닝 쿼리 변형"의 두 판 사이의 차이
DB CAFE
(→공통 표현식 제거) |
(→쿼리 변환 종류) |
||
(같은 사용자의 중간 판 하나는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
== 쿼리 변환 종류 == | == 쿼리 변환 종류 == | ||
− | + | {{틀:타이틀 투명 | |
− | + | |제목="서브쿼리 Unnesting" 과 "뷰 Merging" 이 비용기반 쿼리 변환으로 전환됨 | |
− | + | |아이콘=filter_1 | |
+ | }} | ||
+ | {{틀:타이틀 투명 | ||
+ | |제목=조건절 Pushing 중 "조인 조건 PushDown" 도 비용기반 쿼리 변환으로 전환 | ||
+ | |아이콘=filter_2 | ||
+ | }} | ||
+ | {{틀:타이틀 투명 | ||
+ | |제목=나머지는 변환된 쿼리가 항상 더 나은 성능을 제공하므로 비용기반으로 전환이 불필요 | ||
+ | |아이콘=filter_3 | ||
+ | }} | ||
+ | ---- | ||
=== 서브쿼리 Unnesting === | === 서브쿼리 Unnesting === | ||
{{틀:고지상자 | {{틀:고지상자 | ||
119번째 줄: | 129번째 줄: | ||
</source> | </source> | ||
− | + | {{틀:타이틀 투명 | |
− | + | |아이콘=filter_1 | |
− | + | |제목= 비교 연산을 줄여서 성능개선 | |
+ | }} | ||
+ | {{틀:타이틀 투명 | ||
+ | |아이콘=filter_2 | ||
+ | |제목= 새로운 인덱스 엑세스 조건을 사용할수 있도록 함. | ||
+ | }} | ||
<source lang=sql> | <source lang=sql> | ||
-- 인덱스 생성 | -- 인덱스 생성 |
2023년 6월 20일 (화) 22:08 기준 최신판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 쿼리 변환 종류
- 2 뷰 머징(View Merging)
- 2.1 뷰 머징 이란?
- 2.2 View Merging 제어 힌트
- 2.3 단순 뷰(Simple View) Merging
- 2.4 복합 뷰(Complex View) Merging
- 2.5 비용기반 쿼리 변환의 필요성
- 2.6 Merging 되지 않은 뷰의 처리방식
- 2.7 조건절 Pushing
- 2.8 조건절 이행
- 2.9 공통 표현식 제거
- 2.10 outer 조인을 Inner 조인으로 변환
- 2.11 실체화 뷰 쿼리로 재작성
- 2.12 star 변환
- 2.13 outer 조인 뷰에 대한 조인 조건 Pushdown
- 2.14 OR 절 튜닝 (OR-Expansion)
- 3 쿼리 변환 두 가지 방식
1 쿼리 변환 종류[편집]
filter_1 "서브쿼리 Unnesting" 과 "뷰 Merging" 이 비용기반 쿼리 변환으로 전환됨
filter_2 조건절 Pushing 중 "조인 조건 PushDown" 도 비용기반 쿼리 변환으로 전환
filter_3 나머지는 변환된 쿼리가 항상 더 나은 성능을 제공하므로 비용기반으로 전환이 불필요
1.1 서브쿼리 Unnesting[편집]
- UNNEST_SUBQUERY 세션 매개변수를 TRUE로 설정하면 하위 쿼리 중첩 해제가 활성화됩니다.
- 서브 쿼리의 중첩을 해제하여 메인쿼리에 병합토록 지시하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 이들을 함께 고려할 수 있도록 합니다.
1.1.1 UNNEST 힌트[편집]
/*+ UNNEST */
- 서브쿼리를 Unnesting 함을써 조인방식으로 최적화하도록 유도.
- 즉,서브쿼리를 메인쿼리절의 FROM절로 올리도록 쿼리를 변형한다.
1.1.3 옵티마이저가 중첩해제를 못하는 경우[편집]
- ROWNUM 가상컬럼
- 집합 연산자 중 하나
- 중첩집계함수,서브쿼리의 외부(바깥) 쿼리 블록이 아닌 쿼리블록에 상호연관함수가 포함 된 계층적 서브쿼리(connect by ) 및 서브쿼리가 포함됩니다.
1.1.4 옵티마이저가 자동 중첩해제(UNNEST) 하는 경우[편집]
- 연관관계가 없는 IN 절의 하위 쿼리
- IN 과 EXISTS 의 연관된 서브쿼리, 집계 함수 또는 GROUP BY 절을 포함하지 않는 경우
- 옵티마이저에 추가서브쿼리 유형의 중첩을 해제하도록 지시하여 확장 서브쿼리 중첩해제를 활성화 할 수 있습니다.
- 서브쿼리에 HASH_AJ 또는 MERGE_AJ 힌트를 지정하여 상관되지 않은 NOT IN 서브쿼리의 중첩을 해제 할 수 있음.
- 서브쿼리에 UNNEST 힌트를 지정하여 다른 서브쿼리를 중첩 해제 할 수 있습니다.
- 반대로 NO_UNNEST 힌트는 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립되도록 하는 힌트임.
- WHERE절에 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 하며 IN, EXISTS 관계없이 메인쿼리에서 읽히는 FILTER방식으로 처리되어 메인레코드 하나 읽을 때마다 서브쿼리를 반복적으로 수행하면서 조건에 맞는 데이터를 추출하는 것이다.
- 이러한 필터방식이 최적의 성능을 보장하지 않으므로 옵티마이저는 조인문으로 변경후 최적화(Unnesting) 하거나 메인과 서브쿼리를 별도의 SUB PLAN으로 분리하여 각각 최적화를 수행하는데 이때 서브쿼리에 FILTER 연산이 나타난다.
- 서브 쿼리를 Unnesting 하지 않는다면 메인쿼리의 건 마다 서브쿼리를 반복 수행하는 FILTER 연산자를 사용하기에 Unnesting 힌트는 효율적으로 사용한다면 성능 향상을 가져온다.
1.1.5 UNNEST 같이 쓰는 힌트[편집]
- SWAP_JOIN_INPUTS : 해쉬테이블로 올릴 테이블 지정
- NO_SWAP_JOIN_INPUTS : 해쉬테이블로 올리지 않을 테이블 지정
- NL_SJ : NL SEMI JOIN 으로 수행 되도록 지시
- HASH_SJ SWAP_JOIN_INPUTS : HASH SEMI JOIN 으로 수행 되도록 지시 하고 서브쿼리를 먼저수행(해시테이블로) 되도록 지시함.
- HASH_SJ NO_SWAP_JOIN_INPUTS : HASH SEMI JOIN 으로 수행 되도록 지시 하고 서브쿼리를 먼저수행(해시테이블로) 되지 않도록 지시함.
- NL_AJ : NOT EXISTS 쿼리를 NL JOIN ANTI 로 수행 되도록 함.
- 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 제어 힌트[편집]
- /*+ MERGE */
- /*+ NO_MERGE */
2.3 단순 뷰(Simple View) Merging[편집]
- 가능한 조건
- 조건절과 조인문만을 포함하는 단순 뷰(Simple View)일 경우, no_merge 힌트를 사용하지 않는 한 언제든 Merging 발생
- group by, distinct 연산을 포함하는 복합뷰(Complex View)는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 가능
- 불가능한 조건
- 집합 연산자
- 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단계 : 뷰머징 시행 시 오히려 비용이 증가된다고 판단(10g이후) 되거나, 부정확한 결과 집합 가능성이 있을 시 뷰머징 포기
- 2단계 : 뷰머징이 포기 할 경우 조건절 Pushing 시도
- 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.7 조건절 Pushing[편집]
2.8 조건절 이행[편집]
- 새로운 필터 조건이 추가되면서 조인 조건이 사라진다.
- 새로운 필터 조건으로 인하여 조인 조건이 필요가 없어졌다고 생각하고 옵티마이저가 중복 산정하는 것을 방지하기 위함
- 만약 조건절 이행이 작용해 조인 조건이 사라지고 이로 인해 비용이 잘못 계산되는 문제가 발생되면 사용자가 명시적으로 새로운 필터조건을 추가하거나 조인문을 가공하는 방법 사용
- 조인문 가공 방법 예시 )
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
- 조인 조건은 아니지만 컬럼 간 관계 정보를 통해 추가적인 조건절이 생성되었다.
- 옵티마이저에게 많은 정보를 제공할수록 SQL 성능이 더 좋아진다.
- 튜닝사례 2
- 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해주는 것이 매우 중요함
2.9 공통 표현식 제거[편집]
- 같은 조건식이 여러 곳에서 반복 사용될 경우 해당 조건식이 각 로우당 한 번씩만 평가되도록 오라클이 쿼리를 변환
- _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)
===> 옵티마이져가 아래와 같이 변환
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)[편집]
- WHERE절에서 OR 구문 사용시 옵티마이저가 UNION ALL로 분리 하는 작업을 대신해 주는 경우를 'OR-Expansion'이라 함.
- OR절에 사용된 컬럼이 인덱스에 있어야 함
- 힌트
- USE_CONCAT(OR-Expansion 유도)
- 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 분기를 해 줘야만 한다.