메뉴별 월 추이를 빠르게 하려고 판매상품 테이블을 메뉴 순으로 다시 쌓자는 제안서가 올라왔다

실전DDL유통107 / 700

상황

MD팀의 「메뉴 월별 판매 추이」는 메뉴 하나의 2년 치 수량과 금액을 월별로 그린다. MD 여섯 명이 하루 수백 번 연다. 콜드브루(1010)를 고르면 7천 블록을 읽는다. 조민우의 제안서는 이렇다. "교재 04화에서 배운 대로 CF11을 (ITM_NO, SLE_NO) 순으로 정렬해 다시 쌓으면 CF가 좋아져 144블록이면 된다. 복사본으로 재 봤다. 점검 시간 30분이면 끝난다." 오과장은 이렇게 답했다. "CF11은 매장 POS의 영수증 재출력과 매장 마감도 쓴다. 다른 화면에 무슨 일이 생기는지까지 숫자로 보자."

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록(블록당 약 134행) · 판매번호(SLE_NO 'YYYYMMDD-NNNN') 순으로 쌓였다 · 영수증 한 장에 평균 2.2줄 · POS가 매일 약 1,500줄을 INSERT한다
콜드브루(1010) 15,493줄은 8,204블록 가운데 7,025블록에 흩어져 있다 · 2023-12-15 판매상품 1,498줄
CF11_PK (SLE_NO, ITM_NO): BLEVEL 2 · 리프 약 4,290 · CLUSTERING_FACTOR 16,735
IX_CF11_ITMNO (ITM_NO): BLEVEL 2 · 리프 약 1,830 · CLUSTERING_FACTOR 387,422 · 이 화면이 쓴다 · 통계 최신
복사본 CF11_NEW: 같은 데이터를 (ITM_NO, SLE_NO) 순으로 쌓고 같은 인덱스를 만든 것 · CF11_PK에 해당하는 인덱스의 CF 1,050,135 · (ITM_NO) 인덱스의 CF 약 8,200

원본 SQL

sql

-- S1 「메뉴 월별 판매 추이」 (MD · 하루 수백 번)
SELECT SUBSTR(SLE_NO, 1, 6) AS ym, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11
 WHERE ITM_NO = :v_itm
 GROUP BY SUBSTR(SLE_NO, 1, 6)
 ORDER BY ym;

-- S2 「영수증 상세」 (매장 POS 재출력 · 하루 수천 번)
SELECT ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11
 WHERE SLE_NO = :v_sle_no;

-- S3 「일 판매상품 집계」 (매장 마감 화면과 야간 마감 · 하루 수백 번)
SELECT ITM_NO, SUM(SLE_QTY) AS qty
  FROM CF11
 WHERE SLE_NO >= :v_dt
   AND SLE_NO <  :v_next_dt
 GROUP BY ITM_NO;

-- 제안서
CREATE TABLE CF11_NEW AS SELECT * FROM CF11 ORDER BY ITM_NO, SLE_NO;

실행계획 학습용 예시 · 건수는 적재 데이터(CF11) 그대로 · 재적재 뒤 숫자는 같은 데이터를 메뉴 순으로 다시 쌓았다고 보고 센 값

xplan

[S1 · 콜드브루 1010 · 지금]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |     24 |00:00:00.06 |    7053 |
|   1 |  SORT GROUP BY                       |               |      1 |     24 |     24 |00:00:00.06 |    7053 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF11          |      1 |  12463 |  15493 |00:00:00.05 |    7053 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF11_ITMNO |      1 |  12463 |  15493 |00:00:00.01 |      28 |
----------------------------------------------------------------------------------------------------------------
   3 - access("ITM_NO"=:V_ITM)

[S3 · 2023-12-15 · 지금]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |     62 |00:00:00.01 |      32 |
|   1 |  HASH GROUP BY                       |               |      1 |     88 |     62 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF11          |      1 |   1500 |   1498 |00:00:00.01 |      32 |
|*  3 |    INDEX RANGE SCAN                  | CF11_PK       |      1 |   1500 |   1498 |00:00:00.01 |       8 |
----------------------------------------------------------------------------------------------------------------
   3 - access("SLE_NO">=:V_DT AND "SLE_NO"<:V_NEXT_DT)

[같은 SQL 을 복사본 CF11_NEW 에서 · Buffers]
  S1 콜드브루      7,053  →    144
  S2 영수증 한 장      4  →  약 5     (한 장의 줄이 평균 2.2블록에 흩어진다)
  S3 하루 1,498줄     32  →  1,438
  (참고) 한 달 46,322줄 집계: 약 890 → 인덱스 경로 약 44,700 · 옵티마이저는 FULL 8,204를 고른다

문제

제안대로 다시 쌓으면 S1, S2, S3의 Buffers가 어떻게 바뀌는지 복사본의 측정값과 CLUSTERING_FACTOR로 설명하시오. 한 테이블에서 CF11_PK와 (ITM_NO) 인덱스의 CF가 함께 좋을 수 없는 이유도 설명하시오.
제안을 받아들일지 판단하고, S1을 빠르게 하는 대안을 SQL과 DDL로 작성하시오. 대안의 대가도 적으시오.
다시 쌓았을 때의 효과가 시간이 지나면 어떻게 되는지, POS가 판매를 쌓는 방식을 근거로 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 제안(CF11_NEW 재적재)은 받지 않는다 — S2 · S3 · 월마감이 쓰는 판매번호 순서를 잃는다
-- S1 이 쓰는 컬럼 넷을 다 담는다 → 테이블에 가지 않는다 (CF 를 고치지 않고 피한다)
CREATE INDEX IX_CF11_ITM_COVER ON CF11 (ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 S1 을 재고 → T2 → VISIBLE

SELECT SUBSTR(SLE_NO, 1, 6) AS ym, SUM(SLE_QTY) AS qty, SUM(SLE_QTY * SLE_UPRC) AS amt
  FROM CF11                                                  -- S1 은 그대로
 WHERE ITM_NO = :v_itm
 GROUP BY SUBSTR(SLE_NO, 1, 6)
 ORDER BY ym;

-- 기존 (ITM_NO) 는 새 인덱스의 접두부 · 다른 SQL 을 확인한 뒤 INVISIBLE → 관찰 → DROP (인덱스 수는 그대로)
ALTER INDEX IX_CF11_ITMNO INVISIBLE;

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF11) 그대로 · 재적재 뒤 숫자는 같은 데이터를 메뉴 순으로 다시 쌓았다고 보고 센 값

