5. 조건절 이행

2011.05.29 19:36

balto 조회 수:5500

- 조건절이행(Transitive Predicate Generation)

A=B이고 B=C이면 A=C이다는 추론으로 조건절을 생성하는 쿼리변환.

■ 조건절이행 실험

create table dept as select * from scott.dept;

create table emp as select * from scott.emp;

alter table dept add constraint dept_pk primary key(deptno);

create index emp_idx on emp(deptno, job);

exec dbms_stats.gather_table_stats(user, 'emp');

exec dbms_stats.gather_table_stats(user, 'dept');

(실험 1) - 조건절이 이행되는 예

select * from dept d, emp e

where e.job = 'MANAGER' and e.deptno = 10 and d.deptno = e.deptno;

Execution Plan

----------------------------------------------------------------------

----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | EMP_IDX | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("D"."DEPTNO"=10)

5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')

(실험 2) - 조건절이 이행되는 조건들을 질의문에 직접주었을 때

select * from dept d, emp e

where e.job = 'MANAGER' and e.deptno = 10 and d.deptno = 10;

Execution Plan

----------------------------------------------------------------------

----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | EMP_IDX | 1 | | 0 (0)| 00:00:01 |

-----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("D"."DEPTNO"=10)

5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')

(실험 3) - 조건절이행이 안되도록 질의문을 가공하는 경우

select * from dept d, emp e

where e.job = 'MANAGER' and e.deptno = 10 and d.deptno = e.deptno + 0;

Execution Plan

----------------------------------------------------------------------

----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 57 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_IDX | 1 | | 1 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')

5 - access("D"."DEPTNO"="E"."DEPTNO"+0)

■ 조건절이행이 효과를 발휘하는 사례

create table 상품이력 (상품번호 number, 시작일자 varchar2(8), 종료일자 varchar2(8));

create table 주문 (거래일자 varchar2(8), 상품번호 number);

(실험 1) - 조건절이 이행되는 예

select *

from 상품이력 a, 주문 b

where b.거래일자 between '20090101' and '20090131'

and a.상품번호 = b.상품번호

and b.거래일자 between a.시작일자 and a.종료일자;

Execution Plan

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 44 | 5 (20)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 44 | 5 (20)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| 상품 | 1 | 25 | 2 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| 주문 | 1 | 19 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."상품번호"="B"."상품번호")

filter("B"."거래일자">="A"."시작일자" AND "B"."거래일자"<="A"."종료일자")

2 - filter("A"."종료일자">='20090101' AND "A"."시작일자"<='20090131')

3 - filter("B"."거래일자">='20090101' AND "B"."거래일자"<='20090131')

■ 튜닝사례 1

create table IP주소목록(IP주소 varchar2(15), IP연결일자 varchar2(8), 시작IP주소 varchar2(15), 종료IP주소 varchar2(15), ISP명 varchar2(8), IP등록일자 varchar2(8), IP사용기관ID number, IP사용기관명 varchar2(8), IP사용시도명 varchar2(8), 사용기관주소 varchar2(8), 사용기관우편번호 varchar2(8), IP책임자명 varchar2(8), IP책임자전화번호 varchar2(8));

CREATE INDEX IP주소목록_PK ON IP주소목록(IP주소);

CREATE INDEX IP주소목록_X01 ON IP주소목록(시작IP주소);

-- 192.168.000 서브넷에 속한 IP주소 목록을 가져오기

var strtIpAddr varchar2(15);

exec :strtIpAddr := '192.168.000.001';

var endIpAddr varchar2(15);

exec :endIpAddr := '192.168.000.255';

SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소

, ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명

, 사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호

FROM IP주소목록

WHERE 시작IP주소 >= :strtIpAddr AND 종료IP주소 <= :endIpAddr;

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 94 | 1 (0)|

|* 1 | TABLE ACCESS BY INDEX ROWID| IP주소목록 | 1 | 94 | 1 (0)|

|* 2 | INDEX RANGE SCAN | IP주소목록_| 1 | | 2 (0)|

--------------------------------------------------------------------------

-- IP주소목록_X01 인덱스에 종료IP주소 컬럼 추가하면 조금 개선됨

-- 우리만 아는 정보 적용

SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소

, ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명

, 사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호

FROM IP주소목록

WHERE 시작IP주소 >= :strtIpAddr -- ①

AND 종료IP주소 <= :endIpAddr -- ②

AND 시작IP주소 <= 종료IP주소 -- ③

-- ①, ②, ③ 합성

:strtIpAddr <= 시작IP주소 <= 종료IP주소 <= :endIpAddr

-- 시작IP주소, 종료IP주소 컬럼 기준으로 다시 분해

WHERE 시작IP주소 BETWEEN :strtIpAddr AND :endIpAddr

AND 종료IP주소 BETWEEN :strtIpAddr AND :endIpAddr

-- 변수 바인딩

WHERE 시작IP주소 BETWEEN '192.168.000.001' AND '192.168.000.255'

AND 종료IP주소 BETWEEN '192.168.000.001' AND '192.168.000.255'

SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소

, ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명

, 사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호

FROM IP주소목록

