날짜는 30개뿐인데 그날 매출 합계를 4,110번 다시 구한다

중급SELECT한빛유통242 / 700

상황

정산팀의 「주문별 일 매출 비중」 리포트는 지난달 주문마다 그날 전체 매출에서 차지하는 비중(%)을 보여 준다. 월초 일주일 동안 담당자 다섯 명이 하루 수십 번 돌리는데, 한 번에 1.6초 걸린다. SQL을 짠 조민우는 "서브쿼리 안에서 TRUNC로 날짜만 남겼으니, 캐시가 날짜 30개만 계산하고 나머지는 꺼내 쓸 줄 알았다"라고 했다. 아래는 2024년 6월 치다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음) · 2023-01-01 ~ 2024-12-30, 하루 137건 · 2024년 6월 4,110건
T_ORD는 ORD_SEQ 순서로 쌓여 있고, 같은 날의 주문은 730행 간격으로 테이블 전체에 흩어져 있다
인덱스: PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT) · 통계 최신

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, o.ORD_AMT,
       ROUND(o.ORD_AMT * 100 /
             (SELECT SUM(x.ORD_AMT)
                FROM T_ORD x
               WHERE x.ORD_DT >= TRUNC(o.ORD_DT)
                 AND x.ORD_DT <  TRUNC(o.ORD_DT) + 1), 2) AS day_pct
  FROM T_ORD o
 WHERE o.ORD_DT >= DATE '2024-06-01'
   AND o.ORD_DT <  DATE '2024-07-01'
 ORDER BY o.ORD_DT

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |   4110 |00:00:01.58 |     572K|
|   1 |  SORT AGGREGATE                       |             |   4110 |      1 |   4110 |00:00:01.52 |     571K|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD       |   4110 |    137 |    563K|00:00:01.31 |     571K|
|*  3 |    INDEX RANGE SCAN                   | IX_T_ORD_DT |   4110 |    137 |    563K|00:00:00.24 |    8220 |
|   4 |  SORT ORDER BY                        |             |      1 |   4110 |   4110 |00:00:01.58 |     572K|
|*  5 |   TABLE ACCESS FULL                   | T_ORD       |      1 |   4110 |   4110 |00:00:00.01 |     896 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("X"."ORD_DT">=TRUNC(:B1) AND "X"."ORD_DT"<TRUNC(:B1)+1)
   5 - filter(("O"."ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

(DBMS_XPLAN 이 줄여 쓴 값 — 563K = 563,070 · 571K = 571,290 · 572K = 572,186)

문제

1번 줄의 Starts 4,110이 무엇의 개수인지 쓰고, 날짜가 30개뿐인데도 캐시가 한 번도 듣지 않은 이유를 Predicate Information으로 설명하시오.
결과가 바뀌지 않게 SQL을 고치시오. 고친 방법이 목록에 조건이 더 붙을 때도 같은 답을 내는지 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 그날 합계를 행마다 다시 구하지 않는다 : 6월 주문을 한 번 읽으며 날짜별 합계를 옆에 붙인다
SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT,
       ROUND(ORD_AMT * 100 / SUM(ORD_AMT) OVER (PARTITION BY TRUNC(ORD_DT)), 2) AS day_pct
  FROM T_ORD
 WHERE ORD_DT >= DATE '2024-06-01'
   AND ORD_DT <  DATE '2024-07-01'
 ORDER BY ORD_DT;

-- 목록에만 조건이 붙는 요구라면 분모를 따로 만든다 (T_ORD 를 두 번 읽는다 · 1,792 블록)
-- SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, o.ORD_AMT, ROUND(o.ORD_AMT * 100 / d.day_amt, 2) AS day_pct
--   FROM T_ORD o,
--        (SELECT TRUNC(ORD_DT) AS dd, SUM(ORD_AMT) AS day_amt
--           FROM T_ORD
--          WHERE ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01'
--          GROUP BY TRUNC(ORD_DT)) d
--  WHERE o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'
--    AND o.ORD_ST = 'COMPLETE'
--    AND d.dd = TRUNC(o.ORD_DT)
--  ORDER BY o.ORD_DT;

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |   4110 |00:00:00.03 |     896 |          |
|   1 |  SORT ORDER BY      |       |      1 |   4110 |   4110 |00:00:00.03 |     896 |  337K (0)|
|   2 |   WINDOW SORT       |       |      1 |   4110 |   4110 |00:00:00.02 |     896 |  337K (0)|
|*  3 |    TABLE ACCESS FULL| T_ORD |      1 |   4110 |   4110 |00:00:00.01 |     896 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

1번 줄의 Starts 4,110은 6월 주문 수다. 주문 한 건마다 스칼라 서브쿼리가 한 번씩 돌면서 3번 줄의 IX_T_ORD_DT로 그날 주문 137건을 찾고, 2번 줄에서 그 137건을 테이블에서 하나씩 읽었다(4,110 × 137 = 563,070행, 571K 블록). 같은 날의 주문은 730행 간격으로 흩어져 있어서 한 건에 한 블록씩 든다. 캐시가 듣지 않은 이유는 3번 줄 Predicate의 TRUNC(:B1)에 있다. 캐시가 기억하는 열쇠는 서브쿼리에 들어가는 :B1, 즉 바깥의 o.ORD_DT이고, TRUNC는 서브쿼리 안에서 계산된다. T_ORD의 주문 일시는 시분초가 있어 4,110개가 모두 다르므로, 결과(날짜 30개)는 겹쳐도 같은 열쇠가 다시 들어온 적이 없다. 같은 테이블을 한 번만 읽으면서 날짜별 합계를 붙이는 분석 함수 SUM(ORD_AMT) OVER (PARTITION BY TRUNC(ORD_DT))로 고친다. 6월 범위는 하루 단위로 끊기므로 날짜별 합계는 원래 SQL과 같다. Buffers는 572K(572,186) → 896, A-Time은 1.58 → 0.03초로 준다. 목록에 조건이 더 붙으면(예: ORD_ST = 'COMPLETE'만 보여 줄 때) 분석 함수는 남은 행만 더하므로 분모가 달라진다. 그때는 조건 없는 날짜별 집계(GROUP BY TRUNC(ORD_DT))를 따로 만들어 조인한다. T_ORD를 두 번 읽어 896 × 2 = 1,792블록이다.

해설

함정: 상관 서브쿼리 안에서 바깥 컬럼을 함수로 감싸도 캐시의 열쇠는 감싸기 전의 컬럼이다(교재 07화: MOD(:B1, 8)로 지역 8곳을 찾은 실험에서 Starts 1,000,000). Predicate에서 :B1이 무엇을 감싸고 있는지 본다.
스칼라 서브쿼리를 남기는 답: TRUNC(ORD_DT)를 인라인 뷰에서 미리 만들고 그 컬럼을 서브쿼리에 넣으면 입력이 30종류가 되어 Starts가 30 안팎으로 준다(약 5,000블록). 다만 뷰가 병합되면 입력이 다시 주문 일시로 돌아갈 수 있으니 NO_MERGE를 주고 Predicate의 :B1을 확인해야 한다.
날짜별 집계와 조인하는 답: 목록과 분모를 따로 만들 수 있어 요구가 바뀌어도 안전하다. 대신 T_ORD를 두 번 읽는다(1,792블록).
분석 함수의 WINDOW SORT는 6월 4,110행만 정렬하므로 메모리 부담이 작다. 대상이 수백만 행이면 Used-Mem과 Used-Tmp를 함께 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황