고객 12개월 추이를 보려고 월 실적 테이블의 PK를 열두 달 치 다 훑는다

중급SELECT한빛유통119 / 700

상황

고객센터 상담 화면에는 「고객 12개월 추이」가 있다. 상담원이 고객을 열면 최근 12개월의 월별 주문 건수와 금액을 띄운다. 하루 2,000번쯤 열린다. 데이터는 마감 배치가 매달 초 T_ORD_BIG에서 고객별로 집계해 넣는 월 실적 테이블에서 온다. 조민우는 (CUS_ID, ORD_YM) 인덱스를 하나 더 만들자고 했다. 배치 담당 오과장은 이렇게 답했다. "월 마감 배치가 쓰는 테이블의 인덱스는 늘리기 싫다. 지금 있는 걸로 안 되나?"

테이블과 인덱스

T_CUS_MON 고객 월 실적(T_ORD_BIG의 파생 테이블, 이 문제에서 정의): ORD_YM VARCHAR2(6) · CUS_ID · ORD_CNT · ORD_AMT · 2022-01 ~ 2024-12의 36개월 × 고객 2,000명 = 72,000행 · 320블록 · 매달 초 한 달 치 2,000행을 CUS_ID 순으로 덧붙인다
인덱스 PK_T_CUS_MON (ORD_YM, CUS_ID): BLEVEL 1 · 리프 약 266블록(리프 한 블록에 약 270항목, 한 달 2,000항목이 리프 약 7.4블록) · 통계 최신
다른 인덱스는 없다 · 「월 고객 순위」 화면(ORD_YM = :ym)도 PK를 쓴다

원본 SQL

sql

SELECT ORD_YM, ORD_CNT, ORD_AMT
  FROM T_CUS_MON
 WHERE CUS_ID = :v_cus
   AND ORD_YM BETWEEN :v_ym_from AND :v_ym_to
 ORDER BY ORD_YM

실행계획 학습용 예시 · T_CUS_MON은 T_ORD_BIG에서 집계한 파생 테이블(이 문제에서 정의)

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |     12 |00:00:00.01 |     103 |
|*  1 |  FILTER                      |              |      1 |        |     12 |00:00:00.01 |     103 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_CUS_MON    |      1 |     12 |     12 |00:00:00.01 |     103 |
|*  3 |    INDEX RANGE SCAN          | PK_T_CUS_MON |      1 |     12 |     12 |00:00:00.01 |      91 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:V_YM_FROM<=:V_YM_TO)
   3 - access("ORD_YM">=:V_YM_FROM AND "CUS_ID"=:V_CUS AND "ORD_YM"<=:V_YM_TO)
       filter("CUS_ID"=:V_CUS)

문제

3번 줄이 91블록을 읽는 이유를 access와 filter로 설명하시오.
인덱스를 새로 만들지 않고 읽는 양을 줄이도록 SQL을 고치시오. 그 방법이 여기서 효과가 있는 이유를 선두 컬럼의 분포로 설명하시오.
조민우 안 (CUS_ID, ORD_YM)과 Buffers, 적재 비용을 비교해 어느 쪽을 고를지 판단하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 인덱스를 늘리지 않는다 — 범위 안 선두 값 12 개마다 '그 달, 그 고객' 자리로 건너뛴다
SELECT /*+ INDEX_SS(m PK_T_CUS_MON) */
       m.ORD_YM, m.ORD_CNT, m.ORD_AMT
  FROM T_CUS_MON m
 WHERE m.CUS_ID = :v_cus
   AND m.ORD_YM BETWEEN :v_ym_from AND :v_ym_to
 ORDER BY m.ORD_YM;

