정보계 리포트 넷 가운데 둘이 일별 상품 판매를 통째로 읽는다며 인덱스 설계를 맡겼다

실전DDL유통74 / 700

상황

유통 정보계의 「일별 상품 판매」(IDX-027의 집계 테이블)는 매장, 일, 상품별 판매를 한 줄씩 담는다. 리포트 넷이 이 테이블을 읽는데, 점장 앱의 R2와 MD의 R3이 느리다. DW팀장은 "PK 순서를 바꿀지, 인덱스를 몇 개 더 만들지 정해 달라. 적재 창은 01시부터 30분이다"라고 했다. 적재는 매일 01시에 전날 약 400행을 넣고, 매월 1일에는 반품 정정을 반영하려고 지난달 약 12,000행을 지우고 다시 넣는다. 그 밖의 DML은 없다.

테이블과 인덱스

DW_SLE_DD 일별 상품 판매(이 문제에서 쓰는 파생 테이블): 292,000행 · 1,460블록 · 날짜순 적재 · SLE_YMD NUMBER(8) 20220101 ~ 20231231(730일) · SHP_NO 1001 ~ 1005(5곳) · ITM_NO 88종 · SLE_QTY · SLE_AMT · 하루 약 400행(매장마다 그날 팔린 상품 약 80종)
매장 한 곳의 12개월은 약 29,200행 · 상품 하나의 12개월은 약 1,825행(5매장 × 365일)
PK_DW_SLE_DD (SLE_YMD, SHP_NO, ITM_NO): BLEVEL 1 · 리프 약 800블록(리프 한 블록에 약 365항목) · 다른 인덱스 없음 · 통계 최신
이 테이블을 가리키는 FK는 없다

원본 SQL

sql

-- R1 본사 대시보드 「어제 매장별 매출」 (하루 수백 번)
SELECT SHP_NO, SUM(SLE_QTY) AS qty, SUM(SLE_AMT) AS amt
  FROM DW_SLE_DD
 WHERE SLE_YMD = :v_ymd
 GROUP BY SHP_NO;

-- R2 점장 앱 「월별 판매 품목 수」 (매장 하나 · 최근 12개월 · 하루 수십 번)
SELECT TRUNC(SLE_YMD / 100) AS ym, COUNT(DISTINCT ITM_NO) AS itm_cnt
  FROM DW_SLE_DD
 WHERE SHP_NO = :v_shp
   AND SLE_YMD BETWEEN :v_from AND :v_to
 GROUP BY TRUNC(SLE_YMD / 100);

-- R3 MD 「상품 12개월 월별 추이」 (상품 하나 · 전 매장 합 · 하루 수백 번)
SELECT TRUNC(SLE_YMD / 100) AS ym, SUM(SLE_QTY) AS qty, SUM(SLE_AMT) AS amt
  FROM DW_SLE_DD
 WHERE ITM_NO = :v_itm
   AND SLE_YMD BETWEEN :v_from AND :v_to
 GROUP BY TRUNC(SLE_YMD / 100);

-- R4 MD 「매장 상품 판매 이력」 (매장 하나 · 상품 하나 · 1년 · 가끔)
SELECT SLE_YMD, SLE_QTY, SLE_AMT
  FROM DW_SLE_DD
 WHERE SHP_NO = :v_shp
   AND ITM_NO = :v_itm
   AND SLE_YMD >= :v_from
 ORDER BY SLE_YMD;

실행계획 학습용 예시

xplan

[R2 · 매장 1003 · 20230101 ~ 20231231]
----------------------------------------------------------------------------------------------
| Id  | Operation         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |      1 |        |     12 |00:00:00.02 |     402 |
|   1 |  SORT GROUP BY    |              |      1 |     12 |     12 |00:00:00.02 |     402 |
|*  2 |   INDEX RANGE SCAN| PK_DW_SLE_DD |      1 |  29200 |  29190 |00:00:00.02 |     402 |
----------------------------------------------------------------------------------------------
   2 - access("SLE_YMD">=:V_FROM AND "SHP_NO"=:V_SHP AND "SLE_YMD"<=:V_TO)
       filter("SHP_NO"=:V_SHP)

