개요

• 진행 중인 프로젝트는 서로 다른 DB 구조끼리 데이터를 변환하고, 다시 변경 구조에 맞추어 데이터를 조회해야 함
• 변경된 DB는 정규화로 인해 원하는 데이터 구조를 만들어내기 위해서는 다수의 조인이 들어감
• 이 때 특정 쿼리가 굉장히 느리고, 인덱스 풀스캔을 타는 경우가 있어 이를 공부하고 튜닝한 기록을 남김

대상 데이터

• 최소 2개월 ~ 최대 36개월까지의 신용카드 무이자 할부 정보
• 가맹점/카드사 할부 정보 0만개 이상

대상 쿼리

• 글 작성을 위해 컬럼명 등은 한글로 표기 변경 및 기타 쿼리 제거 (8개 테이블 추가 조인)
• 원하는 조건을 통해 해당 가맹점-카드사가 사용할 수 있는 무이자 할부개월을 BETWEEN을 조인조건절로 삼고 조회하는 목적

이슈 범위

BETWEEN만을 이용한 조인 조건 유도

SELECT 
    T1.카드사 AS CARD_ID
	, V1.할부개월 AS MONTH
	, NVL(T1.할부적용금액, 50000) AS BASE_AMT
FROM CARD.카드사_할부정보 T1
	, (SELECT LEVEL AS 할부개월 FROM DUAL CONNECT BY LEVEL <= 36) V1
WHERE V1.할부개월 BETWEEN T1.할부시작월 AND T1.최대할부종료월

LEFT JOIN을 통한 조인 조건 강제 명시

SELECT 
    T1.카드사 AS CARD_ID
	, V1.할부개월 AS MONTH
	, NVL(T1.할부적용금액, 50000) AS BASE_AMT
FROM CARD.카드사_할부정보 T1
LEFT JOIN (SELECT LEVEL AS 할부개월 FROM DUAL CONNECT BY LEVEL <= 36) V1
    ON V1.할부개월 BETWEEN T1.할부시작월 AND T1.최대할부종료월

옵티마이저의 생각

BETWEEN만을 이용한 조인 조건 유도의 경우

실제 실행 시간

• 데이터에 따라 최소 3s 이상, 최대 실행 불가로 측정 불가

이유

• V1.할부개월 1~36까지 생성
• 옵티마이저는 실행계획을 구성 시 필터링 할 데이터의 크기를 가늠하기 어려움
• 데이터의 크기를 먼저 구성하기 위해 옵티마이저는 T1의 모든 행과 BETWEEN 연산 수행 계획 실행
• 조인 조건이 WHERE 절에서 적용되므로 옵티마이저는 T1의 모든 행 × V1의 36개 행의 카타시안 조인(Cartesian Join)을 먼저 수행한 후 BEWTWEEN 필터링으로 동작
• 이로 인해 1행 비교의 NESTED LOOPS 조인이 수행 되고, T1의 모든 행 × V1의 36개 행 비교가 이루어졌음

결론
🚨 옵티마이저가 WHERE 조건절의 BETWEEN 필터링으로 인식
🚨 데이터 산출을 위해 NESTED LOOPS 방식으로 동작하고 BETWEEN FILTER가 추가로 적용

LEFT JOIN 조인 조건 명시의 경우

실제 실행 시간

• 0.5ms 이내

이유

• 옵티마이저에게 명확한 조인 조건을 명시하여 NESTED LOOPS → HASH JOIN 사용 유도

  • 번외) HASH JOIN의 특징
    조인 조건 테이블을 메모리 선적재 후 테이블 매칭 → 연산량 대폭 감소

• V1의 할부개월 36개 행에 대한 조인 조건절로 T1 테이블 매칭

  • 옵티마이저가 V1과 T1를 먼저 결합한 후 BETWEEN 필터링을 적용
  • T1.시작할부개월 ~ T1.최대할부종료월 범위에 있는 V1.할부개월 값만 비교
  • 기존 쿼리는 WHERE 조건이 적용 전 V1 × T1 모든 데이터 비교

• 옵티마이저 조인 조건 최적화

  • LEFT JOIN은 T1를 기준으로 V1과 조인
  • T1의 인덱스 검색 → 조인으로 INDEX RANGE SCAN 적용

결론

✅ 조인 조건의 유도가 아닌 명확한 명시는 필수
✅ 비교 대상의 데이터가 많으면 많을수록 JOIN 자체를 어떻게 구성할지 고민 필요
✅ 다수 조인은 옵티마이저의 실행 계획 최적화를 위해 기준 테이블을 잡아주는 LEFT JOIN을 사용

• 이때 WHERE 조건을 통해 기준 테이블을 절대 다시 건들면 안된다는 점 유의

✅ 조회 조건의 경우 반드시 INDEX를 탈 수 있도록 구성

• JOIN 키에는 반드시 인덱스를 적용하여 INDEX RANGE SCAN 유도

✅ 데이터 필터링이 가능한 경우, 먼저 필터링을 적용한 후 조인을 수행(절대적 연산 데이터 감소)