대시보드의 「6월 구매 고객」이 21,947명인데 고객은 2,000명뿐이다

실전INSERT한빛유통360 / 700

상황

경영지원팀 대시보드의 「구매 고객」 칸은 어제까지의 주문 수, 주문 금액, 구매 고객 수(같은 고객은 한 번)를 일, 주, 월 추이로 보여 준다. 07시부터 19시까지 1분마다 새로 고쳐 하루 720번 돈다. 처음에는 화면이 T_ORD_BIG에서 바로 셌는데 너무 무거워서, 작년에 조민우가 일 요약 표 T_DASH_DAY를 만들었다. 06시 30분 배치가 전날 한 줄을 넣고, 화면은 그 표를 주와 달로 더한다. 어제 박팀장이 임원 회의에서 「6월 구매 고객 21,947명」이라고 보고했다가 "고객이 2천 명인데 무슨 숫자냐"는 질문을 받았다. 박팀장은 "주문 수와 금액은 맞다는데 고객 수는 왜 틀렸는지 밝히고, 고칠 안을 모두 모아 비용과 대가를 붙여 가져오라"고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 전부 자정 · IX_ORD_BIG_DT (ORD_DT) · 하루 약 912건을 고객 2,000명이 고르게 산다. 하루 구매 고객은 약 730명, 한 주는 약 1,920명, 한 달은 2,000명이다.
T_DASH_DAY 일 요약: BS_DT(PK) · ORD_CNT · ORD_AMT · CUS_CNT · 1,096행 · 8블록. 06:30 배치가 전날 한 줄을 넣는다.
화면은 일 추이(최근 30일), 주 추이(최근 12주, 월요일 시작), 월 추이(이번 달 포함 13개월) 세 SQL을 보낸다. 오늘 주문은 보여 주지 않는다.
통계는 최신이다.

원본 SQL

sql

-- [06:30 배치] 전날 한 줄
INSERT INTO T_DASH_DAY (BS_DT, ORD_CNT, ORD_AMT, CUS_CNT)
SELECT TRUNC(SYSDATE) - 1, COUNT(*), SUM(ORD_AMT), COUNT(DISTINCT CUS_ID)
  FROM T_ORD_BIG
 WHERE ORD_DT >= TRUNC(SYSDATE) - 1
   AND ORD_DT <  TRUNC(SYSDATE);
COMMIT;

-- [화면 · 월 추이] (주 추이는 TRUNC(BS_DT, 'IW'), 일 추이는 BS_DT 로 같은 모양)
SELECT TRUNC(BS_DT, 'MM') AS mon,
       SUM(ORD_CNT) AS ord_cnt, SUM(ORD_AMT) AS ord_amt, SUM(CUS_CNT) AS cus_cnt
  FROM T_DASH_DAY
 WHERE BS_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
 GROUP BY TRUNC(BS_DT, 'MM')
 ORDER BY mon;

실행계획 학습용 예시

xplan

