조건절 이행 (by niceboyous) [2013.01.29]
05. 조건절 이행
- 조건절 이행은 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리 변환이다.
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 이면서 d.deptno = e.deptno 이므로 e.deptno = 10, d.deptno=10 추론 되는걸 알 수 있다.
따라서 조건절 이행을 통해 아래와 같이 형태로 변환된 것이다.
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno = 10
and e.deptno = 10;
- 위와 같이 변환한다면, emp와 dept테이블에서 각각 필터링을 적용하여 조인되는 데이터량을 줄일 수 있고
dept테이블 액세스를 위한 인덱스 사용을 추가로 고려할 수 있게 된다. - 오라클 9i에서는 중복으로 처리하였지만 10g부터는 여러 번 기술하여도 조건절 이행에 따라서 한번으로 실행하게 된다.
- 만약 조건절 이행이 작용해 조인 조건이 제거되지 않도록 하기 위해서 아래 쿼리처러 가공하는 방법도 있다.
select * from dept d, empe
where e.job='MANAGER'
and e.deptno = 10
and e.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은 인덱스 구성과 검색 범위에 따라 스켄 효율에 많은 차이가 발생되기 때문에 검색 범위를 제한해 주는 것이 도움이 된다.
- 기존 발표시 나왔지만 between을 사용하는 것 보다는 (<= , >=)을 사용하면 읽는 데이터량을 줄일 수 있다.
- 상품이력.시작일자 <= '20090131'
- 상품이력.종료일자 >= '20090101'
- 오라클 9i부터는 옵티마이저가 between을 위와 같이 처리하고 있어 개선 효과가 나타지 않는다고 한다.
- 오라클 8i버전을 사용하고 있다면 조건절을 명시적으로 추가해 주는 튜닝 기법이 여전히 유효하다.
h5.튜닝사례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
데이터 모델
아래 쿼리에서 주문상세 테이블에만 상수 조건식이 제공된 것을 볼 수 있다.
여기서, 조건절 이행을 통해 이 조건식이 주문 테이블에도 제공될 것임을 짐작할 수 있겠는가?
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');
상수 및 변수에 대한 조건절은 조인문을 타고 다른 쪽 테이블로 전이된다.
하지만 조인문 자체는 전이되지 않는데, 아래 경우가 그것을 말해준다.
고객번호에 대한 두 조인 조건식을 따져보면 고객과 주문상세가 연결, 주문과 주문상세가 연결
NL조인 순서는 ordered 힌트에 따라 고객 -> 주문 - > 주문상세 순으로 결정
고객과 주문을 먼저 조인하는 단계에서는 고객번호를 연결 조건으로 사용하지 못하는 문제가 생긴다.
아래의 정보①에서 주문상세 테이블에 조건으로부터 전이된 주문일자 조건만 있다.
고객 테이블 고객번호에 대한 조인은 주문상세_PK 인덱스를 액세스하는 단계에서 모두 이루어지고 있다.
주문 테이블과 조인하고 나서 9,000개나 더 많은 레코드가 출력
그만큼 주문상세_PK 인덱스를 여러 번 탐색하면서 10,000개 가량의 블록 I/O도 추가 발생
아래처럼 사용자가 기술한 ①,②번 조인문을 통해 내부적으로 ③번 조인문이 생성되었더라면 위와 같은 문제는
발생하지 않았을 것이다.
이처럼 조인조건은 상수와 변수 조건처럼 전이되지 않으므로 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해주는것이 매우 중요하다.