■  조건절이행 실험

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)

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

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

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

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;

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

:strtIpAddr := '192.168.000.001'

:endIpAddr  := '192.168.000.255'

■ 튜닝사례 2 - Script

drop table 고객;

drop table 주문;

drop table 주문상세;

create table 고객 (고객번호 number, 고객명 varchar2(10));

create table 주문 (주문일자 varchar2(8), 주문번호 number, 고객번호 number, 배송지 varchar2(100));

create table 주문상세 (주문일자 varchar2(8), 주문번호 number, 고객번호 number, 상품번호 number, 상품가격 number, 주문수량 number);

alter table 고객 add constraint 고객_PK primary key (고객번호);

alter table 주문 add constraint 주문_PK primary key (주문일자, 주문번호);

alter table 주문상세 add constraint 주문상세_PK primary key (주문일자, 주문번호, 상품번호);

alter table 주문 add constraint 주문_FK_고객 foreign key (고객번호) references 고객 (고객번호);

alter table 주문상세 add constraint 주문상세_FK_주문 foreign key (주문일자, 주문번호) references 주문 (주문일자, 주문번호);

insert into 고객

select rownum as 고객번호, dbms_random.string('X', 10) as 고객명 from dual connect by level <= 10;

insert into 주문

select x.주문일자,

rank() over (partition by x.주문일자 order by rownum) as 주문번호,

x.고객번호,

x.배송지

from (

select to_char(sysdate + round(dbms_random.value(1, 100)), 'YYYYMMDD') as 주문일자,

a.고객번호,

dbms_random.string('X', 100) as 배송지

from 고객 a, (select level from dual connect by level <= 100)

) x;

insert into 주문상세

select a.주문일자, a.주문번호, a.고객번호,

round(dbms_random.value(1111111,9999999)) as 상품번호,

round(dbms_random.value(10000,99999)) as 상품가격,

round(dbms_random.value(1,99)) as 주문수량

from 주문 a, (select level from dual connect by level <= 10);

commit;

exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => '고객' , degree => 1, cascade => TRUE);

exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => '주문' , degree => 1, cascade => TRUE);

exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => '주문상세' , degree => 1, cascade => TRUE);

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

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