조건절 이행,. (by aini79) [2014.05.31]
05 조건절 이행
- "(A = B) 이고 (B = C) 이면 (A = C) 이다" 라는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리변환
- A 테이블에 사용된 필터 조건이 조인 조건절을 타고 반대편 B 테이블에 대한 필터 조건으로 이행
- 한 테이블 내에서도 두 컬럼간 관계정보를 이용해 조건절 이행
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno = 10
and d.deptno = e.deptno;
----------------------------------------------------------------------------------------
| 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')
- e.deptno = 10 이면서 e.deptno = d.deptno 이므로 d.deptno = 10 의 추론이 가능하다.
- 이와 같은 조건절 이행을 통해 쿼리가 아래와 같이 형태로 변환된 것이다.
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno = 10
and d.deptno = 10
- 장점
\- 해시 조인 또는 소트 머지 조인을 수행하기 전에 emp와 dept 테이블에 각각 필터링을 적용함으로써 조인되는 데이터량을 줄일 수 있다.
\- dept 테이블 액세스를 위한 인덱스를 추가로 고려할 수 있게 돼 실행계획 개선 가능성 - 유의점
\- 새로운 필터조건이 추가되면서 e.deptno = d.deptno 조인조건이 제거됨 -> 비용 계산식 중복 산정 방지를 위해 옵티마이저가 제거
(9i 에서는 사용자가 의도적으로 e.deptno = d.deptno 를 한번 더 기술하면 조인문이 다시 나타났는데, 10g부터는 여러번 기술해도 중복되지 않음)
- 조건절 이행으로 조인조건이 사라지는 것을 방지하기 위해 조인문을 아래와 같이 가공할 수 있다.
select * from dept d, empe
where e.job='MANAGER'
and e.deptno = 10
and d.deptno = e.deptno + 0
조건절 이행이 효과를 발휘하는 사례
select *
from 상품이력 a, 주문 b
where b.거래일자 between '20090101' and '20090131'
and a.상품번호 = b.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
---------------------------------------------------------------------------
| 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')
- 위의 쿼리는 주문 데이터를 조회시 선분이력으로 설계된 상품이력 테이블을 between 조건으로 조인하는 쿼리이다.
- between은 인덱스 구성과 검색 범위에 따라 스캔 효율에 많은 차이가 생기기 때문에 검색 범위를 제한해 주는 것이 도움이 된다.
- 아래 조건절을 명시적으로 넣어주면 상품이이력에서 읽는 데이터량을 줄일 수 있다.
- 상품이력.시작일자 <= '20090131'
- 상품이력.종료일자 >= '20090101'
- 오라클 9i부터는 옵티마이저가 이런 조건을 묵시적으로 추가하고 최적화를 수행한다.(튜닝 효과 없음)
- 오라클 8i버전을 사용하고 있다면 조건절을 명시적으로 추가해 주는 튜닝 기법이 여전히 유효하다.
h4.튜닝사례1
- 특정 서브넷에 속한 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'
- 인덱스 상황
CREATE IP주소목록_PK ON IP주소목록(IP주소)
CREATE IP주소목록_X01 ON IP주소목록(시작IP주소)
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)
- between으로 풀어서 기술하지 않고 '종료IP주소는 시작IP주소보다 크다'는 사실을 옵티마이저에게 알려만 줘도 옵티마이저가 조건절을 내부적으로 생성해 낸다.
-> 어떻게?
h4.튜닝사례2
- 데이터 모델
아래 쿼리에서 주문상세 테이블에만 상수 조건식이 제공된 것을 볼 수 있다.
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');
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 1001 0.125 0.084 0 15098 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 0.125 0.084 0 15098 0 10000
Rows Row Source Operation
------ -----------------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID 주문상세 (cr=15Q98 pr=0 pw=0 time=280245 us)
11001 NESTED LOOPS (cr=5098 pr=0 pw=0 time=99435 us)
1000 NESTED LOOPS (cr=2035 pr=0 pw=0 time=20134 us)
10 TABLE ACCESS FULL 고객 (cr=16 pr=0 pw=0 time=156 us)
1000 TABLE ACCESS BY INDEX ROWID 주문 (cr=2019 pr=0 pw=0 time=14212 us)
1000 INDEX RANGE SCAN 주문_PK (cr=1019 pr=0 pw=0 time=6140 us) ......... 1
10000 INDEX RANGE SCAN 주문상세_PK (cr=3063 pr=0 pw=0 time=66267 us) ...... 2
(i) 상수 및 변수에 대한 조건절은 조인문을 타고 다른 쪽 테이블로 전이된다.
하지만 조인문 자체는 전이되지 않는데, 아래 경우가 그것을 말해준다.
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');
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 1001 0.188 0.220 0 25052 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 0.188 0.220 0 25052 0 10000
Rows Row Source Operation
------ -----------------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID 주문상세 (cr=25052 pr=0 pw=0 time=210132 us)
20001 NESTED LOOPS (cr=15052 pr=0 pw=0 time=260362 us)
10000 NESTED LOOPS (cr=2422 pr=0 pw=0 time=150064 us)
10 TABLE ACCESS FULL 고객 (cr=16 pr=0 pw=0 time=74 us)
10000 TABLE ACCESS BY INDEX ROWID 주문 (cr=2406 pr=0 pw=0 time=90156 us)
10000 INDEX RANGE SCAN 주문_PK (cr=1046 pr=0 pw=0 time=30093 us) ......... 1
10000 INDEX RANGE SCAN 주문상세_PK (cr=12630 pr=0 pw=0 time=167571 us) ...... 2
- 조건식은 고객과 주문상세가 연결, 주문과 주문상세가 연결
- 그런데 NL조인 순서는 ordered 힌트에 따라 고객 -> 주문 - > 주문상세 순으로 결정
- 고객과 주문을 먼저 조인하는 단계에서는 고객번호를 연결 조건으로 사용하지 못하는 문제가 생긴다.
(i) 조인조건은 상수와 변수 조건처럼 전이되지 않으므로 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해주는 것이 매우 중요