매장 200줄이어야 할 「매장별 월 주문 건수」 표가 50만 줄로 내려와 엑셀이 멈춘다

기초SELECT버거368 / 700

상황

버거 본사 재무팀이 「2013년 매장별 월 주문 건수」(매장마다 한 줄, 1월 ~ 12월 열두 칸)를 요청했다. 조민우가 PIVOT으로 만들어 넘겼는데, 내려받기가 6초 넘게 걸리고 TEMP를 100MB 넘게 쓴다. 엑셀에는 50만 줄이 들어왔고, 줄마다 열두 칸 가운데 한 칸만 1이고 나머지는 0이다. 조민우는 "PIVOT은 FOR 뒤의 월로 칸을 나누고 나머지는 알아서 묶어 주니까 매장별로 묶일 줄 알았다"라고 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO(PK) · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO 200곳 · 문자열 채움 컬럼(행 약 195바이트)
2013년 주문은 500,212행(테이블의 절반)이다
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 통계 최신
내려받기는 낮에 돌고, 그 시간 work area 하나의 상한은 약 20MB다

원본 SQL

sql

SELECT *
  FROM (SELECT o.*, SUBSTR(o.ORD_DT, 5, 2) AS MM
          FROM ORD o
         WHERE o.ORD_DT BETWEEN '20130101' AND '20131231')
 PIVOT (COUNT(*) FOR MM IN ('01' AS M01, '02' AS M02, '03' AS M03, '04' AS M04,
                           '05' AS M05, '06' AS M06, '07' AS M07, '08' AS M08,
                           '09' AS M09, '10' AS M10, '11' AS M11, '12' AS M12))
 ORDER BY SHOP_NO

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |    500K|00:00:06.37 |   22422 |       |          |         |
|   1 |  SORT ORDER BY        |      |      1 |    500K|    500K|00:00:06.37 |   22422 |   118M|   21M (1)|     104M|
|   2 |   HASH GROUP BY PIVOT |      |      1 |    500K|    500K|00:00:04.12 |   22422 |   131M|   21M (1)|     112M|
|*  3 |    TABLE ACCESS FULL  | ORD  |      1 |    500K|    500K|00:00:00.21 |   22422 |       |          |         |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

[엑셀에 들어온 결과 · 앞의 세 줄]
  ORD_NO   ORD_DT    ORD_HMS  SHOP_NO  (채움 컬럼 …)  M01 M02 M03 … M12
  500213   20130101  000012   SH0131                   1   0   0  …  0
  500214   20130101  000107   SH0007                   1   0   0  …  0

문제

매장 200줄을 기대한 표가 500,212줄로 나온 까닭을 PIVOT이 묶는 기준으로 설명하시오.
매장 한 줄에 열두 달이 나오도록 SQL을 고치고, 고친 뒤 실행계획에서 무엇이 줄고 무엇이 그대로인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- PIVOT 에 넣는 인라인 뷰에는 표에 필요한 컬럼(매장, 월)만 → 묶는 기준이 SHOP_NO 하나가 된다
SELECT *
  FROM (SELECT o.SHOP_NO, SUBSTR(o.ORD_DT, 5, 2) AS MM
          FROM ORD o
         WHERE o.ORD_DT BETWEEN '20130101' AND '20131231')
 PIVOT (COUNT(*) FOR MM IN ('01' AS M01, '02' AS M02, '03' AS M03, '04' AS M04,
                           '05' AS M05, '06' AS M06, '07' AS M07, '08' AS M08,
                           '09' AS M09, '10' AS M10, '11' AS M11, '12' AS M12))
 ORDER BY SHOP_NO;