개선 후 실행계획 학습용 예시 · T_CUS_MON은 T_ORD_BIG에서 집계한 파생 테이블(이 문제에서 정의)

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |     12 |00:00:00.01 |      37 |
|*  1 |  FILTER                      |              |      1 |        |     12 |00:00:00.01 |      37 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_CUS_MON    |      1 |     12 |     12 |00:00:00.01 |      37 |
|*  3 |    INDEX SKIP SCAN           | PK_T_CUS_MON |      1 |     12 |     12 |00:00:00.01 |      25 |
---------------------------------------------------------------------------------------------------------
   1 - filter(:V_YM_FROM<=:V_YM_TO)
   3 - access("ORD_YM">=:V_YM_FROM AND "CUS_ID"=:V_CUS AND "ORD_YM"<=:V_YM_TO)
       filter("CUS_ID"=:V_CUS)

[비교 — 조민우 안 (CUS_ID, ORD_YM) 을 INVISIBLE 로 재 본 결과] INDEX RANGE SCAN 2 + 테이블 12 = 14

모범 답안 서술

3번 줄의 access에는 ORD_YM 범위와 CUS_ID가 다 있지만 filter에 CUS_ID가 또 있다. PK는 ORD_YM → CUS_ID 순이므로 선두 ORD_YM의 범위가 읽을 구간을 정하고, 그 뒤의 CUS_ID는 구간을 줄이지 못하고 filter로 거를 뿐이다. 그래서 열두 달 치 24,000항목(리프 약 89블록)을 다 읽고 12건을 남긴다. 테이블 12블록을 더해 103블록이다. 인덱스를 늘리지 않고 줄이려면 INDEX_SS 힌트로 INDEX SKIP SCAN을 쓰게 한다. SKIP SCAN은 선두 컬럼이 범위 조건일 때도 쓸 수 있다. 범위 안의 ORD_YM 값은 12개뿐이고, 값마다 고객 2,000명의 구간(리프 약 7.4블록)이 있다. 달마다 '그 달, 그 고객' 자리로 찾아 내려가 리프 한 블록만 읽고 나머지 6 ~ 7블록은 건너뛰므로, 인덱스는 91 → 약 25블록, 전체는 103 → 약 37블록이다. ORD_YM 순서로 나오므로 정렬도 붙지 않는다. ORD_YM IN ('202401', …, '202412')처럼 달을 목록으로 풀어도 같은 효과지만, 화면이 기간에 맞춰 목록을 만들어야 한다. 조민우 안 (CUS_ID, ORD_YM)은 고객의 12항목만 읽어 약 14블록이다. SKIP SCAN보다 23블록 적고, 하루 2,000번이면 하루 약 46,000블록 차이다. 이 테이블은 한 달에 한 번 2,000행을 넣으므로 인덱스 하나의 적재 비용은 미미하다. 그래도 하루 4만 블록대의 차이는 작아서, 오과장의 원칙(배치 대상 테이블의 인덱스를 늘리지 않는다)을 지키고 SKIP SCAN을 고른다. 고객 번호로 찾는 화면이 더 생기면 그때 (CUS_ID, ORD_YM)을 만든다.

해설

SKIP SCAN의 비용은 '범위 안 선두 값의 수 × 한 번 찾아 내려가는 비용'이다. 24개월을 고르면 24번, 36개월이면 36번으로 는다. 그래도 값마다 구간이 리프 7블록이 넘으니 범위 스캔보다 적다. 반대로 값마다 구간이 리프 몇 블록뿐이면 건너뛰어 아끼는 양이 작다(ACC-010의 하루 913항목, 리프 약 4블록).
범위 스캔 91블록이 이미 작아 보이면 옵티마이저는 SKIP SCAN을 고르지 않을 수 있다. 힌트로 고정하므로 힌트 대장에 '고객이 12개월 안팎을 고르는 화면'이라는 전제를 적는다.
월 실적처럼 '기간 → 대상' 순서로 쌓는 집계 테이블은 PK가 (기간, 대상)이 되기 쉽다. 대상 하나의 추이를 보는 화면이 생기면 이 문제가 된다. 집계 테이블을 설계할 때 대상별 조회가 얼마나 잦을지 함께 묻는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · T_CUS_MON은 T_ORD_BIG에서 집계한 파생 테이블(이 문제에서 정의) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · T_CUS_MON은 T_ORD_BIG에서 집계한 파생 테이블(이 문제에서 정의) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황