꿈꾸는 개발자, DBA 커뮤니티 구루비

07 OR-Expansion ( OR To Union All Conversion )

1) OR-Expansion 기본
1. OR, NVL, DECODE 함수를 사용한 조건절을 이용하여 Union all로 쿼리 변경
2. 분기된 쿼리에 중복 액세스되는 영역의 비중이 작을수록 효과적
3. 비용기반 쿼리 변환
or-expansion-subheap (delete addr=0x0000000015365678, in-use=14848, alloc=16408)
or-expansion is worse cost:4.000954
4. 브랜치별(분기쿼리) 실행계획 수립 가능
5. 제어 힌트
use_concat/no_expand
6. 제어 파라미터
_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 */

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 브랜치별 조인 순서 최적화

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

1. 9i
1. OR조건이나 IN-LIST도 OR-Expansion 작동이 가능
2. OR-Expansion 유도시 뒤쪽에 놓인 값이 항상 먼저 출력

2.10g
1. IN-List Iterator ( 기본방식 )
2. CPU 비용모델 : 카디널리디가 작은 값이 먼저 출력
3. ordered_predicates 힌트, IO 비용 모델로 수행시 9i 이전처럼 출력 가능

(쿼리)
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

---

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 유도 */

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
1. 조건건 조건값(:deptno)에 따라 다른 인덱스 사용
2. nvl/decode를 여러 컬럼에 대해 사용했을떄는 변별력이 가장 좋은 컬럼 기준으로 한번만 분기
옵션 조건이 복잡한 경우 수동 union all 분기가 필요
3. 제어 파라미터 : _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 = :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 공통 표현식 제거
1. 같은 조건식이 여러 곳에서 반복 사용될 경우 쿼리변환
2. 비교연산에 대한 수행이 줄고, 새로운 인덱스 액세스 조건으로 수행 가능
3. 제어 파라미터 : _eliminate_common_subexpr
4. _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> 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 조인으로 변환
1. 일부 조건절에 Outer기호(+)를 빠뜨리면 Inner 조인으로 변환
2. 쿼리 변환을 하는 의미는 조인 순서를 자유롭게 결정하기 위함

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. 실체화 뷰 쿼리로 재작성
1. 실체화 뷰는 물리적으로 실제 데이터를 갖는다
2. MV 용도

데이터 복제
Join View
Aggregate View
3. MV 특징
refresh 옵션을 이용해 집계 테이블 자동 관리
Query Rewrite 지원
4. 제어 파라미터 : query_rewrite_enabled

SQL> create table product
as
select rownum product_id, dbms_random.string('u',10) product_name
from dual connect by level <= 10;

SQL> create table customer
as
select rownum customer_id, dbms_random.string('a',10) customer_name
from dual connect by level <= 100;

SQL> create table sales
as
select product_id, customer_id, seq
,to_char(to_date('20081231', 'yyyymmdd')+product_id, 'yyyymmdd') sales_date
,round(dbms_random.value(1,100)) sales_qnt
,round(dbms_random.value(1000,100000),-2) sales_price
from product, customer, (select rownum seq from dual connect by level <= 100)

SQL> create materialized view log on sales
with sequence, rowid(product_id, customer_id, sales_date,sales_qnt,sales_price)
including new values;

SQL> create materialized view mon_cust_prod_mv
build immediate
refresh fast on commit
enable query rewrite
as
select product_id, customer_id, substr(sales_date,1,6) sales_month,
sum(sales_qnt) tot_qnt, sum(sales_price) tot_price
from sales
group by product_id, customer_id, substr(sales_date,1,6)

Page 542

11 집합 연산을 조인으로 변환
1. Intersect, Minus 같은 집합 연산을 조인 형태로 변환
2. 제어 파라미터 : _convert_set_to_join

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 조건 추가
1. 조인 컬럼 deptno가 null인 데이터는 조인 액세스가 불필요하다.
2. 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있으면 쿼리 성능 향상에 도움이 된다.

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

Call Count CPU Time Elapsed Time Disk Query Current Rows

---

--

--
Parse 1 0.016 0.012 0 72 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.078 0.079 0 70841 0 1

---

--

--
Total 4 0.094 0.092 0 70913 0 1

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

Call Count CPU Time Elapsed Time Disk Query Current Rows

---

--

--
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.016 0.006 0 841 0 1

---

--

--
Total 4 0.016 0.007 0 841 0 1

Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)

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

Call Count CPU Time Elapsed Time Disk Query Current Rows

---

--

--
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.069 27 23 0 1

---

--

--
Total 4 0.000 0.071 27 23 0 1

Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)

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) 필터 조건 추가
1. 쿼리를 수행할 때 사용자가 :mx보다 :mm변수에 더 큰 값을 입력한다면 결과는 공집합

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)
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 조건식을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.
이런한 쿼리 변환이 작동하려면 9i, 10g 를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing 모델을 활성화 해야한다

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)

1. 9i에서 시스템 통계를 지우거나 10g에서 I/O 비용 모델로 전환한 상태에서 수행하면 where 절에 기술된 순서대로 조건 비교가 일어난다.

exec dbms_stats .delete_system_stats; -- 9i 일 때
alter session set " optimizer_cost_model" = io; -- 10g일 때

2. RBO 로 바꾼 상태에서 테스트하면 where 절에 기술된 반대 순서로 조건 비교가 일어난다.

alter session set optimizer_mode = rule;

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 뒤쪽부터 실행한다.

04. 쿼리 변환 (07. OR-Expansion ~ 12. 기타 쿼리 변환) (by feelie) [2016.08.11]

07 OR-Expansion ( OR To Union All Conversion )