월말 마감 주간마다 경영 리포트 화면의 SQL 셋이 논리 읽기 1 ~ 3위를 차지한다

실전SELECT한빛유통302 / 700

상황

경영지원팀의 「월 주문 리포트」 화면은 탭 세 개(R1 등급과 상태별, R2 일별 추이와 합계, R3 상태별 전월 대비)를 한꺼번에 띄운다. 평소에는 하루 10번 남짓 열지만 월말 마감 주간에는 팀장들이 하루 150번쯤 열고, 그 주에는 이 화면의 SQL 셋이 AWR의 SQL ordered by Gets 1 ~ 3위를 차지한다. 박팀장은 "세 SQL을 각각 고치고, 인덱스를 만들지 요약 테이블을 만들지도 정해 달라"라고 했다. 아래는 2024년 6월 리포트를 열었을 때의 실행계획이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 이 문제에서는 인덱스가 없다 · ORD_DT DATE(전부 자정) · ORD_ST는 COMPLETE · PENDING · CANCEL 세 가지
2024년 6월 27,390건 · 5월 28,301건 · 주문은 하루 약 900건 들어오고, 화면에서 한 건씩 넣는다
M_CUS: 2,000행 · 30블록 · CUS_GD A · B · C
통계 최신 · 화면은 그달 1일과 다음 달 1일(:d1, :d2), 전달 1일(:d0)을 DATE로 넘긴다

원본 SQL

sql

-- R1 등급 · 상태별
SELECT c.CUS_GD, 'COMPLETE' AS ORD_ST, COUNT(*) AS CNT, SUM(o.ORD_AMT) AS AMT
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID AND o.ORD_DT >= :d1 AND o.ORD_DT < :d2 AND o.ORD_ST = 'COMPLETE'
 GROUP BY c.CUS_GD
UNION ALL
SELECT c.CUS_GD, 'PENDING', COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID AND o.ORD_DT >= :d1 AND o.ORD_DT < :d2 AND o.ORD_ST = 'PENDING'
 GROUP BY c.CUS_GD
UNION ALL
SELECT c.CUS_GD, 'CANCEL', COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID AND o.ORD_DT >= :d1 AND o.ORD_DT < :d2 AND o.ORD_ST = 'CANCEL'
 GROUP BY c.CUS_GD;

-- R2 일별 추이와 합계 줄
SELECT TO_CHAR(ORD_DT, 'YYYY-MM-DD') AS DT, COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1 AND ORD_DT < :d2
 GROUP BY ORD_DT
UNION ALL
SELECT '합계', COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1 AND ORD_DT < :d2
 ORDER BY 1;

-- R3 상태별 전월 대비
SELECT 'THIS' AS MON, ORD_ST, COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1 AND ORD_DT < :d2
 GROUP BY ORD_ST
UNION
SELECT 'PREV', ORD_ST, COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d0 AND ORD_DT < :d1
 GROUP BY ORD_ST;

실행계획 학습용 예시

xplan

[R1]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      9 | 00:00:00.33 |   17568 |
|   1 |  UNION-ALL             |           |      1 |        |      9 | 00:00:00.33 |   17568 |
|   2 |   HASH GROUP BY        |           |      1 |      3 |      3 | 00:00:00.12 |    5856 |
|*  3 |    HASH JOIN           |           |      1 |  24539 |  24651 | 00:00:00.11 |    5856 |
|   4 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 | 00:00:00.01 |      30 |
|*  5 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |  24539 |  24651 | 00:00:00.10 |    5826 |
|   6 |   HASH GROUP BY        |           |      1 |      3 |      3 | 00:00:00.11 |    5856 |
|*  7 |    HASH JOIN           |           |      1 |   2181 |   2191 | 00:00:00.10 |    5856 |
|   8 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 | 00:00:00.01 |      30 |
|*  9 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |   2181 |   2191 | 00:00:00.10 |    5826 |
|  10 |   HASH GROUP BY        |           |      1 |      3 |      3 | 00:00:00.10 |    5856 |
|* 11 |    HASH JOIN           |           |      1 |    545 |    548 | 00:00:00.10 |    5856 |
|  12 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 | 00:00:00.01 |      30 |
|* 13 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |    545 |    548 | 00:00:00.10 |    5826 |
-----------------------------------------------------------------------------------------------
   3 - access("C"."CUS_ID"="O"."CUS_ID")
   5 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."ORD_DT">=:D1 AND "O"."ORD_DT"<:D2))
   (9번, 13번 줄은 상태만 PENDING, CANCEL)

[R2]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |     31 | 00:00:00.21 |   11652 |
|   1 |  SORT ORDER BY         |           |      1 |     31 |     31 | 00:00:00.21 |   11652 |
|   2 |   UNION-ALL            |           |      1 |        |     31 | 00:00:00.21 |   11652 |
|   3 |    HASH GROUP BY       |           |      1 |     30 |     30 | 00:00:00.11 |    5826 |
|*  4 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.10 |    5826 |
|   5 |    SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.10 |    5826 |
|*  6 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.10 |    5826 |
-----------------------------------------------------------------------------------------------
   4 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2))
   6 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2))