WHERE 시작IP주소 BETWEEN :strtIpAddr AND :endIpAddr

AND 종료IP주소 BETWEEN :strtIpAddr AND :endIpAddr;

■ 튜닝사례 2

select /*+ gather_plan_statistics ordered use_nl(o) use_nl(d) index(o) index(d) */

c.고객명, o.주문일자, o.주문번호, o.배송지, d.상품번호, d.상품가격, d.주문수량

from 고객 c, 주문 o, 주문상세 d

where o.고객번호 = c.고객번호

and d.고객번호 = o.고객번호

and d.주문일자 = o.주문일자

and d.주문번호 = o.주문번호

and d.주문일자 = to_char(sysdate + 1, 'YYYYMMDD');

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

Execution Plan

----------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 158 | 37 (0)|

|* 1 | TABLE ACCESS BY INDEX ROWID | 주문상세| 1 | 28 | 4 (0)|

| 2 | NESTED LOOPS | | 1 | 158 | 37 (0)|

| 3 | NESTED LOOPS | | 5 | 650 | 17 (0)|

| 4 | TABLE ACCESS FULL | 고객 | 10 | 140 | 3 (0)|

|* 5 | TABLE ACCESS BY INDEX ROWID| 주문 | 1 | 116 | 2 (0)|

|* 6 | INDEX RANGE SCAN | 주문_PK | 5 | | 1 (0)|

|* 7 | INDEX RANGE SCAN | 주문상세| 2 | | 1 (0)|

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("D"."고객번호"="O"."고객번호")

5 - filter("O"."고객번호"="C"."고객번호")

6 - access("O"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD'))

7 - access("D"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD') AND

"D"."주문번호"="O"."주문번호")

- 상수및 변수에대한 조건문은 조인문을 타고 다른쪽 테이블로 전이되나 조인문 자체는 전이되지 않는 사례

select /*+ gather_plan_statistics ordered use_nl(o) use_nl(d) index(o) index(d) */

c.고객명, o.주문일자, o.주문번호, o.배송지, d.상품번호, d.상품가격, d.주문수량

from 고객 c, 주문 o, 주문상세 d

where d.고객번호 = c.고객번호

and d.고객번호 = o.고객번호

and d.주문일자 = o.주문일자

and d.주문번호 = o.주문번호

and d.주문일자 = to_char(sysdate + 1, 'YYYYMMDD');

Execution Plan

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 158 | 217 (0)|

|* 1 | TABLE ACCESS BY INDEX ROWID | 주문상세| 1 | 28 | 4 (0)|

| 2 | NESTED LOOPS | | 1 | 158 | 217 (0)|

| 3 | NESTED LOOPS | | 50 | 6500 | 17 (0)|

| 4 | TABLE ACCESS FULL | 고객 | 10 | 140 | 3 (0)|

| 5 | TABLE ACCESS BY INDEX ROWID| 주문 | 5 | 580 | 2 (0)|

|* 6 | INDEX RANGE SCAN | 주문_PK | 5 | | 1 (0)|

|* 7 | INDEX RANGE SCAN | 주문상세| 2 | | 1 (0)|

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("D"."고객번호"="C"."고객번호" AND "D"."고객번호"="O"."고객번호")

6 - access("O"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD'))

7 - access("D"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD') AND

"D"."주문번호"="O"."주문번호")

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)

작성자: 박우창 (balto)

최초작성일: 2011년 05월 29일

본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net

문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^

4장5절-조건절이행-SQL실험.hwp [File Size:38.0KB/Download:262]

이 게시물을

번호	제목	글쓴이	날짜	조회 수
35	1. 옵티마이저	휘휘	2011.04.17	6189
34	3. 옵티마이저의 한계 - P	휘휘	2011.04.17	3735
33	2. 옵티마이저 행동에 영향을 미치는 요소	balto	2011.04.17	6197
32	3. 옵티마이저의 한계	휘휘	2011.04.19	6735
31	4. 통계정보 Ⅰ	토시리	2011.04.24	16114
30	6. 히스토그램	오예스	2011.04.25	17672
29	5. 카디널리티	오라클잭	2011.04.26	13001
28	7. 비용	balto	2011.05.01	5120
27	8. 통계정보 Ⅱ	AskZZang	2011.05.04	5946
26	1. 쿼리 변환이란?	운영자	2011.05.16	6296
25	3. 뷰 Merging	오라클잭	2011.05.17	6114
24	2. 서브쿼리 Unnesting	토시리	2011.05.17	2116
»	5. 조건절 이행	balto	2011.05.29	5500
22	4. 조건절 Pushing	오예스	2011.05.31	17747
21	6. 조인 제거	AskZZang	2011.06.01	5484
20	7. OR-Expansion	AskZZang	2011.06.01	8468
19	12. 기타 쿼리 변환	휘휘	2011.06.05	3185
18	10. 실체화 뷰 쿼리로 재작성	오라클잭	2011.06.07	9996
17	11. 집합 연산을 조인으로 변환	오라클잭	2011.06.07	4993
16	2. 소트를 발생시키는 오퍼레이션	balto	2011.06.11	4890

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

5. 조건절 이행

댓글 0