[작년까지 · 화면이 원본에서 바로 세던 월 추이 (한 번)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |     13 |00:00:00.58 |    5826 |          |
|   1 |  SORT GROUP BY      |           |      1 |     13 |     13 |00:00:00.58 |    5826 | 9216K (0)|
|*  2 |   TABLE ACCESS FULL | T_ORD_BIG |      1 |    361K|    361K|00:00:00.06 |    5826 |          |
-------------------------------------------------------------------------------------------------------
  (일 · 주 · 월 세 SQL × 1분마다 하루 720번 = 하루 약 1,258만 블록)

[지금 · 06:30 배치 (하루 한 번)]  IX_ORD_BIG_DT 범위 4 + 테이블 6 · Buffers 10

[지금 · 화면 월 추이 (한 번)]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |     13 |00:00:00.01 |       3 |          |
|   1 |  SORT GROUP BY                        |               |      1 |     13 |     13 |00:00:00.01 |       3 | 2048 (0) |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_DASH_DAY    |      1 |    395 |    395 |00:00:00.01 |       3 |          |
|*  3 |    INDEX RANGE SCAN                   | PK_T_DASH_DAY |      1 |    395 |    395 |00:00:00.01 |       2 |          |
-------------------------------------------------------------------------------------------------------------------------

[결과 · 2024년 6월]
                       ORD_CNT   ORD_AMT      CUS_CNT
  T_DASH_DAY 를 더한 값  27,390   (원장과 같다)   21,947
  T_ORD_BIG 원장         27,390   (원장과 같다)    2,000   ← COUNT(DISTINCT CUS_ID)

문제

주문 수와 금액은 맞는데 구매 고객 수만 틀린 까닭을 설명하시오. 일 요약을 주와 달로 더하는 방식으로는 왜 고칠 수 없는지도 쓰시오.
정확한 구매 고객 수를 일, 주, 월로 보여 줄 수 있는 안을 셋 이상 들고, 화면 한 번과 하루의 일량, 정확성, 대가를 비교하시오.
하나를 골라 배치와 화면 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ② 기간 표 : 일 · 주 · 월 묶음마다 구매 고객을 원본에서 센 값을 둔다
CREATE TABLE T_DASH_PRD (
  PRD_TP  CHAR(1) NOT NULL,      -- D 일 · W 주(월요일 시작) · M 월
  PRD_DT  DATE    NOT NULL,      -- 그 기간의 첫날
  ORD_CNT NUMBER  NOT NULL,
  ORD_AMT NUMBER  NOT NULL,
  CUS_CNT NUMBER  NOT NULL,      -- 그 기간에 한 번이라도 산 고객 수 (더해서 만들지 않는다)
  BAS_DT  DATE    NOT NULL,
  CONSTRAINT PK_T_DASH_PRD PRIMARY KEY (PRD_TP, PRD_DT)
);

-- 06:30 배치 : 13개월을 한 번 읽고, 묶음 셋을 GROUPING SETS 로 만들어 묶음마다 COUNT(DISTINCT) 한다
DELETE FROM T_DASH_PRD;          -- 약 460줄 · DML 이라 화면은 커밋 전까지 어제 값을 본다 (TRUNCATE 는 그사이 빈 표를 보인다)
INSERT INTO T_DASH_PRD (PRD_TP, PRD_DT, ORD_CNT, ORD_AMT, CUS_CNT, BAS_DT)
SELECT CASE GROUPING_ID(d, w, m) WHEN 3 THEN 'D' WHEN 5 THEN 'W' ELSE 'M' END,   -- 일 3(011) · 주 5(101) · 월 6(110)
       COALESCE(d, w, m),
       COUNT(*), SUM(ORD_AMT), COUNT(DISTINCT CUS_ID), TRUNC(SYSDATE) - 1
  FROM (SELECT ORD_DT              AS d,
               TRUNC(ORD_DT, 'IW') AS w,
               TRUNC(ORD_DT, 'MM') AS m,
               CUS_ID, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)
           AND ORD_DT <  TRUNC(SYSDATE))
 GROUP BY GROUPING SETS ((d), (w), (m));
COMMIT;

-- 화면 : 더하지 않고 읽기만 한다
SELECT PRD_DT, ORD_CNT, ORD_AMT, CUS_CNT
  FROM T_DASH_PRD
 WHERE PRD_TP = :tp                -- 'D' · 'W' · 'M'
 ORDER BY PRD_DT;

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

xplan

[② 06:30 배치 · 하루 한 번]
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                           |                           |      1 |        |      0 |00:00:01.34 |   11094 |          |
|   1 |  LOAD TABLE CONVENTIONAL                   | T_DASH_PRD                |      1 |        |      0 |00:00:01.34 |   11094 |          |
|   2 |   TEMP TABLE TRANSFORMATION                |                           |      1 |        |    463 |00:00:01.33 |   11061 |          |
|   3 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6641_3A1B7C |      1 |        |      0 |00:00:00.31 |    5826 |          |
|*  4 |     TABLE ACCESS FULL                      | T_ORD_BIG                 |      1 |    361K|    361K|00:00:00.07 |    5826 |          |
|   5 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6642_3A1B7C |      1 |        |      0 |00:00:00.33 |    1744 |          |
|   6 |     SORT GROUP BY                          |                           |      1 |    395 |    395 |00:00:00.33 |    1744 | 9216K (0)|
|   7 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6641_3A1B7C |      1 |    361K|    361K|00:00:00.05 |    1744 |          |
|   8 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6642_3A1B7C |      1 |        |      0 |00:00:00.32 |    1744 |          |
|   9 |     SORT GROUP BY                          |                           |      1 |     57 |     55 |00:00:00.32 |    1744 | 9216K (0)|
|  10 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6641_3A1B7C |      1 |    361K|    361K|00:00:00.05 |    1744 |          |
|  11 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6642_3A1B7C |      1 |        |      0 |00:00:00.33 |    1744 |          |
|  12 |     SORT GROUP BY                          |                           |      1 |     13 |     13 |00:00:00.33 |    1744 | 9216K (0)|
|  13 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6641_3A1B7C |      1 |    361K|    361K|00:00:00.05 |    1744 |          |
|  14 |    VIEW                                    |                           |      1 |    465 |    463 |00:00:00.01 |       3 |          |
|  15 |     TABLE ACCESS FULL                      | SYS_TEMP_0FD9D6642_3A1B7C |      1 |    465 |    463 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------------------------------

[② 화면 · 한 번]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |     13 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_DASH_PRD    |      1 |     13 |     13 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN           | PK_T_DASH_PRD |      1 |     13 |     13 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

[대조 · 2024년 6월]  T_DASH_PRD 'M' 2024-06-01 : 27,390건 · 구매 고객 2,000명 = 원장 COUNT(DISTINCT CUS_ID) 2,000명

모범 답안 서술