[R3]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      6 | 00:00:00.21 |   11652 |
|   1 |  SORT UNIQUE           |           |      1 |      6 |      6 | 00:00:00.21 |   11652 |
|   2 |   UNION-ALL            |           |      1 |        |      6 | 00:00:00.21 |   11652 |
|   3 |    HASH GROUP BY       |           |      1 |      3 |      3 | 00:00:00.11 |    5826 |
|*  4 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.10 |    5826 |
|   5 |    HASH GROUP BY       |           |      1 |      3 |      3 | 00:00:00.10 |    5826 |
|*  6 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |  28190 |  28301 | 00:00:00.10 |    5826 |
-----------------------------------------------------------------------------------------------
   4 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2))
   6 - filter(("ORD_DT">=:D0 AND "ORD_DT"<:D1))

[화면 한 번]  17,568 + 11,652 + 11,652 = 40,872블록 · 월말 주간 하루 150번이면 약 613만 블록

문제

화면 한 번에 읽는 블록이 어디서 나오는지 SQL마다 줄 번호와 Buffers로 설명하시오.
세 SQL을 각각 결과가 바뀌지 않게 T_ORD_BIG을 한 번만 읽도록 고치시오. R2의 합계 줄과 R3의 UNION을 어떻게 처리했는지 근거를 쓰시오.
고친 SQL에 더해 인덱스를 만드는 안과 일 단위 요약 테이블을 만드는 안을 일량과 부작용으로 비교하고 하나를 고르시오.

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

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

모범답안

모범 답안 SQL

sql

-- R1 : 상태는 갈래가 아니라 값이다. 원래 보이던 세 상태만 보이도록 IN 을 남긴다
SELECT c.CUS_GD, o.ORD_ST, COUNT(*) AS CNT, SUM(o.ORD_AMT) AS AMT
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
   AND o.ORD_DT >= :d1 AND o.ORD_DT < :d2
   AND o.ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL')
 GROUP BY c.CUS_GD, o.ORD_ST;

-- R2 : 일별 행과 합계 줄을 한 번에 (합계 줄의 날짜 칸은 GROUPING 으로 가려 '합계')
SELECT CASE WHEN GROUPING(ORD_DT) = 1 THEN '합계' ELSE TO_CHAR(ORD_DT, 'YYYY-MM-DD') END AS DT,
       COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1 AND ORD_DT < :d2
 GROUP BY ROLLUP(ORD_DT)
 ORDER BY 1;

-- R3 : 붙어 있는 두 달을 한 범위로 읽고 달로 나눠 묶는다 (UNION 의 정렬에 기대던 순서는 ORDER BY 로)
SELECT CASE WHEN TRUNC(ORD_DT, 'MM') = :d1 THEN 'THIS' ELSE 'PREV' END AS MON,
       ORD_ST, COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d0 AND ORD_DT < :d2
 GROUP BY TRUNC(ORD_DT, 'MM'), ORD_ST
 ORDER BY 1, 2;

-- 월말 주간 대책 : 세 SQL 이 쓰는 네 컬럼만 담은 인덱스 (테이블에 가지 않는다)
CREATE INDEX IX_ORD_BIG_DT_ST_CUS_AMT ON T_ORD_BIG (ORD_DT, ORD_ST, CUS_ID, ORD_AMT);

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

xplan

[R1 고친 뒤]
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |      9 | 00:00:00.12 |    5856 |
|   1 |  HASH GROUP BY        |           |      1 |      9 |      9 | 00:00:00.12 |    5856 |
|*  2 |   HASH JOIN           |           |      1 |  27266 |  27390 | 00:00:00.11 |    5856 |
|   3 |    TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 | 00:00:00.01 |      30 |
|*  4 |    TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.10 |    5826 |
----------------------------------------------------------------------------------------------

[R2 고친 뒤]
------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |     31 | 00:00:00.11 |    5826 |
|   1 |  SORT ORDER BY          |           |      1 |     31 |     31 | 00:00:00.11 |    5826 |
|   2 |   SORT GROUP BY ROLLUP  |           |      1 |     31 |     31 | 00:00:00.11 |    5826 |
|*  3 |    TABLE ACCESS FULL    | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.10 |    5826 |
------------------------------------------------------------------------------------------------

[R3 고친 뒤]
---------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      6 | 00:00:00.12 |    5826 |
|   1 |  SORT GROUP BY       |           |      1 |      6 |      6 | 00:00:00.12 |    5826 |
|*  2 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |  55456 |  55691 | 00:00:00.10 |    5826 |
---------------------------------------------------------------------------------------------