[R3 · 상품 1010 · 20230101 ~ 20231231]
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     12 |00:00:00.02 |    1460 |
|   1 |  HASH GROUP BY     |           |      1 |     12 |     12 |00:00:00.02 |    1460 |
|*  2 |   TABLE ACCESS FULL| DW_SLE_DD |      1 |   1825 |   1825 |00:00:00.02 |    1460 |
---------------------------------------------------------------------------------------------
   2 - filter(("ITM_NO"=:V_ITM AND "SLE_YMD">=:V_FROM AND "SLE_YMD"<=:V_TO))

[R1 · 20231215]  INDEX RANGE SCAN PK_DW_SLE_DD · access("SLE_YMD"=:V_YMD) · 테이블 2블록 · Buffers 5
[R4 · 매장 1003 · 상품 1010 · 2023년]  INDEX RANGE SCAN PK_DW_SLE_DD · access(SLE_YMD 범위) · filter(SHP_NO, ITM_NO) · 테이블 365블록 · Buffers 767

문제

지금 PK로 R1 ~ R4가 각각 어떻게 처리되는지 설명하고, R2와 R3이 무거운 이유를 PK의 선두 컬럼으로 설명하시오.
PK 컬럼 순서를 바꾸는 안과 인덱스를 더하는 안을 함께 따져 R1 ~ R4를 받치는 인덱스 세트를 설계하시오. 각 리포트가 어느 인덱스를 어떻게 쓰는지 설명하시오.
날짜만 주는 R1을 새 설계에서 어떻게 받치는지 SKIP SCAN의 조건으로 설명하고, 이 설계의 대가와 반영 절차를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① PK 순서를 바꾼다 : 종류가 적은 매장을 선두로, 날짜, 상품 (유일성은 순서와 상관없다)
--    R2 · R4 는 매장 = 으로 찾아가고, 날짜만 주는 R1 은 매장 5 곳을 건너뛴다
ALTER TABLE DW_SLE_DD DROP PRIMARY KEY DROP INDEX;
ALTER TABLE DW_SLE_DD ADD CONSTRAINT PK_DW_SLE_DD PRIMARY KEY (SHP_NO, SLE_YMD, ITM_NO);

-- ② 상품으로 찾는 R3 · R4 : 상품 = , 날짜 범위, 매장은 인덱스 안에서 거르고, 합계 컬럼까지 담는다
CREATE INDEX IX_DW_SLE_DD_ITM ON DW_SLE_DD (ITM_NO, SLE_YMD, SHP_NO, SLE_QTY, SLE_AMT);

-- 반영 : 01시 적재가 끝난 뒤 적재 창 안에서 · R1 ~ R4 를 다시 잰다 · 리포트 SQL 은 그대로

개선 후 실행계획 학습용 예시

xplan

[R3 · 고친 뒤]
--------------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |      1 |        |     12 |00:00:00.01 |       9 |
|   1 |  HASH GROUP BY    |                  |      1 |     12 |     12 |00:00:00.01 |       9 |
|*  2 |   INDEX RANGE SCAN| IX_DW_SLE_DD_ITM |      1 |   1825 |   1825 |00:00:00.01 |       9 |
--------------------------------------------------------------------------------------------------
   2 - access("ITM_NO"=:V_ITM AND "SLE_YMD">=:V_FROM AND "SLE_YMD"<=:V_TO)

[R1 · 고친 뒤]
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |      5 |00:00:00.01 |      13 |
|   1 |  HASH GROUP BY                       |              |      1 |      5 |      5 |00:00:00.01 |      13 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DW_SLE_DD    |      1 |    400 |    402 |00:00:00.01 |      13 |
|*  3 |    INDEX SKIP SCAN                   | PK_DW_SLE_DD |      1 |    400 |    402 |00:00:00.01 |      10 |
--------------------------------------------------------------------------------------------------------------
   3 - access("SLE_YMD"=:V_YMD)
       filter("SLE_YMD"=:V_YMD)

[R2 · 고친 뒤]  INDEX RANGE SCAN PK_DW_SLE_DD · access("SHP_NO"=:V_SHP AND "SLE_YMD">=… AND "SLE_YMD"<=…) · 인덱스만 · Buffers 82
[R4 · 고친 뒤]  INDEX RANGE SCAN IX_DW_SLE_DD_ITM · access(ITM_NO, SLE_YMD 범위) · filter(SHP_NO) · 인덱스만 · 정렬 없음 · Buffers 9

