정산 테이블에 처음 만든 인덱스가 고객 한 명의 석 달 치에 50블록을 읽는다

기초DDL한빛유통90 / 700

상황

야간 배치가 채우는 정산 테이블 T_ORD_STLM에는 인덱스가 하나도 없었다. 고객센터가 「고객 정산 내역」(고객 한 명의 석 달 치)을 쓰기 시작하자 조민우가 첫 인덱스를 만들었다. "범위 스캔을 하려면 범위 조건 컬럼이 맨 앞에 있어야 한다." 그래서 (STLM_DT, CUS_ID) 순서로 만들었다. 상담원 서른 명이 하루 6천 번쯤 연다. 아래는 고객 CUS_000123의 2024년 2분기다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · 정산 배치가 매일 밤 비우고(TRUNCATE) T_ORD에서 직접 경로(APPEND)로 다시 채운다 · STLM_DT DATE(시분초 있음, 2023 ~ 2024, 하루 약 137건) · 고객당 50건
2024년 2분기 정산: 12,467건 · 그중 CUS_000123은 6건
인덱스 IX_STLM_DT_CUS (STLM_DT, CUS_ID): BLEVEL 1 · 리프 약 392블록(리프 한 블록에 약 255항목) · CLUSTERING_FACTOR 약 100,000 · 통계 최신

원본 SQL

sql

CREATE INDEX IX_STLM_DT_CUS ON T_ORD_STLM (STLM_DT, CUS_ID);

SELECT STLM_SEQ, ORD_SEQ, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE CUS_ID  = :v_cus
   AND STLM_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND STLM_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY STLM_DT;

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |      6 |00:00:00.01 |      56 |
|*  1 |  FILTER                      |                |      1 |        |      6 |00:00:00.01 |      56 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_STLM     |      1 |      6 |      6 |00:00:00.01 |      56 |
|*  3 |    INDEX RANGE SCAN          | IX_STLM_DT_CUS |      1 |      6 |      6 |00:00:00.01 |      50 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("STLM_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "CUS_ID"=:V_CUS AND "STLM_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
       filter("CUS_ID"=:V_CUS)

문제

6건을 찾는 데 3번 줄이 50블록을 읽는 이유를 Predicate Information으로 설명하시오. 조민우의 말이 왜 틀렸는지도 설명하시오.
인덱스를 다시 설계해 DDL을 작성하고, 이 인덱스가 야간 적재에 주는 부담을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

DROP INDEX IX_STLM_DT_CUS;

-- = 로 들어오는 고객을 앞에, 범위인 정산일을 뒤에 — '이 고객의 2분기' 가 한 구간
CREATE INDEX IX_STLM_CUS_DT ON T_ORD_STLM (CUS_ID, STLM_DT);

SELECT STLM_SEQ, ORD_SEQ, STLM_DT, STLM_AMT          -- SQL 은 그대로
  FROM T_ORD_STLM
 WHERE CUS_ID  = :v_cus
   AND STLM_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND STLM_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY STLM_DT;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |      6 |00:00:00.01 |       8 |
|*  1 |  FILTER                      |                |      1 |        |      6 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_STLM     |      1 |      6 |      6 |00:00:00.01 |       8 |
|*  3 |    INDEX RANGE SCAN          | IX_STLM_CUS_DT |      1 |      6 |      6 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("CUS_ID"=:V_CUS AND "STLM_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "STLM_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

3번 줄의 access에 "CUS_ID"=:V_CUS가 있지만 filter에도 또 있다. 선두 STLM_DT가 범위(석 달)로 들어왔으므로 뒤의 CUS_ID는 구간을 줄이지 못하고 filter로 거른다. STLM_DT에는 시분초가 있어서 값이 거의 다 달라, CUS_ID가 시작점을 좁히는 효과도 없다. 그래서 2분기 12,467항목, 리프 약 49블록을 다 읽고 6건만 남긴다(Buffers 50, 전체 56). 조민우의 말은 'RANGE SCAN'이라는 이름을 오해한 것이다. 인덱스 범위 스캔은 리프에서 연속된 구간을 읽는다는 뜻이고, CUS_ID = :v도 '이 고객의 항목'이라는 구간을 만든다. 범위 조건 컬럼이 앞에 있을 필요는 없다. 오히려 =로 들어오는 컬럼을 앞에 두어야 그 뒤의 범위 조건까지 access로 쓰여 구간이 좁아진다. (CUS_ID, STLM_DT)로 다시 만들면 '이 고객의 2분기'가 한 구간이 되어 리프 한 블록을 읽는다. Buffers는 56 → 8(인덱스 2, 테이블 6)이고, 하루 6천 번이면 약 34만 → 5만 블록이다. 그 구간 안은 STLM_DT 순이라 ORDER BY STLM_DT도 정렬 없이 처리된다. 야간 적재의 부담도 적는다. 이 테이블은 매일 밤 TRUNCATE 뒤에 직접 경로(APPEND)로 10만 건을 채운다. 직접 경로 적재는 인덱스를 행마다 고치지 않고 적재가 끝난 뒤 한꺼번에 정렬해 만들므로 일반 INSERT보다 싸지만, 정렬 시간과 TEMP는 더 든다. 적재 시간이 마감에 걸리면 적재 전에 인덱스를 UNUSABLE로 두고 적재 뒤 REBUILD하는 순서도 있다(교재 14화).

해설

범위 조건이 선두이고 뒤에 = 조건이 있는 모양은 ACC-001, ACC-004와 같다. 이번 오해는 선택도가 아니라 이름이다. INDEX RANGE SCAN은 등치 조건만으로도 나온다(교재 02화 §2).
시분초가 있는 날짜를 선두에 두면 값이 거의 다 달라서, 뒤 컬럼으로 시작점을 좁히는 효과마저 사라진다. 자정만 있는 T_ORD_BIG의 ORD_DT보다 더 나쁘다.
다른 SQL이 STLM_DT 하나로 하루치를 찾는다면(일 정산 합계 등) 그때 STLM_DT 선두 인덱스를 따로 검토한다. 하루 137건이라 FULL(640블록)로 견딜 수 있는지도 같이 잰다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황