h2.조건절 이행이란?
(/) 연역법 추리(A=B고 B=C이면 A=C이다 )를 통해 새로운 조건절을 내부적으로 생성해주는 쿼리임
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno=10
and d.deptno=e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 35793385
----------------------------------------------------------------------------------------
| 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 | PK_DEPT | 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 | 2 | | 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')
-조건에 없던 d.deptno 값이 추가된것을 확인할 수 있다
-(e.deptno = 10) 이고 (d.deptno = e.deptno) 이면 (d.deptno = 10) 라는 결론
- 내부적으로 변환된 쿼리는 조인절이 제거되어 아래와 같다
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno=10
and d.deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 35793385
----------------------------------------------------------------------------------------
| 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 | PK_DEPT | 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 | 2 | | 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')
--만약 조건절 이행이 작용하여 조인조건이 사라지고 이로인한 비용 계산이 잘못되어 문제가 생긴다면,
사용자가 명시적으로 d.deptno=10을 추가하거나 아래와 같이 조인값을 가공하여 사용할 수 있다
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno=10
and d.deptno=e.deptno+0;
Execution Plan
----------------------------------------------------------
Plan hash value: 31914708
----------------------------------------------------------------------------------------
| 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 | 2 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 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)
h3.조건절이행의 좋은사례
SQL> create table 상품이력 (상품번호 number, 시작일자 varchar2(8), 종료일자 varchar2(8));
테이블이 생성되었습니다.
SQL> create table 주문 (거래일자 varchar2(8), 상품번호 number);
테이블이 생성되었습니다.
SQL> select *
2 from 상품이력 a, 주문 b
3 where b.거래일자 between '20090101' and '20090131'
4 and a.상품번호 = b.상품번호
5 and b.거래일자 between a.시작일자 and a.종료일자;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3208568965
---------------------------------------------------------------------------
| 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')
SQL> select *
2 from 상품이력 a, 주문 b
3 where b.거래일자 >= '20090101'
4 and b.거래일자 <= '20090131'
5 and a.상품번호 = b.상품번호
6 and b.거래일자 >= a.시작일자
7 and b.거래일자 <= a.종료일자;
선택된 레코드가 없습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3208568965
---------------------------------------------------------------------------
| 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')
(b.거래일자 >= '20090101') 이고 (b.거래일자 <= a.종료일자) 이면 ('20090101' <= a.종료일자) 이다
(b.거래일자 <= '20090131') 이고 (b.거래일자 >= a.시작일자) 이면 ('20090131' >= a.시작일자) 이다
h3.튜닝사례1
-인덱스 상황
CREATE IP주소목록_PK ON IP주소목록(IP주소)
CREATE IP주소목록_X01 ON IP주소목록(시작IP주소)
-특정 서브넷에 속한 IP목록을 조회하기위한 쿼리
SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
, ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
, 사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
FROM IP주소목록
WHERE 시작IP주소 >= :strtIpAddr
AND 종료IP주소 <= :endIpAddr
-바인드변수
192.168.000 서브넷에 속한 IP주소 목록을 가져오기
:strtIpAddr := '192.168.000.001'
:endIpAddr := '192.168.000.255'
-실행계획
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.00 0 0 0 0
Execute 1 0.000 0.00 0 0 0 0
Fetch 9 32.820 1922.797 341291 6940276 0 106
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 11 32.820 1922.797 341291 6940276 0 106
Rows Row Source Operation
------- ----------------------------------------------------
106 TABLE ACCESS BY INDEX ROWID IP주소목록 (cr=6940276 pr=341291 pw=0 ... )
8362619 INDEX RANGE SCAN IP주소목록_X01 (cr=27980 pr=27968 pw=0 time=33450495 us)
-인덱스를 읽고 테이블 엑세스 8,362,619회 발생
-테이블 랜덤 엑세스 6,912,296 (= 6,940,276 - 27,980) 회 발생
튜닝방법
1. 인덱스 수정
과도한 랜덤 엑세스를 줄이기 위해 IP주소목록_IX01에 종료 IP컬럼 추가
문제점 : 인덱스 스켄과정에서 바인드 변수에 따라 과도한 인덱스 스켄이 발생할 수 있음
2. IP주소목록 테이블에 종료IP주소는 시작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
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 9 0.000 0.001 0 55 0 106
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 11 0.000 0.001 0 55 0 106
Rows Row Source Operation
------- ----------------------------------------------------
0 STATEMENT
106 FILTER (cr=55 pr=0 pw=0 time=37 us)
106 TABLE ACCESS BY INDEX ROWID IP주소목록 (cr=55 pr=0 pw=0 time=34 us)
106 INDEX RANGE SCAN IP주소목록_X01 (cr=12 pr=0 pw=0 time=654 us)
-조인조건은 아니지만 컬럼간의 관계 정보를 통해 주가적으로 조건절을 생성하여 옵티마이저에게 정보를 제공해줌으로써
SQL 성능이 향상된 사례이다.
h3.튜닝사례 2
SQL> create table 고객 (고객번호 number, 고객명 varchar2(10));
테이블이 생성되었습니다.
SQL> create table 주문 (주문일자 varchar2(8), 주문번호 number, 고객번호 number, 배송지 varchar2(100));
테이블이 생성되었습니다.
SQL> create table 주문상세 (주문일자 varchar2(8), 주문번호 number, 고객번호 number, 상품번호 number, 상품가격 number, 주문수량 number);
테이블이 생성되었습니다.
SQL> alter table 고객 add constraint 고객_PK primary key (고객번호);
테이블이 변경되었습니다.
SQL> alter table 주문 add constraint 주문_PK primary key (주문일자, 주문번호);
테이블이 변경되었습니다.
SQL> alter table 주문상세 add constraint 주문상세_PK primary key (주문일자, 주문번호, 상품번호);
테이블이 변경되었습니다.
SQL> alter table 주문 add constraint 주문_FK_고객 foreign key (고객번호) references 고객 (고객번호);
테이블이 변경되었습니다.
SQL> alter table 주문상세 add constraint 주문상세_FK_주문 foreign key (주문일자, 주문번호) references 주문 (주문일자, 주문번호);
테이블이 변경되었습니다.
SQL> insert into 고객
2 select rownum as 고객번호, dbms_random.string('X', 10) as 고객명 from dual connect by level <= 10;
SQL> insert into 주문
2 select x.주문일자,
3 rank() over (partition by x.주문일자 order by rownum) as 주문번호,
4 x.고객번호,
5 x.배송지
6 from (
7 select to_char(sysdate + round(dbms_random.value(1, 100)), 'YYYYMMDD') as 주문일자,
8 a.고객번호,
9 dbms_random.string('X', 100) as 배송지
10 from 고객 a, (select level from dual connect by level <= 100)
11 ) x;
SQL> insert into 주문상세
2 select a.주문일자, a.주문번호, a.고객번호,
3 round(dbms_random.value(1111111,9999999)) as 상품번호,
4 round(dbms_random.value(10000,99999)) as 상품가격,
5 round(dbms_random.value(1,99)) as 주문수량
6 from 주문 a, (select level from dual connect by level <= 10);
SQL> commit;
커밋이 완료되었습니다.
exec dbms_stats.gather_table_stats (ownname => 'scott', tabname => '고객' , degree => 1, cascade => TRUE);
PL/SQL 처리가 정상적으로 완료되었습니다.
exec dbms_stats.gather_table_stats (ownname => 'scott', tabname => '주문' , degree => 1, cascade => TRUE);
PL/SQL 처리가 정상적으로 완료되었습니다.
exec dbms_stats.gather_table_stats (ownname => 'scott', tabname => '주문상세' , degree => 1, cascade => TRUE);
PL/SQL 처리가 정상적으로 완료되었습니다.
- 힌트에 의해 고객 -> 주문 -> 주문상세 순으로 NL조인 되고 있다
********************************************************************************
select /*+ 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');
Execution Plan
----------------------------------------------------------
Plan hash value: 1214512319
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 25 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 159 | 25 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 130 | 23 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | 고객 | 10 | 140 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| 주문 | 1 | 116 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | 주문_PK | 1 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | 주문상세| 1 | | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | 주문상세| 1 | 29 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("O"."고객번호"="C"."고객번호")
6 - access("O"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD'))
7 - access("D"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD') AND
"D"."주문번호"="O"."주문번호")
8 - filter("D"."고객번호"="O"."고객번호")
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.194 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 33 0 10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.195 0 33 0 10
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
10 NESTED LOOPS (cr=33 pr=0 pw=0 time=0 us)
10 NESTED LOOPS (cr=23 pr=0 pw=0 time=63 us cost=25 size=159 card=1)
1 NESTED LOOPS (cr=20 pr=0 pw=0 time=0 us cost=23 size=130 card=1)
10 TABLE ACCESS FULL 고객 (cr=8 pr=0 pw=0 time=9 us cost=3 size=140 card=10)
1 TABLE ACCESS BY INDEX ROWID 주문 (cr=12 pr=0 pw=0 time=0 us cost=2 size=116 card=1)
10 INDEX RANGE SCAN 주문_PK (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
10 INDEX RANGE SCAN 주문상세_PK (cr=3 pr=0 pw=0 time=54 us cost=1 size=0 card=1)
10 TABLE ACCESS BY INDEX ROWID 주문상세 (cr=10 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
********************************************************************************
-위와 같이 주문테이블의 주문일자가 추가된것을 볼수 있다.
-상수 및 변수에 대한 조건절이 조인문을 타고 다른쪽 테이블로 이관됨을 볼수 있다
-하지만 조인문 자체는 이관되지 않음을 아래를 통해 알수 있다
-조인조건을 바꾸어 테스트해보자
o.고객번호 = c.고객번호->d.고객번호 = c.고객번호
********************************************************************************
select /*+ 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
----------------------------------------------------------
Plan hash value: 1214512319
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 43 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 159 | 43 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 1300 | 23 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | 고객 | 10 | 140 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| 주문 | 1 | 116 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | 주문_PK | 1 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | 주문상세| 1 | | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | 주문상세| 1 | 29 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("O"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD'))
7 - access("D"."주문일자"=TO_CHAR(SYSDATE@!+1,'YYYYMMDD') AND
"D"."주문번호"="O"."주문번호")
8 - filter("D"."고객번호"="C"."고객번호" AND "D"."고객번호"="O"."고객번호")
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.016 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.001 0 130 0 10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.003 0 130 0 10
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
10 NESTED LOOPS (cr=130 pr=0 pw=0 time=0 us)
100 NESTED LOOPS (cr=30 pr=0 pw=0 time=198 us cost=43 size=159 card=1)
10 NESTED LOOPS (cr=20 pr=0 pw=0 time=99 us cost=23 size=1300 card=10)
10 TABLE ACCESS FULL 고객 (cr=8 pr=0 pw=0 time=27 us cost=3 size=140 card=10)
10 TABLE ACCESS BY INDEX ROWID 주문 (cr=12 pr=0 pw=0 time=0 us cost=2 size=116 card=1)
10 INDEX RANGE SCAN 주문_PK (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
100 INDEX RANGE SCAN 주문상세_PK (cr=10 pr=0 pw=0 time=40 us cost=1 size=0 card=1)
10 TABLE ACCESS BY INDEX ROWID 주문상세 (cr=100 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
********************************************************************************
-고객의 고객번호는 주문상세의 고객번호와 연결이 되어있고 , 주문의 고객번호는 주문상세의 고객번호와 연결되어있으나
ordered 힌트에 따라 고객->주문->주문상세 순으로 NL조인이 이루어지고 있고 고객과 주문을 먼저 조인하는 단계에서 고객번호를 연결조건으로 사용하지 못하는 문제가 발생한다.
-주문테이블과 조인하고 나서 97개 가량의 블록 I/O도 추가로 발생하여 성능 저하의 문제가 생긴다.
이처럼 조인조건은 상수와 변수 조건처럼 전이되지 않으므로 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해주는것이 매우 중요하다.