[화면 한 번]  40,872 → 17,508블록 (T_ORD_BIG FULL 7번 → 3번)
[+ 인덱스]    R1 약 190 · R2 약 160 · R3 약 310 → 약 660블록 (월말 주간 하루: 약 613만 → 약 10만 블록)
[요약 테이블 안]  일자 × 등급 × 상태 한 달 270행 · 세 SQL 합계 약 15블록 · 오늘 주문은 원장에서 따로 더해야 한다

모범 답안 서술

R1은 상태마다 갈래를 하나씩 두어 5번, 9번, 13번 줄이 같은 6월을 FULL로 세 번 읽는다(3 × 5,856 = 17,568). R2는 합계 줄을 구하려고 4번과 6번 줄에서 같은 6월을 두 번(11,652), R3는 이번 달과 지난달을 갈래로 나눠 두 번(11,652) 읽는다. 화면 한 번에 T_ORD_BIG을 일곱 번 통째로 읽어 40,872블록이고, 월말 주간 하루 150번이면 약 613만 블록이다. R1은 갈래를 가르던 상태를 값으로 보고 GROUP BY c.CUS_GD, o.ORD_ST로 한 번에 묶는다. 원래 SQL은 세 상태만 보였으므로 ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL')을 남겨 나중에 상태가 늘어도 결과가 같게 한다. 그달 어떤 등급의 취소가 0건이면 원래도 그 행이 없었으므로 결과가 같다. R2의 합계 줄은 일별 행의 합이므로 GROUP BY ROLLUP(ORD_DT)로 같은 읽기에서 만든다. 합계 줄의 날짜 칸은 NULL로 나오므로 GROUPING(ORD_DT) = 1일 때 '합계'를 넣고, ORDER BY 1로 원래처럼 맨 뒤에 둔다. 번호 두 개와 카티션 곱으로 복제해 만들어도 된다. R3의 UNION은 없앨 중복이 없다. 'THIS' 행과 'PREV' 행은 첫 칸이 달라 같아질 수 없고, 한 갈래 안에서는 상태마다 한 행이다. 그런데도 UNION이라 1번 줄 SORT UNIQUE로 정렬했다. 두 달은 붙어 있으므로 지난달 1일부터 다음 달 1일 전까지를 한 번 읽고 TRUNC(ORD_DT, 'MM')로 달을 나눠 묶는다. UNION의 정렬이 사라지면 행 순서도 보장되지 않으므로 화면이 기대던 순서는 ORDER BY로 적는다. 세 SQL을 고치면 화면 한 번이 40,872 → 17,508블록이 된다. 여기에 세 SQL이 쓰는 네 컬럼만 담은 (ORD_DT, ORD_ST, CUS_ID, ORD_AMT) 인덱스를 만들면 테이블에 가지 않고 그달(R3는 두 달) 범위의 인덱스만 읽어 R1 약 190, R2 약 160, R3 약 310, 합계 약 660블록이 된다. 대가는 100만 행 테이블에 인덱스(리프 약 5,500블록)가 하나 늘어 주문이 들어올 때마다 인덱스도 고친다는 것이다. 하루 900건이라 적재 부담은 작다. 일 단위 요약 테이블(일자 × 등급 × 상태, 한 달 270행)을 밤마다 채우면 세 SQL이 약 15블록으로 끝난다. 하지만 오늘 주문은 요약에 없어서 원장에서 따로 읽어 UNION ALL로 더해야 하고, 주문이 정정되면 요약도 다시 만들어야 하며, 요약과 원장이 어긋나지 않았는지 대조하는 절차가 생긴다. 이 화면은 월말 주간에만 무겁고, 고친 SQL과 인덱스로 하루 약 10만 블록이면 충분하다. 그래서 SQL 셋을 고치고 인덱스 하나를 만드는 안을 고른다.

해설

R1에서 IN을 빼면: 지금은 상태가 셋뿐이라 결과가 같다. 하지만 '반품' 같은 상태가 생기면 원래 SQL에는 없던 행이 나온다. 갈래를 값으로 바꿀 때는 갈래가 고르던 값의 목록을 조건으로 남긴다(SUBQ-059의 권역 밖 매장과 같은 함정).
R3를 UNION ALL로만 바꾸는 안: 정렬은 사라지지만 테이블은 여전히 두 번 읽는다. 갈래가 같은 테이블의 붙어 있는 범위라면 한 범위로 읽고 묶는 것이 낫다.
요약 테이블이 맞는 경우: 같은 집계를 쓰는 화면이 계속 늘거나 원장이 커져 인덱스 범위도 무거워지면 그때 요약 테이블(또는 구체화 뷰)을 검토한다. 결과를 미리 계산해 두는 일은 그 답이 언제 바뀌는지(오늘 주문, 정정)를 먼저 정해야 한다(교재 15b화).
고친 세 SQL은 원래 SQL과 행 수, 건수 합계, 금액 합계를 대조한다. R2의 합계 줄은 일별 행의 합과도 같아야 한다(교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황