5. 조건절 이행

조회 수 4551 추천 수 0 2011.06.01 21:33:51
balto *.49.6.17

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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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;

 

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

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

| 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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

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

| 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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

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

| 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
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^