유형 소계와 전체 합계 줄을 붙인 뒤로 「월간 메뉴 판매표」가 주문상품을 세 번 읽는다

기초SELECT버거350 / 700

상황

본사 메뉴팀의 「월간 메뉴 판매표」는 한 달 치 메뉴별 판매 수량과 판매 줄 수를 메뉴 유형별로 늘어놓는다. 지난달 메뉴팀이 "유형마다 소계 줄을, 맨 아래에는 전체 합계 줄을 붙여 달라"고 해서, 개발자가 같은 집계를 묶는 기준만 바꿔 세 번 쓰고 UNION ALL로 이었다. 월초 사흘 동안 메뉴팀과 지역 담당이 하루 300번쯤 여는데, 그 뒤로 화면이 0.1초에서 0.4초로 느려졌다. 아래는 2013년 12월로 연 실행계획이다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 주문일 순으로 쌓여 있다. 2013년 12월은 104,867줄(테이블 2,345블록, 인덱스 리프 약 420블록)이다.
ITEM 상품: 92행 · 5블록 · PK (ITEM_ID) · ITEM_TYPE_CD(메뉴 유형 5종)와 ITEM_NM은 NOT NULL이다. 12월에는 92개 메뉴가 모두 팔렸다.
ORD_ITEM.ITEM_ID에는 ITEM을 가리키는 FK가 있다. 통계는 최신이다.

원본 SQL

sql

SELECT ITEM_TYPE_CD, ITEM_ID, ITEM_NM, qty, lines
  FROM (SELECT t.ITEM_TYPE_CD, t.ITEM_ID, t.ITEM_NM, 1 AS lvl,
               SUM(i.ORD_ITEM_QTY) AS qty, COUNT(*) AS lines
          FROM ORD_ITEM i, ITEM t
         WHERE i.ORD_DT BETWEEN :d_from AND :d_to
           AND t.ITEM_ID = i.ITEM_ID
         GROUP BY t.ITEM_TYPE_CD, t.ITEM_ID, t.ITEM_NM
        UNION ALL
        SELECT t.ITEM_TYPE_CD, NULL, '유형 소계', 2,
               SUM(i.ORD_ITEM_QTY), COUNT(*)
          FROM ORD_ITEM i, ITEM t
         WHERE i.ORD_DT BETWEEN :d_from AND :d_to
           AND t.ITEM_ID = i.ITEM_ID
         GROUP BY t.ITEM_TYPE_CD
        UNION ALL
        SELECT NULL, NULL, '전체 합계', 3,
               SUM(i.ORD_ITEM_QTY), COUNT(*)
          FROM ORD_ITEM i
         WHERE i.ORD_DT BETWEEN :d_from AND :d_to)
 ORDER BY ITEM_TYPE_CD NULLS LAST, lvl, qty DESC

실행계획 학습용 예시

xplan

[2013년 12월]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |              |      1 |        |     98 |00:00:00.41 |    8320 |          |
|   1 |  SORT ORDER BY                            |              |      1 |     98 |     98 |00:00:00.41 |    8320 | 2048 (0) |
|   2 |   VIEW                                    |              |      1 |     98 |     98 |00:00:00.41 |    8320 |          |
|   3 |    UNION-ALL                              |              |      1 |        |     98 |00:00:00.41 |    8320 |          |
|   4 |     HASH GROUP BY                         |              |      1 |     92 |     92 |00:00:00.15 |    2775 | 1124K (0)|
|*  5 |      FILTER                               |              |      1 |        |   104K |00:00:00.11 |    2775 |          |
|*  6 |       HASH JOIN                           |              |      1 |   104K |   104K |00:00:00.10 |    2775 | 1236K (0)|
|   7 |        TABLE ACCESS FULL                  | ITEM         |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   104K |   104K |00:00:00.06 |    2770 |          |
|*  9 |         INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.02 |     425 |          |
|  10 |     HASH GROUP BY                         |              |      1 |      5 |      5 |00:00:00.14 |    2775 | 1018K (0)|
|* 11 |      FILTER                               |              |      1 |        |   104K |00:00:00.10 |    2775 |          |
|* 12 |       HASH JOIN                           |              |      1 |   104K |   104K |00:00:00.09 |    2775 | 1236K (0)|
|  13 |        TABLE ACCESS FULL                  | ITEM         |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|  14 |        TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   104K |   104K |00:00:00.05 |    2770 |          |
|* 15 |         INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.02 |     425 |          |
|  16 |     SORT AGGREGATE                        |              |      1 |      1 |      1 |00:00:00.12 |    2770 |          |
|* 17 |      FILTER                               |              |      1 |        |   104K |00:00:00.09 |    2770 |          |
|  18 |       TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |   104K |   104K |00:00:00.05 |    2770 |          |
|* 19 |        INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.02 |     425 |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(:D_FROM<=:D_TO)
   6 - access("T"."ITEM_ID"="I"."ITEM_ID")
   9 - access("I"."ORD_DT">=:D_FROM AND "I"."ORD_DT"<=:D_TO)
  11 - filter(:D_FROM<=:D_TO)
  12 - access("T"."ITEM_ID"="I"."ITEM_ID")
  15 - access("I"."ORD_DT">=:D_FROM AND "I"."ORD_DT"<=:D_TO)
  17 - filter(:D_FROM<=:D_TO)
  19 - access("I"."ORD_DT">=:D_FROM AND "I"."ORD_DT"<=:D_TO)

문제

