| 조인유형 |
데이터 상황 |
튜닝 방안 |
| 정해진 시점으로 |
조회대상이 많지 않을 때 |
인덱스 순서 조정 |
| 선분이력과 단순조인 |
조회대상이 많을 때 |
해시조인 |
| Between 조인 |
조회대상이 많지 않을 때 |
스칼라서브쿼리에 stopkey조건사용 |
| 조회대상이 많지만 대상별 |
해시조인 |
| 이력레코드가 많지 않을때 |
? |
----------------------------------------------------------------------------------------------------------------------------------------
select /*+ ordered use_nl(b) */ a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C70'
and b.고객번호 = a.고객번호
and '20050131' between b.시작일 and b.종료일;'
------------------------------------------------------------------------------------------------------------------------------------------
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=4636 pr=4329 pw=0 time=5424 us)
21 NESTED LOOPS (cr=4634 pr=4329 pw=0 time=106042 us)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=958 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=449 us)(Object ID 11731)
10 INDEX RANGE SCAN 고객별연체이력_IDX01 (cr=4630 pr=4329 pw=0 time=7018 us)(Object ID 11735)
------------------------------------------------------------------------------------------------------------------------------------------------
*고객 테이블에 입력된 10명의 연체이력 조회하는데 고객별연체이력_IDX01에서 블록 I/O 4630 발생.
-고객마다 종료일이 2005년 1월 31일 보다 크거나 같은 이력을 모두 스캔
-고격별 연체이력 index를 시작일 종료일로 변경-
create index 고객별연체이력_idx01 on 고객별연체이력(고객번호, 시작일, 종료일)
-----------------------------------------------------------------------------------------------------------------------------------------------
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=30 pr=156 pw=0 time=24949 us)
21 NESTED LOOPS (cr=28 pr=156 pw=0 time=496840 us)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=342 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=203 us)(Object ID 11731)
10 INDEX RANGE SCAN 고객별연체이력_IDX01 (cr=24 pr=156 pw=0 time=126239 us)(Object ID 11820)
--------------------------------------------------------------------------------------------------------------------------------------------------
*인덱스 순서를 바꿔줌으로써 시작일이 2005년 1월 31일보다 작거나 같은 이력을 찾게 되고 여기에 해당한는
데이터가 매우 소량이므로 스캔량이 줌
<between 조인 튜닝-조회 대상이 많지 않을 때>
-원본-
select /*+ ordered use_nl(b) */ a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C70'
and b.고객번호 = a.고객번호
and a.서비스만료일 between b.시작일 and b.종료일;
---------------------------------------------------------------------------------------------------------------------------------------------------
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=2571 pr=2497 pw=0 time=95713 us)
21 NESTED LOOPS (cr=2561 pr=2497 pw=0 time=1912141 us)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=372 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=223 us)(Object ID 11731)
10 INDEX RANGE SCAN 고객별연체이력_IDX01 (cr=2557 pr=2497 pw=0 time=1105543 us)(Object ID 11820)
-----------------------------------------------------------------------------------------------------------------------------------------------------
*조인문을 스칼라 서브쿼리나 중첩된 서브쿼리 형태로 바꿔 고객별로 단 하나의 이력만 읽도록 rownum <=1 추가
-튜닝-
select a.고객명, a.거주지역, a.주소, a.연락처
,(select /*+ index_desc(b 고객별연체이력_idx01) */ 연체금액
from 고객별연체이력 b
where b.고객번호 = a.고객번호
and a.서비스만료일 between 시작일 and 종료일
and rownum <= 1) 연체금액
from 고객 a
where 가입회사 = 'C70';
-------------------------------------------------------------------------------------------------------------------------------------------------------
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 COUNT STOPKEY (cr=40 pr=0 pw=0 time=669 us)
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=40 pr=0 pw=0 time=532 us)
10 INDEX RANGE SCAN DESCENDING 고객별연체이력_IDX01 (cr=30 pr=0 pw=0 time=382 us)(Object ID 11820)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=183 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=323 us)(Object ID 11731)
----------------------------------------------------------------------------------------------------------------------------------------------------
<Between 조인 튜닝-대상별 이력 레코드가 많을 때>
--NL 조인 방식으로 between 조인 수행 --
select /*+ leading(b) use_nl(a) index(a 상품이력_idx)*/
sum(b.거래수량) 총거래수량
, sum(b.거래수량 * a.판매가) 총판매금액
, round(avg(b.거래수량 * a.판매가)) 평균판매금액
from 상품이력 a, 일별상품거래 b
where b.상품번호 = a.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
-------------------------------------------------------------------------------------------------------------------------------------------
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.006 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 186.560 192.684 0 1900386 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 186.560 192.690 0 1900386 0 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1900386 pr=0 pw=0 time=192683588 us)
365300 TABLE ACCESS BY INDEX ROWID 상품이력 (cr=1900386 pr=0 pw=0 time=194340387 us)
730601 NESTED LOOPS (cr=1535086 pr=0 pw=0 time=3667956 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1096094 us)
365300 INDEX RANGE SCAN 상품이력_IDX (cr=1533917 pr=0 pw=0 time=183433317 us)(Object ID 11907)
-----------------------------------------------------------------------------------------------------------------------------------------------------
*190만개의 블록을 읽으면서 192초가 소요
--stopkey 조건을 적용한 서브쿼리로 rowid를 읽어 직접 이력 테이블 액세스--
select /*+ ordered use_nl(b) rowid(b) */
sum(a.거래수량) 총거래수량
, sum(a.거래수량 * b.판매가) 총판매금액
, round(avg(a.거래수량 * b.판매가)) 평균판매금액
from 일별상품거래 a, 상품이력 b
where b.rowid = (select /*+ index_desc(c 상품이력_idx)*/ rowid
from 상품이력 c
where 상품번호 = a.상품번호
and a.거래일자 between c.시작일자 and c.종료일자
and rownum <= 1)
----------------------------------------------------------------------------------------------------------------------------------------------
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.011 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 21.130 21.316 0 1462650 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 21.130 21.327 0 1462650 0 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1462650 pr=0 pw=0 time=21315680 us)
365300 NESTED LOOPS (cr=1462650 pr=0 pw=0 time=20457199 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=730819 us)
365300 TABLE ACCESS BY USER ROWID 상품이력 (cr=1461481 pr=0 pw=0 time=18623300 us)
365300 COUNT STOPKEY (cr=1096181 pr=0 pw=0 time=12861999 us)
365300 INDEX RANGE SCAN DESCENDING 상품이력_IDX (cr=1096181 pr=0 pw=0 time=9616552 us)(Object
------------------------------------------------------------------------------------------------------------------------------------------------------------
*146만개 블록을 읽으면서 21초 소요
-- HASH 조인으로 수행 --
select /*+ leading(a) use_hash(b) */
sum(b.거래수량) 총거래수량
, sum(b.거래수량 * a.판매가) 총판매금액
, round(avg(b.거래수량 * a.판매가)) 평균판매금액
from 상품이력 a, 일별상품거래 b
where b.상품번호 = a.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
---------------------------------------------------------------------------------------------------------
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.006 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 438.050 452.508 0 1578 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 438.050 452.514 0 1578 0 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1578 pr=0 pw=0 time=452508360 us)
365300 HASH JOIN (cr=1578 pr=0 pw=0 time=456535169 us)
91325 TABLE ACCESS FULL 상품이력 (cr=409 pr=0 pw=0 time=91508 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=730958 us)
---------------------------------------------------------------------------------------------------------------
*hash 조인을 사용했으나 상품별 이력이 평균 913건이기 때문에 해시 테이블 탐색 비용이 높아짐
-BETWEEN 조인 튜닝 요약-
1.대상별 이력 레코드가 많을 때의 between 조인은 좋은 성능을 내기가 쉽지 않음
2.마스터 데이터 건수가 적으면서 변경이 잦은 경우라면 매일 전체 대상 집합을 새로 저장하는 이력 관리 방식
(스냅샷 형태)
(10)조인에 실패한 레코드 읽기
-조인에 실패했을 때 정해진 특정 레코드에서 가져온 값으로 보여주고 싶은 경우
<cdr_rating>
-----------------------------------
국 ''''||지역|| 요금
-- ------------ -----------
82 'A' 100
82 'B' 200
82 'C' 500
82 'D' 300
82 'E' 100
84 'A' 300
84 'B' 500
84 'C' 400
84 ' ' 800
86 'A' 500
86 'B' 200
86 ' ' 700
---------------------------------------
-국가코드82는 모든 지역에 대한 요금 정보를 갖고 있다 .하지만 84의 경우는 A B C가 아닌 지역에 대해서는 일괄적으로 800을
부과할려교 ' '공백문자로 입력
<NL 조인시>
select /*+ ordered use_nl(r) */
c.통화시간, c.국가코드, c.지역, r.요금
from cdr c, cdr_rating r
where c.통화시간 like '20050315%'
and c.국가코드 = r.국가코드
and c.지역 = r.지역;
-----------------------------------------------------
통화시간 국 지역 요금
--------------- -- ---------- -----------
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 300
20050315 050101 84 B 500
20050315 060101 84 C 400
20050315 090101 86 A 500
20050315 100101 86 B 200
--------------------------------------------------------
*일반적인 조인문을 사용하면 조인에 실패한 통화내역은 출력되지 않음
<OUTER 조인>
select /*+ ordered use_nl(r) */
c.통화시간, c.국가코드, c.지역, r.요금
from cdr c, cdr_rating r
where c.통화시간 like '20050315%'
and c.국가코드 = r.국가코드(+)
and c.지역 = r.지역(+);
------------------------------------------------------
통화시간 국 지역 요금
--------------- -- ---------- -----------
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 300
20050315 050101 84 B 500
20050315 060101 84 C 400
20050315 070101 84 D
20050315 080101 84 E
20050315 090101 86 A 500
20050315 100101 86 B 200
20050315 110101 86 C
20050315 120101 86 D
20050315 130101 86 E
20050315 140101 86 F
-----------------------------------------------------
*cdr 기준으로 outer 조인하면 통화내역은 모두 출력되지만 기타지역 통화내역에 대한 요금 정보가 NULL로 출력
-조인에 실패한 지역이 공백 ' '인 요금 정보 출력-
select /*+ ordered use_nl(r) */
c.통화시간, c.국가코드, c.지역, r.요금
from cdr c, cdr_rating r
where c.통화시간 like '20050315%'
and (r.국가코드, r.지역) =
(select c.국가코드, max(지역)
from cdr_rating
where 국가코드 = c.국가코드
and 지역 in (' ', c.지역) );
------------------------------------------------------------
통화시간 국 지역 요금
--------------- -- ---------- -----------
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 300
20050315 050101 84 B 500
20050315 060101 84 C 400
20050315 070101 84 D 800
20050315 080101 84 E 800
20050315 090101 86 A 500
20050315 100101 86 B 200
20050315 110101 86 C 700
20050315 120101 86 D 700
20050315 130101 86 E 700
20050315 140101 86 F 700
--------------------------------------------------------