구매 고객 수는 '그 기간에 한 번이라도 산 고객'의 수라서 기간마다 원본에서 다시 세야 한다. 일 요약의 CUS_CNT는 날마다 따로 센 값(하루 약 730명)이다. 같은 고객이 그달 여러 날 사면 날마다 한 번씩 세어진 값이 더해지므로, 6월 30일 치를 더한 21,947명은 '고객별 구매일 수의 합'에 가깝다. 주문 수와 금액은 줄을 더하는 집계라 날마다 더한 값을 다시 더해도 맞지만, 구매 고객 수는 더할 수 없는 집계다. 일 요약에는 '누가' 샀는지가 남아 있지 않으므로 그 표를 어떻게 더해도 고칠 수 없다. 안은 넷이다. ① 화면이 원본에서 바로 센다(작년 방식): 정확하다. 하지만 새로 고칠 때마다 세 SQL이 T_ORD_BIG을 통째로 읽어 17,478블록이고, 하루 720번이면 약 1,258만 블록이다. 36만 행 정렬도 1분마다 세 번 돈다. 일 요약을 만든 까닭이 이것이다. ② 배치가 기간마다 미리 센다: 06시 30분에 13개월을 한 번 읽어 GROUPING SETS ((일), (주), (월))로 묶음마다 COUNT(DISTINCT CUS_ID)를 원본에서 센다. 하루 한 번 5,826블록과 임시 테이블 읽기를 합쳐 약 1만 1천 블록이고, 화면은 기간 표에서 13 ~ 30줄을 읽어 3블록이다. 정확하지만 정해 둔 일, 주, 월 칸만 볼 수 있고 어제까지의 값이다. ③ (일, 고객) 단위 요약을 둔다: 하루에 산 고객마다 한 줄(하루 약 730줄, 13개월 약 29만 줄, 약 1,200블록)을 쌓고 화면이 기간마다 COUNT(DISTINCT CUS_ID)를 센다. 어떤 기간이든 정확히 셀 수 있지만, 월 추이 한 번에 29만 줄을 읽고 정렬해야 해서 1분마다 새로 고치기에는 무겁다(하루 약 260만 블록). 기간을 마음대로 고르는 분석 화면에 맞는 안이다(SORT-043의 고객 구매 달력 T_CUS_DAY가 이 모양이다). ④ 근사값을 쓴다: APPROX_COUNT_DISTINCT는 원본을 그대로 읽으므로 ①의 블록은 줄지 않고 정렬 메모리만 준다. 일별 스케치(APPROX_COUNT_DISTINCT_DETAIL)를 쌓아 두고 주와 달로 합치는 방식(APPROX_COUNT_DISTINCT_AGG)은 더할 수 있어 싸지만 1 ~ 2%의 오차가 있다. 임원 보고에 쓰는 숫자라 오차를 받아들이기 어렵다. ②를 고른다. 대시보드가 보여 주는 칸은 일, 주, 월로 정해져 있고 1분마다 읽히므로 읽는 쪽이 가장 싸야 한다. 묶음 번호는 GROUPING_ID(d, w, m)가 일 3, 주 5, 월 6이다. 배치는 기간 표를 지우고 다시 채우는데, TRUNCATE 대신 DELETE와 INSERT를 한 트랜잭션으로 해서 화면이 커밋 전까지 어제 값을 보게 한다(TRUNCATE는 DDL이라 그사이 화면이 빈다). 바꾸는 날에는 6월 한 달을 원장(T_ORD_BIG)에서 COUNT(DISTINCT CUS_ID)로 센 값(2,000명)과 대조하고, 주와 일도 몇 칸 골라 맞춰 본다. 주문 수와 금액은 기존 일 요약을 더한 값과도 같아야 한다.

해설

주의 경계: 주는 월요일에 시작하는 ISO 주('IW')로 센다. 13개월 앞의 첫 주는 앞부분이 잘려 있지만 화면은 최근 12주만 보므로 상관없다. 회사가 주를 다르게 센다면 업무 달력의 주 번호를 쓴다.
오늘 주문을 붙이고 싶다면: ②는 어제까지의 값이다. 이번 달 구매 고객 수는 '어제까지 + 오늘'로 더할 수 없으므로, 오늘을 넣는 칸은 그 칸의 기간 전체를 원본에서 따로 세야 한다. 같은 함정이 다시 생기는 자리다.
지난 주문의 정정: 매일 13개월을 다시 세므로 지난 주문의 금액 정정이나 취소도 다음 날 아침에 반영된다. 일 요약에 한 줄씩 쌓는 방식은 지난날을 고치지 않는다.
ROLLUP으로 될까: 일과 월은 계층이라 ROLLUP (m, d)로 한 번에 묶을 수 있지만, 주는 달을 넘나들어 계층이 아니다. 세 묶음이 다 필요하면 GROUPING SETS다(SORT-045, SORT-048).
숫자를 바로잡는 일이므로 임원 보고에 쓰인 지난 숫자(21,947명)를 고치는 공지를 함께 낸다. 같은 화면의 주 추이도 같은 이유로 틀려 있었다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황