xplan

[S1 · 콜드브루 1010 · 커버링]
----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |      1 |        |     24 |00:00:00.01 |      71 |
|   1 |  SORT GROUP BY      |                   |      1 |     24 |     24 |00:00:00.01 |      71 |
|*  2 |   INDEX RANGE SCAN  | IX_CF11_ITM_COVER |      1 |  12463 |  15493 |00:00:00.01 |      71 |
----------------------------------------------------------------------------------------------------
   2 - access("ITM_NO"=:V_ITM)

[S2 · S3]  테이블 순서를 바꾸지 않으므로 그대로 4 · 32

모범 답안 서술

복사본으로 재면 S1은 7,053 → 144로 좋아진다. (ITM_NO) 인덱스의 CF가 387,422 → 약 8,200이 되어 콜드브루 15,493줄이 116블록에 모이기 때문이다. 하지만 같은 복사본에서 CF11_PK의 CF는 16,735 → 1,050,135로 무너진다. 테이블은 한 가지 순서로만 쌓을 수 있다. 판매번호 순으로 쌓여 있을 때는 영수증 한 장의 줄과 하루치 판매가 몇 블록에 모여 있었는데, 메뉴 순으로 쌓으면 그 줄들이 메뉴마다 다른 자리로 흩어진다. 그 결과는 다음과 같다. S2 영수증 상세는 한 장의 2.2줄이 평균 2.2블록에 흩어져 4 → 약 5블록이 된다. 하루 수천 번이라 그만큼 늘어난다. S3 일 판매상품 집계는 하루 1,498줄이 행마다 다른 블록이 되어 32 → 1,438로 45배가 된다. 한 달을 묶는 월마감은 인덱스 경로가 약 44,700블록이 되어 옵티마이저가 FULL 8,204를 고르게 되니, 지금의 약 890블록보다 9배 무겁다. MD 화면 하나를 위해 매장 화면과 마감을 모두 희생하는 셈이므로 제안은 받지 않는다. 효과도 오래 가지 않는다. 다시 쌓은 뒤에도 POS는 매일 약 1,500줄을 판매번호 순으로 테이블 끝에 덧붙인다. 그래서 새로 쌓이는 달은 메뉴 순이 아니라 지금처럼 흩어진다. MD가 주로 보는 최근 몇 달일수록 효과가 먼저 사라지고, 유지하려면 점검 시간마다 다시 쌓아야 한다. 대안은 CF를 고치지 않고 피하는 것이다. S1이 쓰는 ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC를 다 담은 (ITM_NO, SLE_NO, SLE_QTY, SLE_UPRC) 커버링 인덱스를 만들면 콜드브루 15,493항목(리프 약 69블록)만 읽고 테이블에 가지 않는다(7,053 → 71). 테이블 순서를 건드리지 않으므로 S2와 S3은 그대로다. 대가는 인덱스 크기다. 리프가 약 1,830 → 4,870블록으로 커지고, POS가 판매상품을 INSERT할 때마다 고치는 인덱스의 항목도 커진다. 기존 (ITM_NO) 인덱스는 새 인덱스의 접두부이므로 다른 SQL을 확인한 뒤 정리하면 인덱스 수는 늘지 않는다. 또 화면에 컬럼이 늘면 커버링이 깨지므로 전제하는 컬럼 목록을 T2 승인서에 적는다.

해설

함정: 교재 04화의 정렬 재적재가 효과를 낸 것은 테이블이 원래 그 순서(주문 시각)로 쌓이는 테이블이었기 때문이다. 쌓이는 순서와 다른 키로 다시 쌓으면 그 키 하나를 위해 원래 순서를 쓰던 모든 SQL이 나빠진다(ACC-028과 반대 경우).
CF는 인덱스마다 하나씩 있지만 그 바탕인 테이블의 순서는 하나다. 그래서 '어느 인덱스의 CF를 좋게 할까'는 '어느 SQL이 제일 중요한가'와 같은 질문이다. 이 테이블에서는 영수증과 마감이다.
ACC-012의 (ITM_NO, SLE_NO) 인덱스는 영수증 번호만 뽑는 SQL이라 커버링이었다. 이 화면은 수량과 단가가 필요해서 두 컬럼을 더 담는다. IDX-006의 (ITM_NO, SLE_QTY, SLE_UPRC)는 월별로 나누지 않아 SLE_NO가 필요 없었다.
2년 치를 매번 다시 묶는 대신, 지난달까지의 메뉴별 월 합계를 밤에 한 번 집계해 두는 요약 테이블도 대안이다. 그러면 이번 달분만 인덱스로 더하면 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 데이터(CF11) 그대로 · 재적재 뒤 숫자는 같은 데이터를 메뉴 순으로 다시 쌓았다고 보고 센 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF11) 그대로 · 재적재 뒤 숫자는 같은 데이터를 메뉴 순으로 다시 쌓았다고 보고 센 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황