-- 같은 표 · 묶는 기준을 GROUP BY 에 드러내는 CASE 판
-- SELECT SHOP_NO,
--        COUNT(CASE WHEN SUBSTR(ORD_DT, 5, 2) = '01' THEN 1 END) AS M01,
--        …
--        COUNT(CASE WHEN SUBSTR(ORD_DT, 5, 2) = '12' THEN 1 END) AS M12
--   FROM ORD
--  WHERE ORD_DT BETWEEN '20130101' AND '20131231'
--  GROUP BY SHOP_NO
--  ORDER BY SHOP_NO;

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

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |    200 |00:00:00.31 |   22422 |       |          |         |
|   1 |  SORT ORDER BY        |      |      1 |    200 |    200 |00:00:00.31 |   22422 | 20480 |18432  (0)|         |
|   2 |   HASH GROUP BY PIVOT |      |      1 |    200 |    200 |00:00:00.31 |   22422 |  1270K| 1134K (0)|         |
|*  3 |    TABLE ACCESS FULL  | ORD  |      1 |    500K|    500K|00:00:00.12 |   22422 |       |          |         |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

모범 답안 서술

PIVOT은 집계 함수(COUNT(*))와 FOR 절(MM)에 쓴 컬럼을 뺀 나머지 컬럼 전부로 묶는다. GROUP BY를 따로 적지 않을 뿐, 묶는 기준은 PIVOT에 들어오는 컬럼이 정한다. 인라인 뷰가 o.*로 주문의 모든 컬럼을 내보냈으므로 묶는 기준이 SHOP_NO가 아니라 ORD_NO, ORD_DT, ORD_HMS, SHOP_NO와 채움 컬럼 전부가 되었다. ORD_NO가 주문마다 유일하니 묶음도 주문마다 하나, 곧 500,212줄이고 줄마다 그 주문의 달 칸만 1이다. 2번 줄 HASH GROUP BY PIVOT의 A-Rows가 500K인 것이 그 증거다. 묶는 행이 약 195바이트짜리 50만 줄이라 2번 줄에 131M가 필요했고, 상한 20MB에서 one-pass(21M (1))로 돌며 112M를 TEMP에 썼다. 1번 줄 SORT ORDER BY도 그 50만 줄을 다시 정렬하며 104M를 TEMP에 썼다. 6.4초의 대부분이 이 둘이다. 인라인 뷰에서 매장과 월, 두 컬럼만 내보내면 묶는 기준이 SHOP_NO 하나가 된다. 2번 줄은 500,212 → 200줄로 묶고 작업 공간은 1M 남짓이라 TEMP가 사라진다(6.37초 → 0.31초). Buffers는 22,422 그대로다. 2013년이 테이블의 절반이라 FULL로 읽는 것은 같고, 줄어든 것은 묶고 정렬하는 일이다. 같은 표를 GROUP BY SHOP_NO와 COUNT(CASE WHEN … THEN 1 END) 열두 개로 써도 된다. CASE로 쓰면 묶는 기준이 GROUP BY에 드러나서 이런 실수가 생길 자리가 없다. PIVOT을 쓸 때는 인라인 뷰에 표에 필요한 컬럼만 남기는 것을 습관으로 한다.

해설

엑셀에서 다시 묶으면 되지 않나: 50만 줄을 내려받아 엑셀 피벗으로 묶으면 표는 맞지만, DB는 여전히 50만 줄을 묶고 정렬해 보내고 네트워크로 그만큼 옮긴다. 화면이 쓰는 모양으로 DB에서 묶어 보내는 편이 양쪽 다 가볍다(교재 09화 §11).
월은 SELECT 절에서 SUBSTR(o.ORD_DT, 5, 2)로 만들고, WHERE 절은 ORD_DT BETWEEN '20130101' AND '20131231'처럼 가공하지 않았다. 범위가 한 달이었다면 ORD_X01의 범위 스캔도 쓸 수 있는 모양이다.
1년의 절반을 읽는 표라 인덱스로 줄일 거리는 없다. 같은 표를 하루에도 여러 번 연다면 매장별 월 건수를 미리 묶어 두는 방법(마감 집계 표나 구체화 뷰)을 생각한다(SORT-066, SORT-067).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황