화면은 98줄인데 8,320블록을 읽는 까닭을 실행계획의 세 묶음으로 설명하시오.
주문상품을 한 번만 읽고 소계와 합계 줄까지 만들도록 SQL을 고치시오. 소계 줄을 어떻게 알아보고 줄 순서를 어떻게 정하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번 읽고 (유형, 메뉴) → (유형) → (전체) 순으로 정렬 한 번에 소계까지 만든다
SELECT CASE WHEN GROUPING(t.ITEM_TYPE_CD) = 1 THEN '(전체)' ELSE t.ITEM_TYPE_CD END AS ITEM_TYPE_CD,
       t.ITEM_ID,
       CASE WHEN GROUPING(t.ITEM_TYPE_CD) = 1 THEN '전체 합계'
            WHEN GROUPING(t.ITEM_ID) = 1      THEN '유형 소계'
            ELSE t.ITEM_NM END                                              AS ITEM_NM,
       SUM(i.ORD_ITEM_QTY) AS qty,
       COUNT(*)            AS lines
  FROM ORD_ITEM i, ITEM t
 WHERE i.ORD_DT BETWEEN :d_from AND :d_to
   AND t.ITEM_ID = i.ITEM_ID
 GROUP BY ROLLUP (t.ITEM_TYPE_CD, (t.ITEM_ID, t.ITEM_NM))   -- 메뉴 번호와 메뉴명은 한 단위로 굴린다
 ORDER BY GROUPING(t.ITEM_TYPE_CD), t.ITEM_TYPE_CD, GROUPING(t.ITEM_ID), qty DESC

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

xplan

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |     98 |00:00:00.14 |    2775 |          |
|   1 |  SORT ORDER BY                          |              |      1 |     93 |     98 |00:00:00.14 |    2775 | 2048 (0) |
|   2 |   SORT GROUP BY ROLLUP                  |              |      1 |     93 |     98 |00:00:00.14 |    2775 | 4096 (0) |
|*  3 |    FILTER                               |              |      1 |        |   104K |00:00:00.10 |    2775 |          |
|*  4 |     HASH JOIN                           |              |      1 |   104K |   104K |00:00:00.09 |    2775 | 1236K (0)|
|   5 |      TABLE ACCESS FULL                  | ITEM         |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |   104K |   104K |00:00:00.05 |    2770 |          |
|*  7 |       INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |   104K |   104K |00:00:00.02 |     425 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:D_FROM<=:D_TO)
   4 - access("T"."ITEM_ID"="I"."ITEM_ID")
   7 - access("I"."ORD_DT">=:D_FROM AND "I"."ORD_DT"<=:D_TO)

모범 답안 서술

세 묶음이 같은 12월 주문상품 104,867줄을 각자 읽는다. 메뉴별 묶음(4번 줄)이 2,775블록, 유형 소계(10번 줄)가 2,775블록, 전체 합계(16번 줄)가 2,770블록이라 합쳐 8,320블록이고, 해시 조인과 집계도 따로 한다. 소계와 합계는 메뉴별 집계를 다시 더하면 나오는 값인데, 원본을 세 번 읽어 만든 셈이다. GROUP BY ROLLUP (t.ITEM_TYPE_CD, (t.ITEM_ID, t.ITEM_NM))으로 바꾸면 주문상품을 한 번 읽어 (유형, 메뉴) 묶음을 만들고, 같은 정렬 안에서 (유형) 소계와 전체 합계 줄을 덧붙인다(SORT GROUP BY ROLLUP). 메뉴 번호와 메뉴명은 괄호로 묶어 한 단위로 굴린다. 소계와 합계 줄에서는 굴린 컬럼이 NULL로 나오므로 GROUPING()으로 그 줄을 알아보고 이름표를 붙인다. ROLLUP이 내는 줄의 순서는 보장되지 않는다. ORDER BY GROUPING(t.ITEM_TYPE_CD), t.ITEM_TYPE_CD, GROUPING(t.ITEM_ID), qty DESC로 메뉴 줄, 유형 소계, 전체 합계 순서를 정한다. Buffers는 8,320 → 2,775, 시간은 0.41초 → 0.14초다. 결과는 98줄(메뉴 92, 유형 소계 5, 전체 합계 1)로 같다.

해설

NVL로 이름표를 붙이면: 여기서는 ITEM_TYPE_CD가 NOT NULL이라 NVL(t.ITEM_TYPE_CD, '(전체)')로 써도 결과가 같다. 하지만 컬럼에 진짜 NULL이 있으면 그 묶음과 합계 줄이 같은 모양이 되어 구분할 수 없다(SORT-046). 소계 줄은 값이 아니라 GROUPING()으로 가린다.
원래 합계 줄은 ITEM과 조인하지 않았다: ITEM에 없는 ITEM_ID 줄이 있었다면 원래 전체 합계에는 들어가고 ROLLUP 합계에서는 빠진다. 지금은 FK가 막고 있어 같다. 바꾸기 전에 원래 SQL과 건수, 합계를 대조한다(교재 08화 정합성 회귀 체크리스트).
GROUPING SETS ((t.ITEM_TYPE_CD, t.ITEM_ID, t.ITEM_NM), (t.ITEM_TYPE_CD), ())도 같은 줄을 낸다. 묶음이 계층(유형 안에 메뉴)이면 ROLLUP이 뜻을 가장 짧게 적는다. 계층이 아닌 묶음을 GROUPING SETS로 적으면 임시 테이블을 거치는 계획(TEMP TABLE TRANSFORMATION)이 나올 수 있다(SORT-048).
소계를 화면에서 더하는 방법도 있다. DB는 메뉴 줄 92개만 내고 화면이 유형별로 더한다. 읽기는 한 번이지만 합계 규칙이 화면 코드로 흩어진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황