5. 조건절 이행

2011.05.30 04:36

balto 조회 수:5465

- 조건절이행(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:217]

이 게시물을

번호	제목	글쓴이	날짜	조회 수
35	7. 인덱스 스캔 효율	휘휘	2011.03.06	8449
34	7. OR-Expansion	AskZZang	2011.06.01	8327
33	1. 소트 수행 원리	balto	2011.06.12	8011
32	2. 소트 머지 조인	오예스	2011.03.21	7912
31	4. 테이블 Random 액세스 부하	휘휘	2011.02.26	6942
30	1장. 인덱스 원리와 활용	휘휘	2011.02.21	6796
29	3. 옵티마이저의 한계	휘휘	2011.04.19	6698
28	3. 해시 조인	휘휘	2011.03.21	6486
27	8. 고급 조인 테크닉-1	휘휘	2011.04.05	6460
26	1. 쿼리 변환이란?	운영자	2011.05.16	6258
25	2. 옵티마이저 행동에 영향을 미치는 요소	balto	2011.04.18	6160
24	3. 뷰 Merging	오라클잭	2011.05.17	6082
23	1. 옵티마이저	휘휘	2011.04.18	6064
22	7. Sort Area 크기 조정	오예스	2011.06.16	5973
21	8. 통계정보 Ⅱ	AskZZang	2011.05.04	5913
20	8. 인덱스 설계	AskZZang	2011.03.09	5726
»	5. 조건절 이행	balto	2011.05.30	5465
18	6. 조인 제거	AskZZang	2011.06.01	5440
17	4. 조인 순서의 중요성	AskZZang	2011.03.30	5328
16	9. 비트맵 인덱스	휘휘	2011.03.07	5014

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

5. 조건절 이행

댓글 0