[Oracle] 오라클 고급 JOIN 테크닉 - 점이력 조회

점이력 조회

- 시작일자, 종료일자로 관리되는 선분이력에 반해 점이력은 변경일자만으로 관리

SELECT A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액

   FROM 고객 A, 고객별연체이력 B

 WHERE A.가입회사 = 'C70'

     AND B.고객번호 = A.고객번호

     AND B.변경일자 = (SELECT MAX(C.변경일자)

 FROM 고객별연체이력 C

    WHERE C.고객번호 = A.고객번호

        AND C.변경일자 <= A.서비스만료일); 

-- 찾고자 하는 시점(서비스만료일)보다 앞선 변경일자 중 가장 마지막 레코드 조회

Rows    Row Source Operation

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

  10    TABLE ACCESS BY INDEX ROWID 고객별연체이력 (CR=59 PR=0 PW=0 TIME=177 us)

  21        NESTED LOOPS (CR=49 PR=0 PW=0 TIME=2162 us)

  10      TABLE ACCES BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=142 us)

  10        INDEX RANGE SCAN 고객_IDX01 (cr2 pr=0 pw=0 time=53 us)

  10      INDEX RANGE SCAN 고객별연체이력_IDX01 (cr=45 pr=0 pw=0 time=681 us)

  10        SORT AGGREGATE  (cr=22 pr=0 pw=0 time=464 us)

  10          FIRST ROW (cr=22 pr=0 pw=0 time=283 us)

  10            INDEX RANGE SCAN (MIN/MAX) 고객별연체이력_IDX01 (cr=22 pr=0 pw=0 ..)

- 서브쿼리 내에서 서비스만료일보다 작은 레코드를 모두 스캔하지 않고 오라클이 인덱스를 거꾸로 스캔하면서 가장 큰 값 하나만을 찾는 방식을 사용

- 고객별연체이력_IDX01 인덱스를 두 번 access하는 비효율 발생

- 서브쿼리를 【index_desc 힌트 & rownum<=1 조건】을 이용한 방식으로 바꿀 수도 있지만 여전히 고객별연체이력_IDX01 인덱스를 두 번 access할 뿐만 아니라 버퍼 Pinning 효과가 사라져 오히려 블록 I/O가 더 많이 발생

* first row(min/max) 알고리즘이 작동할 때는 반드시 min/max 함수를 사용하는 것이 바람직

   (단, min 또는 max 함수 내에서 컬럼을 가공하면 first row  알고리즘 작동 X)

▼
서브쿼리를 스칼라 서브쿼리로 변환

SELECT A.고객명, A.거주지역, A.주소, A.연락처,

(SELECT /*+ index_desc(B 고객별연체이력_idx01) */ 연체금액

   FROM 고객별연체이력 B

WHERE B.고객번호 = A.고객번호

    AND B.변경일자 <= A.서비스만료일

    AND ROWNUM <= 1) 연체금액

  FROM 고객 A

WHERE A.가입회사 = 'C70'

-- 인덱스를 두 번 액세스하지 않아도 되기 때문에 I/O 감소 (버퍼 Pinning 효과는 사라짐)

고객별연체이력 테이블에서 두 개 이상 컬럼을 읽어야 할 경우

SELECT 고객명, 거주지역, 주소, 연락처,

TO_NUMBER(SUBSTR(연체, 3)) 연체금액,

TO_NUMBER(SUBSTR(연체, 1, 2) 연체개월수

  FROM (SELECT A.고객명, A.거주지역, A.주소, A.연락처,

 (SELECT /*+ index_desc(B 고객별연체이력_idx01) */ 

   LPAD(연체개월수,2) || 연체금액

    FROM 고객별연체이력 B

 WHERE B.고객번호 = A.고객번호

      AND B.변경일자 <= A.서비스만료일

      AND ROWNUM <= 1) 연체

  FROM 고객 A

     WHERE A.가입회사 = 'C70' 

);

▼

SELECT /*+ ordered use_nl(B) rowid(B) */ A.*, B.연체금액, B.연체개월수

  FROM (SELECT A.고객명, A.거주지역, A.주소, A.연락처,

  (SELECT /*+ index_desc(B 고객별연체이력_idx01) */ ROWID

      FROM 고객별연체이력 B

    WHERE B.고객번호 = A.고객번호

        AND B.변경일자 <= A.서비스만료일

        AND ROWNUM <= 1) RID

    FROM 고객 A

 WHERE A.가입회사 = 'C70') A, 고객별연체이력 B

WHERE B.ROWID = A.ROWID;

- 고객별연체이력 테이블과 JOIN을 두 번 했지만 RowID를 이용했으므로 JOIN을 한 번만 한 것과 일량이 같음

▼
스칼라 서브쿼리를 서브쿼리로 변환

SELECT  /*+ ordered use_nl(B) rowid(B) */

 A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액, B.연체개월수

  FROM 고객 A, 고객별연체이력 B

WHERE A.가입회사 = 'C70'

    AND B.ROWID = (SELECT /*+ index_desc(C 고객별연체이력_idx01) */ ROWID

            FROM 고객별연체이력 C

          WHERE C.고객번호 = A.고객번호

              AND C.변경일자 <= A.서비스만료일

              AND ROWNUM <= 1);

정해진 시점 기준으로 조회

-- 모든 고객을 대상으로 이력 조회 (가입회사 = 'C70' 조건 X)

SELECT /*+ full(A) full(B) full(C) use_hash(A B C) no_merge(B) */ 

 A.고객명, A.거주지역, A.주소, A.연락처, C.연체금액, C.연체개월수

  FROM 고객 A,

(SELECT 고객번호, MAX(변경일자) 변경일자

    FROM 고객별연체이력

 WHERE 변경일자 <= TO_CHAR(SYSDATE, 'YYYYMMDD')

 GROUP BY 고객번호) B, 고객별연체이력 C

WHERE B.고객번호 = C.고객번호

    AND C.고객번호 = A.고객번호

    AND C.변경일자 = B.변경일자;

-- 고객별연체이력 테이블을 두 번 Full Scan

▼

SELECT A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액, B.연체개월수

  FROM 고객 A,

(SELECT 고객번호, 연체금액, 연체개월수,

  ROW_NUMBER() OVER (PARTITION BY 고객번호 ORDER BY 변경일자 DESC) NO

    FROM 고객별연체이력

 WHERE 변경일자 <= TO_CHAR(SYSDATE, 'YYYYMMDD')) B

WHERE B.고객번호 = A.고객번호

    AND B.NO = 1;