8. 고급 조인 테크닉-2

조회 수 10598 추천 수 0 2011.04.13 21:01:50
오라클잭 *.176.29.44

(8)선분이력조인

_____________________________________________________________________________

선분이력 스캔 효율 높이는 방법(1장 복습)

*어느 시점을 주로 조회하느냐에 따라 인덱스 구성 전략을 달리 해야 한다

  -최근데이터: 종료일 + 시작일

  -과거데이터: 시작일 + 종료일

 

*rownum 과 index_desc 힌트를 적절히 사용여 필요헌 한건만 스캔

 -인덱스 구성 시작일 + 종료일 : index_desc 와 rownum <=1

 -인덱스 구성 종료일 + 시작일 : rownum <=1

 

*현재 시점 데이터 조회(미래시점 데이터를 미리 입력하는 경우가 없다면)

-between 사용하기보다 종료일='99991231 조건이 효과적

_____________________________________________________________________________

 

<과거/현재/미래의 임의 시점 조회>

-2개 이상의 선분이력을 함께 조인 (고객등급변경이력과 전화번호이력 테이블의 선분이력)

 

--------------------------------------------------------------------------
select c.고객번호,c.고객명,c1.고객등급,c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호=123
and c1.고객번호=c.고객번호
and c2.고객번호=c.고객번호
and :dt between c1 시작일자 and c1.종료일자
and :dt between c2 시작일자 and c2.종료일자  
      //dt 변수: 20040901

--------------------------------------------------------------------------

                                                                       20040901

                 20040306              20040510                     20041028                         20050608                99991231

고객등급     | ============== | =======|  |===== | ============ | ============ | 

                                         C                                B |                             A                                C

                                                                                                     

                20040306                             20040604                         20050316                             99991231

전화번호     | ================= | ====| |========= | ================= | 

                                          123-1234                    987-6543                           123-4567            

 

*dt 변수: 20040901에 의해 고객등급 B, 고객전화번호 987-6543 조회

 

 

<현재시점조회>
-미래 시점 데이터를 미리 입력하는 예약기능이 없다면 현재시점은 '=' 조건이 효과적

----------------------------------------------------------------------------------
select c.고객번호,c.고객명,c1.고객등급,c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호=123
and c1.고객번호=c.고객번호
and c2.고객번호=c.고객번호
and c1.종료일자='99991231'
and c2.종료일자='99991231'

------------------------------------------------------------------------------------

 

-미래시점 데이터를 미리 입력해 두는 기능이 있다면 ,현재 시점을 조회할 때 sysdate 와 between 사용

----------------------------------------------------------------------------------------
select c.고객번호,c.고객명,c1.고객등급,c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호=123
and c1.고객번호=c.고객번호
and c2.고객번호=c.고객번호
and to_char(sysdate,'yyyymmdd') between c1 시작일자 and c1.종료일자
and to_char(sysdate,'yyyymmdd') between c2 시작일자 and c2.종료일자

-------------------------------------------------------------------------------------------

 

                                                                                                                   20050607

                 20040306              20040510                     20041028                                      20050608                99991231

고객등급     | ============== | ============= | ===========|  |== | =========== | 

                                         C                                B |     ?                        A                                C

                                                                                                     

                20040306                             20040604                         20050316                             99991231

전화번호     | ================== | ================ | ====|  |============ | 

                                          123-1234                    987-6543                           123-4567     

 

 

 

(9)선분이력 조인 튜닝

 --------------------------------   TEST   -----------------------------

 

<고객테이블>

-index 고객_idx01 (가입회사)

고객번호 고객명 가입회사 거주지역 주소 연락처 서비스만료일
7369 SMITH C70 서울 ... 123-7369 20591005
7499 ALLEN C70 서울 ... 123-7499 21140709
7521 WARD C70 서울 ... 123-7521 21690411
7566 JONES C70 서울 ... 123-7566 22240114
7654 MARTIN C70 서울 ... 123-7654 22781017
7698 BLAKE C70 서울 ... 123-7698 23330721
7782 CLARK C70 서울 ... 123-7782 23880423
7788 SCOTT C70 서울 ... 123-7788 24430125
7839 KING C70 서울 ... 123-7839 24971028
7844 TURNER C70 서울 ... 123-7844 25520801

 

<고객별 연체이력>

index 고객별연체이력_idx01 (고객번호, 종료일, 시작일)

고객번호 시작일 종료일 연체개월수 연체금액
7369 20050103 20050104 3 51800
7499 20050103 20050104 3 51800
7521 20050103 20050104 3 51800
7566 20050103 20050104 3 51800
7654 20050103 20050104 3 51800
7698 20050103 20050104 3 51800
7782 20050103 20050104 3 51800
7788 20050103 20050104 3 51800
7839 20050103 20050104 3 51800
7844 20050103 20050104 3 51800
7369 20050105 20050106 6

56900

---------------------------------------------------------------------------------------------------------------------------------------- 

 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

--------------------------------------------------------

 

조인유형 데이터 상황 튜닝 방안
정해진 시점으로  조회대상이 많지 않을 때 인덱스 순서 조정
선분이력과 단순조인 조회대상이 많을 때 해시조인
Between 조인 조회대상이 많지 않을 때 스칼라서브쿼리에 stopkey조건사용
조회대상이 많지만 대상별 해시조인
이력레코드가 많지 않을때 ?