07 OR-Expansion ( OR To Union All Conversion )
1) OR-Expansion 기본
- OR, NVL, DECODE 함수를 사용한 조건절을 이용하여 Union all로 쿼리 변환
- 분기된 쿼리에 중복 액세스되는 영역의 비중이 작을수록 효과적
- 비용기반 쿼리 변환 ( 10053 trace )
or-expansion-subheap (delete addr=0x0000000015365678, in-use=14848, alloc=16408)
or-expansion is worse cost:4.000954 - 브랜치별(분기쿼리) 실행계획 수립 가능
- 제어 힌트
use_concat/no_expand - 제어 파라미터
_no_or_expansion
alter session set "_no_or_expansion" = true;
/* 아래 쿼리 수행 시 or 조건이므로 Full Table Scan 또는 Index Combine 동작 */
SELECT *
FROM EMP
WHERE JOB = 'CLERK' OR DEPTNO = 20
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 336 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 336 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
/* JOB, DEPTNO 각각 생성된 인덱스를 사용하고 싶으면 Union All 형태로 쿼리 작성
LNNVL 함수 : false, unknown인 경우 true 리턴, true인 경우 false 리턴 */
SELECT *
FROM EMP
WHERE JOB = 'CLERK'
UNION ALL
SELECT *
FROM EMP
WHERE DEPTNO = 20
AND LNNVL(JOB = 'CLERK')
SELECT /*+ USE_CONCAT */ *
FROM EMP
WHERE JOB = 'CLERK' OR DEPTNO = 20
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 312 | 4(0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | 1(0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 12 | 288 | 2(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_EMP_JOB | 12 | | 1(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=20)
4 - filter(LNNVL("DEPTNO"=20))
5 - access("JOB"=' CLERK ')
/* _no_or_expansion 제어 파라미터 변경에 따른 실행계획 */
SQL> alter session set "_no_or_expansion" = true;
Session alteted.
SQL> SELECT /*+ USE_CONCAT */ *
FROM EMP
WHERE JOB = 'CLERK' OR DEPTNO = 20
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 336 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 336 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
2) OR-Expansion 브랜치별 조인 순서 최적화
- OR-Expansion에 의해 분기된 브랜치마다 각기 다른 조인순서를 가질 수 있다
SQL> select /*+ no_expand */ *
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 2000
and (e.job = 'SALESMAN' or d.loc = 'CHICAGO')
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_SAL_IDX | 1 | | 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 | 20 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."SAL">=2000)
5 - access("D"."DEPTNO"="E"."DEPTNO")
6 - filter("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO')
* 드라이빙 조건( SAL >= 2000 )의 변별력이 나빠 조인 액세스 건수가 많고, 최종 필터되는
OR 조건 ( JOB ='SALESMAN' OR LOC ='CHICAGO' ) 의 변별력이 좋다면 비효율적
SQL> select /*+ USE_CONCAT */ *
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 2000
and (e.job = 'SALESMAN' or d.loc = 'CHICAGO')
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 6 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 7 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 24 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
SQL> select /*+ USE_CONCAT FIRST_ROWS */ *
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 2000
and (e.job = 'SALESMAN' or d.loc = 'CHICAGO')
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 6 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 7 | | 0 (0)| 00:00:01
|* 7 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 24 | 1 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
3) 같은 컬럼에 대한 OR-Expansion
(쿼리)
SQL> select * from emp
where (deptno = 10 or deptno = 20)
and ename = :ename
SQL> select * from emp
where deptno in (10, 20)
and ename = :ename
1. 9i
1. OR조건이나 IN-LIST도 OR-Expansion 작동이 가능
2. OR-Expansion 유도 시 뒤쪽에 놓인 값이 항상 먼저 출력
2.10g
1. IN-List Iterator ( 기본방식 )
2. CPU 비용모델 : 카디널리디가 작은 값이 먼저 출력
3. ordered_predicates 힌트, IO 비용 모델로 수행 시 9i 이전처럼 뒤쪽에 놓인 값이 먼저 출력
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 168 | 2(0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 7 | 168 | 2(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 14 | | 1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ENAME"=:ENAME)
3 - access("DEPTNO"=10 OR "DEPTNO"=20)
/* USE_CONCAT 힌트에 인자를 제공하여 OR-Expansion 유도
USE_CONCAT 인자
8 : Inlist를 사용할 수 있는 경우에는 union all로 분리하지 말것을 강제하는 힌트
1 : 가능한 경우 모든 Unoin all 로 분리하라 (use_concat 힌트 제대로 알기 참조) */
SQL> select /*+ QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */ * from emp
where (deptno = 10 or deptno = 20)
and ename = :ename
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 192 | 4(0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | 1(0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 7 | 168 | 2(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 13 | | 1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
/* 10G 이후에 비교 연산자가 '=' 가 아닐 때는 USE_CONCAT 힌트만으로 유도 가능 */
SQL> select /*+ USE_CONCAT */ * from emp
where (deptno = 10 or deptno >= 20)
and ename = :ename
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 168 | 4(0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | 1(0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 144 | 2(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 13 | | 1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
4) nvl/decode 조건식에 대한 OR-Expansion
- 조건 조건값(:deptno)에 따라 다른 인덱스 사용
- nvl/decode를 여러 컬럼에 대해 사용했을때는 변별력이 가장 좋은 컬럼 기준으로 한번만 분기
옵션 조건이 복잡한 경우 수동 union all 분기가 필요 - 제어 파라미터 : _or_expand_nvl_predicate
SQL> select * from emp
where deptno = nvl(:deptno, deptno)
and ename like :ename || '%'
SQL> select * from emp
where deptno = decode(:deptno,null, deptno, :deptno)
and ename like :ename || '%'
/* OR-Expansion 쿼리 변환 */
SQL> select * from emp
where :deptno is null
and deptno is not null
and ename like :ename || '%'
union all
select * from emp
where :deptno is not null
and deptno = :deptno
and ename like :ename || '%'
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 288 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 8 | 192 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_ENAME_IDX | 8 | | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 96 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 7 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:DEPTNO IS NULL)
3 - filter("DEPTNO" IS NOT NULL)
4 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
5 - filter(:DEPTNO IS NOT NULL)
6 - filter("ENAME" LIKE :ENAME||'%')
7 - access("DEPTNO"=:DEPTNO)
SQL> select /*+ QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */ * from emp
where (deptno = decode(:deptno,null, deptno, :deptno)
OR ename = decode(:ename, null, ename, :ename) )
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 600 | 7(0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 336 | 2(0)| 00:00:01 |
|* 4 | INDEX FULL SCAN | EMP_ENAME_IDX | 14 | | 1(0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 7 | 168 | 2(0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_ENAME_IDX | 7 | | 1(0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | EMP | 4 | 96 | 3(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:ENAME IS NULL)
4 - filter("ENAME" IS NOT NULL)
5 - filter(:ENAME IS NOT NULL)
7 - access("ENAME"=:ENAME)
8 - filter("DEPTNO"=DECODE(:DEPTNO,NULL,"DEPTNO",:DEPTNO) AND
LNNVL("ENAME"=DECODE(:ENAME,NULL,"ENAME",:ENAME)))
08 공통 표현식 제거
- 같은 조건식이 여러 곳에서 반복 사용될 경우 쿼리변환
- 비교연산에 대한 수행이 줄고, 새로운 인덱스 액세스 조건으로 수행 가능
- 제어 파라미터 : _eliminate_common_subexpr
- _eliminate_common_subexpr = false로 변환히 오라클 선택
1. OR-Expansion
2. Full Scan
SQL> 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 | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 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 | 20 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."JOB"='CLERK')
5 - access("E"."DEPTNO"="D"."DEPTNO")
6 - filter("D"."LOC"='DALLAS' OR "E"."SAL">=1000)
<쿼리 변환>
SQL> select * from emp e, dept d
where e.deptno = d.deptno
and e.job = 'CLERK'
and (d.loc = 'DALLAS' or e.sal >= 1000)
SQL> alter session set "_eliminate_common_subexpr" = false;
/* 1. OR-Expansion */
SQL> 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 | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 6 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 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 | 20 | 1 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 24 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."SAL">=1000)
5 - access("E"."JOB"='CLERK')
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - filter("D"."LOC"='DALLAS')
11 - access("E"."JOB"='CLERK')
12 - filter("E"."DEPTNO"="D"."DEPTNO" AND (LNNVL("E"."DEPTNO"="D"."DEPTNO") OR
LNNVL("E"."JOB"='CLERK') OR LNNVL("E"."SAL">=1000)))
/* 2. Full Scan */
SQL> 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)
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 44 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 336 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 0 (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)
09. Outer 조인을 Inner 조인으로 변환
- 일부 조건절에 Outer기호(+)를 빠뜨리면 Inner 조인으로 변환
- 쿼리 변환을 하는 의미는 조인 순서를 자유롭게 결정하기 위함
- Outer NL조인, Outer 소트머지 조인, Outer Hash 조인 : Outer기호가 없는 테이블이 드라이빙
- Outer Hash 조인 : swap_join_inputs 힌트로 드라이빙 순서 조정이 가능
SQL> select * from emp e, dept d
where d.deptno(+) = e.deptno
and d.loc = 'DALLAS'
and e.sal >= 1000;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 264 | 3(0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 6 | 264 | 3(0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPT | 1 | 20 | 2(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 7 | | 0(0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 144 | 1(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."LOC"='DALLAS')
4 - access("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL">=1000)
/* ANSI Outer 조인문인 경우 조건절 위치 조심 */
SQL> select *
from dept d left outer join emp e on d.deptno = e.deptno
where e.sal > 1000
=> Inner Join으로 변환
SQL> select *
from dept d left outer join emp e on d.deptno = e.deptno and e.sal > 1000
=> EMP 테이블의 sal > 1000 집합으로 Outer Join 수행
10. 실체화 뷰 쿼리로 재작성
- 실체화 뷰는 물리적으로 실제 데이터를 갖는다
- 실체화 뷰(MV) 용도
- 데이터 복제
- Join View
- Aggregate View
- MV 특징
- refresh 옵션을 이용해 집계 테이블 자동 관리
- Query Rewrite 지원
- 제어 파라미터 : query_rewrite_enabled
-상품테이블생성
create table 상품
as
select rownum 상품ID, dbms_random.string('u', 10) 상품명
from dual connect by level <= 10;
-고객테이블생성
create table 고객
as
select rownum 고객ID, dbms_random.string('a', 10) 고객명
from dual connect by level <= 100;
-판매테이블생성
create table 판매
as
select 상품ID, 고객ID, 판매일련번호
, to_char(to_date('20081231', 'yyyymmdd')+ 상품ID, 'yyyymmdd') 판매일자
, round(dbms_random.value(1, 100)) 판매수량
, round(dbms_random.value(1000, 100000), -2) 판매금액
from 상품, 고객, (select rownum 판매일련번호 from dual connect by level <= 100);
-구체화된 뷰로그 생성
기준 테이블에 발생한 트랜잭션을 실시간 반영할 수 있도록 MV 로그 생성
create materialized view log on 판매
with sequence, rowid(상품ID, 고객ID, 판매일자, 판매수량, 판매금액)
including new values;
-구체화된 뷰 생성
create materialized view -월고객상품별_MV
build immediate - 바로 MV 데이터 생성
refresh fast on commit - 커밋 시점에 MV에 실시간 반영
enable query rewrite - query rewrite 활성화
as
select 상품ID, 고객ID, substr(판매일자, 1, 6) 판매월
, sum(판매수량) 판매수량, sum(판매금액) 판매금액
from 판매
group by 상품ID, 고객ID, substr(판매일자, 1, 6);
-query rewrite 를 위한 쿼리수행
select p.상품명, c.고객명, substr(s.판매일자, 1, 6) 판매월
, sum(s.판매수량) 판매수량, sum(s.판매금액) 판매금액
from 판매 s, 상품 p, 고객 c
where s.상품ID = p.상품ID
and s.고객ID = c.고객ID
group by p.상품명, c.고객명, substr(s.판매일자, 1, 6);
Execution Plan
----------------------------------------------------------
Plan hash value: 4290105475
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 3994K| 12 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 1000 | 3994K| 12 (17)| 00:00:01 |
|* 2 | HASH JOIN | | 1000 | 3994K| 11 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | 고객 | 100 | 196K| 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 1000 | 2026K| 8 (13)| 00:00:01 |
| 5 | TABLE ACCESS FULL | 상품 | 10 | 20150 | 3 (0)| 00:00:01 |
| 6 | MAT_VIEW REWRITE ACCESS FULL| 월고객상품| 1000 | 60000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("월고객상품별_MV"."고객ID"="C"."고객ID")
4 - access("월고객상품별_MV"."상품ID"="P"."상품ID")
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.063 0.062 0 174 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.016 0.006 0 12 0 1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.078 0.068 0 186 0 1000
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1000 HASH GROUP BY (cr=12 pr=0 pw=0 time=713 us cost=12 size=4090000 card=1000)
1000 HASH JOIN (cr=12 pr=0 pw=0 time=2622 us cost=11 size=4090000 card=1000)
100 TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=99 us cost=3 size=201500 card=100)
1000 HASH JOIN (cr=9 pr=0 pw=0 time=1873 us cost=8 size=2075000 card=1000)
10 TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=9 us cost=3 size=20150 card=10)
1000 MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=374 us cost=4 size=60000 card=1000)
-사용자는 판매 테이블을 쿼리 했지만 옵티마이저에 의해 월고객상품별_MV가 엑세스되었다.
-no_rewrite 힌트를 통한 쿼리 수행
select /*+ no_rewrite */ p.상품명, c.고객명, substr(s.판매일자, 1, 6) 판매월
, sum(s.판매수량) 판매수량, sum(s.판매금액) 판매금액
from 판매 s, 상품 p, 고객 c
where s.상품ID = p.상품ID
and s.고객ID = c.고객ID
group by p.상품명, c.고객명, substr(s.판매일자, 1, 6);
Execution Plan
----------------------------------------------------------
Plan hash value: 1609446651
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 104K| 405M| 132 (7)| 00:00:02 |
| 1 | HASH GROUP BY | | 104K| 405M| 132 (7)| 00:00:02 |
|* 2 | HASH JOIN | | 104K| 405M| 127 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL | 고객 | 100 | 196K| 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 104K| 205M| 123 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL| 상품 | 10 | 20150 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| 판매 | 104K| 5894K| 119 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.141 0.135 0 425 0 1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.141 0.136 0 425 0 1000
Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1000 HASH GROUP BY (cr=425 pr=0 pw=0 time=570 us cost=132 size=425409544 card=104063)
100000 HASH JOIN (cr=425 pr=0 pw=0 time=211762 us cost=127 size=425409544 card=104063)
100 TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=198 us cost=3 size=201500 card=100)
100000 HASH JOIN (cr=422 pr=0 pw=0 time=118413 us cost=123 size=215722599 card=104063)
10 TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=9 us cost=3 size=20150 card=10)
100000 TABLE ACCESS FULL 판매 (cr=419 pr=0 pw=0 time=25319 us cost=119 size=6035654 card=104063)
-query rewrite 기능 비활성화를 통해 비효율이 발생한것을 확인할 수 있다.
11 집합 연산을 조인으로 변환
- Intersect, Minus 같은 집합 연산을 조인 형태로 변환
- 제어 파라미터 : _convert_set_to_join ( Default : false )
- SYS_OP_MAP_NONNULL : null 값끼리 '=' 비교 시 true 리턴
SQL> select job, deptno from emp
minus
select job, deptno from emp
where deptno = 10;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 282 | 7(58)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 14 | 126 | 4(25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 13 | 156 | 3(34)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 156 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 13 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DEPTNO"=10)
SQL> alter session set "_convert_set_to_join" = true;
SQL> select job, deptno from emp
minus
select job, deptno from emp
where deptno = 10;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 36 | 7(29)| 00:00:01 |
| 1 | HASH UNIQUE | | 2 | 36 | 7(29)| 00:00:01 |
|* 2 | HASH JOIN ANTI | | 14 | 252 | 6(17)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 117 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 13 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("JOB") AND
SYS_OP_MAP_NONNULL("DEPTNO")=SYS_OP_MAP_NONNULL("DEPTNO"))
5 - access("DEPTNO"=10)
SQL> select distinct job, deptno from emp e
where not exists (
select 'x' from emp
where deptno = 10
and SYS_OP_MAP_NONNULL(JOB)=SYS_OP_MAP_NONNULL(e.JOB)
and SYS_OP_MAP_NONNULL(DEPTNO)=SYS_OP_MAP_NONNULL(e.DEPTNO)
)
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 36 | 7(29)| 00:00:01 |
| 1 | HASH UNIQUE | | 2 | 36 | 7(29)| 00:00:01 |
|* 2 | HASH JOIN ANTI | | 14 | 252 | 6(17)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 117 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 13 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("JOB") AND
SYS_OP_MAP_NONNULL("DEPTNO")=SYS_OP_MAP_NONNULL("DEPTNO"))
5 - access("DEPTNO"=10)
12. 기타 쿼리 변환
(1) 조인 컬럼에 IS NOT NULL 조건 추가
- 조인 컬럼 deptno가 null인 데이터는 조인 액세스가 불필요하다
- 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있으면 쿼리 성능 향상에 도움이 된다
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
and e.edptno is not null
and d.deptno is not null
SQL> create table t_emp as select * from emp , (select rownum no from dual connect by level <=1000);
테이블이 생성되었습니다.
SQL> update t_emp set deptno = null;
14000 행이 갱신되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> create index t_emp_idx on t_emp(sal);
인덱스가 생성되었습니다.
select /*+ ordered use_nl(d) index(e t_emp_idx) index(d dept_pk) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900;
Execution Plan
----------------------------------------------------------
Plan hash value: 3232964574
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 10359 (1)| 00:02:05 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
| 2 | NESTED LOOPS | | 1 | 52 | 10359 (1)| 00:02:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 9535 | 363K| 810 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 9535 | | 27 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."SAL"<=2900)
6 - access("D"."DEPTNO"="E"."DEPTNO")
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us cost=11050 size=52 card=1)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=12783 us cost=809 size=399243 card=10237)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=3417 us cost=27 size=0 card=10237)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
1. 옵티마이저에 의해 추가된 필터 조건은 없다.
2. t_emp테이블에서 10,000 레코드를 읽었지만 dept 테이블과의 조인 액세스가 발생되지 않은것을 확인
3. is null 조건을 따로 기술 하지 않더라도 읽은 값이 null일때는 조인 액세스를 하지 않는다
4. Inner 테이블을 Full Table Scan으로 액세스할 때는 아래처럼 조인 액세스가 발생
Select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=70841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=70841 pr=0 pw=0 time=0 us cost=14691 size=52 card=1)
10000 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=16200 us cost=809 size=399243 card=10237)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=5442 us cost=27 size=0 card=10237)
0 TABLE ACCESS FULL DEPT (cr=70000 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
컬럼 통계를 수집하고 나면 옵티마이저가 자동으로 추가해 주지만 null 5% 이상일 때만 이 기능이 작동한다.
begin
dbms_stats.gather_table_stats(user, 't_emp'
, method_opt=>'for all columns', no_invalidate=>false);
end;
/
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=0 us cost=807 size=34 card=1)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=0 us cost=804 size=21 card=1)
10000 INDEX RANGE SCAN T_EMP_IDX (cr=22 pr=0 pw=0 time=3417 us cost=22 size=0 card=10001)
0 TABLE ACCESS FULL DEPT (cr=0 pr=0 pw=0 time=0 us cost=3 size=13 card=1)
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 807 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
| 2 | NESTED LOOPS | | 1 | 34 | 807 (1)| 00:00:10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 21 | 804 (1)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 10001 | | 22 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 13 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO" IS NOT NULL)
4 - access("E"."SAL"<=2900)
5 - filter("D"."DEPTNO"="E"."DEPTNO")
1. dept 테이블을 10,000번 Full Scan하면서 발생하던 70,000개의 블록 I/O가 사라졌다.
2. 옵티마이저에 의해 e.deptno is not null 조건이 추가되었음을 알 수 있다.
3. t_emp테이블을 액세스하면서 발생한 블록 I/O 통계정보를 수집하기 전과 똑같이 841개이다.
4. t_emp_idx 인덱스에 deptno 컬럼을 추가하고 다시 수행하여 I/O가 841에서 23으로 준다.
select /*+ ordered use_nl(d) index(e t_emp_idx) */
count(e.empno), count(d.dname)
from t_emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2900
Rows Row Source Operation
------- -----------------------------------------------------------------------
1 SORT AGGREGATE (cr=23 pr=27 pw=0 time=0 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=0 us)
0 NESTED LOOPS (cr=23 pr=27 pw=0 time=0 us cost=24 size=34 card=1)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=23 pr=27 pw=0 time=0 us cost=24 size=21 card=1)
0 INDEX RANGE SCAN T_EMP_IDX (cr=23 pr=27 pw=0 time=0 us cost=24 size=0 card=1)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
1. 조인 컬럼에 is not null 조건을 추가해 주면 NL 조인뿐만 아니라 해시 조인, 소트 머인조인 시에도 효과를 발휘한다.
2. 해시 조인시 Build Input을 읽어 해시 맵을 만들 때 적은 메모리를 사용한다.
3. Probe Input을 읽을 때도 null 값인 레코드를 제외함으로서 탐색 횟수를 줄일 수 있다.
4. 소트 머지 조인시 양쪽 테이블에서 조인 컬럼 null인 레코드를 제외한다면 연산 횟수를 줄일 수 있다.
5. 이러한 null을 체크하는 옵티마이저의 성능은 null 값이 5%을 넘을 때 변환을 시도하기 때문에
쿼리 작성시 직접 조건에 추가해 주는것이 불필요한 액세스를 줄일 수 있다.
(2) 필터 조건 추가
- 쿼리를 수행할 때 사용자가 :mx보다 :mn변수에 더 큰 값을 입력한다면 결과는 공집합
select * from emp
where sal between :mn and :mx
1. 사전에 두 값을 비교해 알 수 있음에도 쿼리를 수행하고서야 공집합을 출력한다면 매우 비합리적
2. 8i까지는 사용자가 한참을 기다려야만 했다. 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))
2 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets --블록 I/O전혀없음
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
1. 실행계획 상으로는 Table Full Scan을 수행하고나서 필터 처리가 일어나는것 같지만, 실제는 Table Full Scan 자체를 생략한 것이다.
2. 바인드변수대신 상수값으로 조회할 때도 filter 조건이 추가되는데 9i와 10g는 조금 다르게 처리된다.
9i : filter(5000 <=100)
10g이상 : filter(null is not null)
select * from emp
where sal between 5000 and 100
Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 24 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("SAL">=5000 AND "SAL"<=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
663 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
3. 9i에서 통계정보가 없으면 RBO 모드로 작동해서 위와 같은 쿼리 변환이 일어나지 않는다.
4. 10g는 통계정보가 없어도 항상 CBO 모드로 작동하므로 쿼리변환이 잘 일어나지만 optimizer_features_enable 파라미터를 8.1.7로 바꾸고 테스트 해보면 아래와 같이 불필요한 I/O를 수행한다.
SQL> alter session set optimizer_features_enable='8.1.7';
세션이 변경되었습니다.
SQL> select * from emp
2 where sal between :mn and :mx;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets --불필요한 I/O 발생
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
(3) 조건절 비교 순서
위 데이터를 아래의 SQL문으로 검색하면 B컬럼에 대한 조건식을 먼저 평가하는 것이 유리하다.
대부분의 레코드가 B=1000 조건을 만족하지 않아 A 컬럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.
SELECT * FROM T
WHERE A = 1
AND B = 1000
반대로 A = 1 조건식을 먼저 평가한다면, A컬럼에 1인 값이 많기 때문에 그만큼 수행해야 하므로 CPU 사용량이 늘어날 것이다.
조건절을 처리할 때도 부등호 조건을 먼저 평가하느냐 LIKE 조건을 먼저 평가하느냐에 따라 일량에 차이가 생긴다
SELECT /*+ FULL(도서) */ 도서번호, 도서명, 가격, 저자, 출판사, isbn
FROM 도서
WHERE 도서명 > :last_book_nm
ADN 도서명 LIKE :book_nm||'%'
옵티마이저는 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter 조건식을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.
!조건절순서.png!
SQL> set autotrace traceonly exp;
SQL> select * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 445 (10)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 445 (10)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=1000 AND "A"=1)
ordered_predicates 힌트를 사용하여 CPU Consting 모드에서의 조건절 비교순서 제어
힌트를 썼더니 예상비용이 증가
I/O 뿐만 아니라 CPU 연산 시간까지 비용 계산식에 포함하고 있음을 알수 있다
SQL> select /*+ ORDERED_PREDICATES */ * from t
2 where a = 1
3 and b = 1000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 453 (12)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 453 (12)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1000)
ordered_predicates 힌트의 또 다른 용도
1. 10g에서 OR 또는 IN-List조건에 대한 OR-Expansion이 일어날 때 실행순서를 제어할 목적으로 ordered_predicates힌트를 사용할수 있다.
2. 9i까지는 I/O비용모델, CPU 비용모델을 불문하고 IN-List를 OR-Expansion(=Concatenation) 방식으로 처리할 때 뒤쪽에 있는 값을 먼저 실행한다.
3. 10g CPU비용 모델 하에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.
4. 10g에서 같은 컬럼에 대한 OR 또는 IN-List 조건에 OR-Expansion이 작동하도록 하려면 use_concat 힌트에 아래와 같은 인자를 사용해야 한다.
select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *
from emp e
where deptno in (10, 30);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO"=30)
1. 30을 IN-List 뒤쪽에 기술했음에도, Predicate정보를 보면 통계정보 상 카디널리티가 낮은 10이 위쪽으로 올라가는 것을 볼수 있다.
2. ordered_predicates 힌트를 사용하면 9i이전 버전처럼 IN-List 뒤쪽에 있는 값을 먼저 실행한다.
select /*+ use_concat(@subq 1) qb_name(subq) index(e) ordered_predicates */ *
from emp e
where deptno in (10, 30) ;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 6 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 3 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=30)
5 - access("DEPTNO"=10)
_optimizer_cost_model 파라미터를 'IO'로 설정하거나 아래와 같이 no_cpu_costing 힌트를 사용해 IO 비용 모델로 변경해도 IN-List 뒤쪽부터 실행한다.
SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) no_cpu_consting */ *
from emp e
where deptno in (10,30);
퀴즈 풀이
1번 실행계획
Execution Plan
----------------------------------------------------------
Plan hash value: 3269917256
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
SQL> select *
2 from dept d
3 where dname = 'SALES'
4 and exists (
5 select 'x'
6 from emp
7 where deptno = d.deptno
8 and job = 'SALESMAN'
9 and sal > 1000)
10 /
no rows selected
SQL> set autotrace trace exp
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 1754319153
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 33 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="D"."DEPTNO")
2 - filter("DNAME"='SALES')
3 - filter("JOB"='SALESMAN' AND "SAL">1000)
SQL>
SQL>
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1 select *
2 from dept d
3 where dname = 'SALES'
4 and exists (
5 select /*+ no_unnest */ 'x'
6 from emp
7 where deptno = d.deptno
8 and job = 'SALESMAN'
9* and sal > 1000)
SQL>
SQL>
SQL>
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
2 - filter("DNAME"='SALES')
3 - filter("JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000)
SQL> create index dept_x01 on dept(dname,deptno)
2 /
Index created.
SQL> create index emp_x01 on emp(deptno, job)
2 /
Index created.
SQL> select *
2 from dept d
3 where dname = 'SALES'
4 and exists (
5 select /*+ no_unnest */ 'x'
6 from emp
7 where deptno = d.deptno
8 and job = 'SALESMAN'
9 and sal > 1000)
10 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3557655817
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
2 - filter("DNAME"='SALES')
3 - filter("SAL">1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
SQL>
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1 select /*+ index(d dept_x01) */ *
2 from dept d
3 where dname = 'SALES'
4 and exists (
5 select /*+ no_unnest */ 'x'
6 from emp
7 where deptno = d.deptno
8 and job = 'SALESMAN'
9* and sal > 1000)
SQL>
SQL>
SQL>
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3410011884
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)|00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)|00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
3 - filter("SAL">1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
SQL>
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1 select /*+ index(d dept_x01) */ *
2 from dept d
3 where dname = 'SALES'
4 and exists (
5 select /*+ no_unnest no_push_subq */ 'x'
6 from emp
7 where deptno = d.deptno
8 and job = 'SALESMAN'
9* and sal > 1000)
SQL>
SQL>
SQL>
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3269917256
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
3 - access("DNAME"='SALES')
4 - filter("SAL">1000)
5 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
3번 문제
Execution Plan
----------------------------------------------------------
Plan hash value: 3410011884
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)|00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)|00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
SQL> ed
Wrote file afiedt.buf
1 select /*+ index(d dept_x01) */ *
2 from dept d
3 where dname = 'SALES'
4 and exists (
5 select /*+ no_unnest */ 'x'
6 from emp
7 where deptno = d.deptno
8 and job = 'SALESMAN'
9* and sal > 1000)
SQL>
SQL>
SQL>
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3410011884
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)|00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)|00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
3 - filter("SAL">1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
7번 문제
SQL> select *
2 from dept d
3 where deptno = (
4 select deptno
5 from emp e
6 where sal >=5000
7 and job = 'PRESIDENT')
8 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1561374394
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT "DEPTNO" FROM "EMP" "E" WHERE "SAL" IS NOT NULL
AND "SAL">=5000 AND "JOB"='PRESIDENT'))
3 - filter("JOB"='PRESIDENT')
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
where 조건절에 사용된 서브쿼리
1. 메인쿼리와 상관관계가 없으면서
2. 단일 로우를 리턴
=> 서브쿼리를 Fetch가 아닌 Execute 시점에 먼저 수행해 그 결과 값을 메인 쿼리에 상수로 제공