[Oracle] 오라클 고급 JOIN 테크닉 - 징검다리 테이블 JOIN을 이용한 튜닝

징검다리 테이블 JOIN을 이용한 튜닝

-- 고객의 할인혜택 조회

SELECT /*+ ordered use_nl(S R) */ C.고객번호, S.서비스번호, S.서비스구분코드,

 S.서비스상태코드, S.서비스상태변경코드, R.할인시작일자, R.할인종료일자

   FROM 고객 C, 서비스 S, 서비스요금할인 R

 WHERE C.주민법인등록번호 = :ctz_biz_num

     AND S.명의고객번호 = C.고객번호

     AND R.서비스번호 = S.서비스번호

     AND R.서비스상품그룹 = '3001'

     AND R.할인기간코드 = '15'

 ORDER BY R.할인종료일자 DESC, S.서비스번호;

-- 인덱스 구성

고객_N1 : 주민법인등록번호

서비스_N2 : 명의고객번호 + 서비스번호

서비스요금할인_PK : 서비스번호 + 서비스상품그룹

서비스요금할인_N1 : 서비스상품그룹 + 할인기간코드

① 주민법인등록번호 = :ctz_biz_num 조건으로 고객_N1 index range scan 

    → 고객 테이블 access (고객 번호가 인덱스 컬럼이 아니므로..)

② 위에서 선택된 고객 번호로 서비스_N2 index range scan

    → 서비스 테이블 access (select 절에 인덱스 컬럼이 아닌 애들이 있으므로..)

    * 서비스 테이블에서 선택된 건들이 76455 건 (모든 조건이 인덱스 컬럼이므로 테이블에서 별도의 필터링이 없음)

③ 위에서 선택된 서비스 번호로 서비스요금할인_PK index unique scan

    → 서비스요금할인 테이블 access (서비스 번호가 인덱스 컬럼이 아니고 select 절에도 아닌 애들이 있음)

    * 서비스요금할인 테이블에서 서비스 번호로 JOIN된 건들이 6837 건, 그 중에 할인기간코드가 15인 건들이 183 건

▶ 총 블록I/O 226,672번 발생

- JOIN 결과는 많지 않으나 각 테이블 내에 조건에 맞는 데이터 건수가 아주 많을 경우 드라이빙 테이블을 바꿔도 JOIN access량을 줄이기가 어려움

- 테이블 JOIN 시 과도한 부하 발생

튜닝 방안

-- 서비스요금할인_N1 서비스상품그룹 + 할인기간코드 + 서비스번호 (JOIN 컬럼 추가) 

SELECT /*+ ordered use_hash(R_BRDG) rowid(S) rowid(R) */

            C.고객번호, S.서비스번호, S.서비스구분,

 S.서비스상태코드, S.서비스상태변경코드, R.할인시작일자, R.할인종료일자

  FROM  고객 C,

            서비스 S_BRDG, 서비스요금할인 R_BRDG,

            서비스 S, 서비스요금할인 R

WHERE C.주민법인등록번호 = :CTZ_BIZ_NUM

    AND C.고객번호 = S_BRDG.명의고객번호

    AND S_BRDG.서비스번호 = R_BRDG.서비스번호

    AND R_BRDG.서비스상품그룹 = '3001'

    AND R_BRDG.할인기간코드 = '15'

    AND S.ROWID = S_BRDG.ROWID

    AND R.ROWID = R_BRDG.ROWID

ORDER BY R.할인종료일자 DESC, S.서비스번호

① 주민법인등록번호 = :ctz_biz_num 조건으로 고객_N1 index range scan 

    → 고객 테이블 access (고객 번호가 인덱스 컬럼이 아니므로..)

② 위에서 선택된 고객 번호로 서비스_N2 index range scan

③ 위에서 선택된 데이터를 Hash Area에 올림

④ 서비스요금할인_N1이 비드라이빙이 되어 서비스_N2를 탐색하며 JOIN

    * 서비스번호를 인덱스 컬럼에 추가했으므로 인덱스만으로 JOIN 가능

⑤ 최종 선택된 183건의 데이터만 RowID를 이용해 테이블 access (select 절에 있는 데이터 get)

▶ 총 블록I/O 857번 발생

- 서비스와 서비스요금할인 테이블을 두 번씩 access 하도록 쿼리를 작성했지만 실제 처리 일량은 한 번만 한 것과 동일