모범 답안 서술

지금 PK는 SLE_YMD가 선두다. R1은 날짜를 등치로 주므로 그날 400항목을 바로 찾아가고(5블록), 테이블도 날짜순이라 2블록이면 된다. 하지만 R2와 R3은 날짜를 12개월 범위로 준다. 선두가 범위이면 그 뒤의 SHP_NO와 ITM_NO는 범위를 좁히지 못하고, 12개월 치 PK 항목(리프 약 400블록)을 다 읽으며 거르는 filter가 된다. R2는 키 컬럼만 쓰므로 인덱스만 읽어 402블록이다. R3은 수량과 금액 때문에 테이블에 가야 하는데, 상품 하나의 1,825행이 12개월 치 블록에 흩어져 있어 옵티마이저는 FULL(1,460블록)을 골랐다. R4도 같은 이유로 PK 1년 치를 읽고 테이블 365블록을 더해 767블록이다. PK의 유일성은 컬럼 순서와 상관없다. (SLE_YMD, SHP_NO, ITM_NO)든 (SHP_NO, SLE_YMD, ITM_NO)든 같은 조합이 두 번 들어오지 못하게 막는다. 그래서 PK를 다시 만들 때 순서를 리포트에 맞게 고를 수 있다. 매장을 선두로 (SHP_NO, SLE_YMD, ITM_NO)로 바꾸면 R2는 매장 등치 조건과 날짜 범위가 둘 다 access라 그 매장의 12개월 항목만 읽는다(402 → 82). 날짜만 주는 R1은 선두를 잃지만 매장이 5곳뿐이라, 다섯 덩어리마다 한 번씩 들어가는 SKIP SCAN으로 그날 항목을 찾는다(5 → 13). 선두가 날짜 730일이었다면 반대로 SKIP SCAN은 730번 들어가야 해서 쓸 수 없다. 종류가 적은 컬럼이 선두일 때만 SKIP SCAN이 싸다. R3과 R4는 상품을 등치로 준다. (ITM_NO, SLE_YMD, SHP_NO, SLE_QTY, SLE_AMT)를 더하면 R3은 상품 하나의 12개월 1,825항목만 읽어 인덱스 안에서 합계를 낸다(Buffers 1,460 → 9). R4는 같은 범위에서 매장을 인덱스 안에서 거르고, 날짜 순으로 읽혀 정렬도 없다(767 → 9). 대가는 인덱스 하나(리프 약 1,100블록, 약 9MB)와 적재 때 고칠 항목이다. 이 테이블의 DML은 하루 400행과 월 12,000행뿐이라 작다. PK를 다시 만드는 동안은 유일성 검사가 없으므로 01시 적재가 끝난 뒤 적재 창 안에서 바꾸고, R1 ~ R4를 다시 잰다. 월 1일의 지난달 DELETE(날짜 범위)도 새 PK에서는 매장 5곳을 건너뛰어 찾는다.

해설

운영 판매(CF10)였다면 이렇게 쉽게 인덱스를 늘리지 않는다. 정보계 집계 테이블은 적재가 하루 한 번이라 리포트마다 커버링 인덱스를 두는 대가가 작다. 같은 질문이라도 테이블의 DML 성격이 답을 바꾼다.
PK는 그대로 두고 (SHP_NO, SLE_YMD, ITM_NO) 인덱스를 따로 더해도 된다. 그러면 PK와 컬럼이 같은 인덱스를 하나 더 두는 셈이고, 리프 800블록과 적재 비용이 는다. 순서를 바꿀 수 있으면 바꾼다.
매장이 늘어 수십 곳이 되면 R1의 SKIP SCAN도 다시 따진다. 버거원처럼 200곳이면 이 설계는 맞지 않는다(IDX-065).
R2의 COUNT(DISTINCT ITM_NO)는 키 컬럼만 쓰므로 PK만 읽고 끝난다. 리포트가 금액을 더 보여 달라고 하면 테이블에 가야 하므로, 그때 R2용 커버링을 다시 따진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황