목표 대비 실적 화면이 판매상품을 열두 번 읽고, 빨라진 새 판은 목표를 잡지 않은 달의 실적을 빠뜨린다

중급SELECT유통369 / 700

상황

본사 영업기획팀의 「상품별 월 목표 대비 실적」(2023년, 상품 109개 × 12개월 = 1,308줄)은 열 때마다 1.9초가 걸린다. 영업기획팀 20명이 하루 약 400번 연다. 목표는 상품마다 한 줄에 열두 달 칸이 옆으로 늘어선 표 CF_ITM_GOAL에 들어 있어서, 2019년 작성자는 달마다 한 덩어리씩 열두 덩어리를 UNION ALL로 이어 붙였다. 조민우가 UNPIVOT으로 목표를 세로로 세우고 실적을 한 번에 묶는 판을 만들었다. 0.3초로 빨라졌지만 1,261줄만 나왔다. 영업기획팀은 "목표를 잡지 않은 달도 실적은 보여야 한다. 시범 판매나 시즌 연장 실적이 거기 있다"라고 한다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510) · SLE_DT VARCHAR2(8) · 2023년 250,120건 · 한 달 약 20,800건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_QTY · 2023년 줄 약 548,000
CF_ITM_GOAL 상품 월 목표(이 문제에서 쓰는 표): GOAL_YY · ITM_NO · M01_QTY ~ M12_QTY · 연도마다 상품 109줄(2년 218행) · 4블록 · PK (GOAL_YY, ITM_NO)
목표를 잡지 않은 달은 NULL이다. 2023년에는 상품 11개에 NULL 칸이 47개 있고, 그 가운데 19칸에 판매 실적이 있다
인덱스 IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CF 9,921 · 통계 최신

원본 SQL

sql

-- 지금 SQL (2019년) : 달마다 한 덩어리 · 모두 12 덩어리
SELECT g.ITM_NO, '01' AS MM, g.M01_QTY AS GOAL_QTY, a.ACT_QTY
  FROM CF_ITM_GOAL g,
       (SELECT d.ITM_NO, SUM(d.SLE_QTY) AS ACT_QTY
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN '20230101' AND '20230131'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO) a
 WHERE g.GOAL_YY = '2023'
   AND a.ITM_NO(+) = g.ITM_NO
UNION ALL
SELECT g.ITM_NO, '02', g.M02_QTY, a.ACT_QTY
  FROM CF_ITM_GOAL g,
       (SELECT d.ITM_NO, SUM(d.SLE_QTY) AS ACT_QTY
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN '20230201' AND '20230228'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO) a
 WHERE g.GOAL_YY = '2023'
   AND a.ITM_NO(+) = g.ITM_NO
UNION ALL
-- … 3월 ~ 12월도 같은 모양
ORDER BY 1, 2;

-- 조민우 판 (0.3초 · 1,261줄)
SELECT g.ITM_NO, g.MM, g.GOAL_QTY, a.ACT_QTY
  FROM (SELECT ITM_NO, MM, GOAL_QTY
          FROM CF_ITM_GOAL
       UNPIVOT (GOAL_QTY FOR MM IN (M01_QTY AS '01', M02_QTY AS '02', M03_QTY AS '03', M04_QTY AS '04',
                                    M05_QTY AS '05', M06_QTY AS '06', M07_QTY AS '07', M08_QTY AS '08',
                                    M09_QTY AS '09', M10_QTY AS '10', M11_QTY AS '11', M12_QTY AS '12'))
         WHERE GOAL_YY = '2023') g,
       (SELECT d.ITM_NO, SUBSTR(h.SLE_DT, 5, 2) AS MM, SUM(d.SLE_QTY) AS ACT_QTY
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN '20230101' AND '20231231'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO, SUBSTR(h.SLE_DT, 5, 2)) a
 WHERE a.ITM_NO(+) = g.ITM_NO
   AND a.MM(+)     = g.MM
 ORDER BY 1, 2;

실행계획 학습용 예시

xplan

[지금 SQL · 덩어리 12개 가운데 1월과 12월만 펼쳐 적는다]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                |      1 |        |   1308 |00:00:01.91 |     104K|
|   1 |  SORT ORDER BY                            |                |      1 |   1308 |   1308 |00:00:01.91 |     104K|
|   2 |   UNION-ALL                               |                |      1 |        |   1308 |00:00:01.90 |     104K|
|*  3 |    HASH JOIN OUTER                        |                |      1 |    109 |    109 |00:00:00.16 |    8677 |
|*  4 |     TABLE ACCESS FULL                     | CF_ITM_GOAL    |      1 |    109 |    109 |00:00:00.01 |       4 |
|   5 |     VIEW                                  |                |      1 |     88 |     84 |00:00:00.16 |    8673 |
|   6 |      HASH GROUP BY                        |                |      1 |     88 |     84 |00:00:00.16 |    8673 |
|*  7 |       HASH JOIN                           |                |      1 |  45900 |  45188 |00:00:00.14 |    8673 |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20850 |  20611 |00:00:00.01 |     469 |
|*  9 |         INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20850 |  20611 |00:00:00.01 |      60 |
|  10 |        TABLE ACCESS FULL                  | CF11           |      1 |   1096K|   1096K|00:00:00.11 |    8204 |
|     |    (2월 ~ 11월 덩어리 10개 · 모양이 같고 덩어리마다 CF11 FULL 8,204)                                             |
|* 91 |    HASH JOIN OUTER                        |                |      1 |    109 |    109 |00:00:00.16 |    8692 |
|* 92 |     TABLE ACCESS FULL                     | CF_ITM_GOAL    |      1 |    109 |    109 |00:00:00.01 |       4 |
|  93 |     VIEW                                  |                |      1 |     88 |     88 |00:00:00.16 |    8688 |
|  94 |      HASH GROUP BY                        |                |      1 |     88 |     88 |00:00:00.16 |    8688 |
|* 95 |       HASH JOIN                           |                |      1 |  46600 |  46640 |00:00:00.14 |    8688 |
|  96 |        TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  21250 |  21264 |00:00:00.01 |     484 |
|* 97 |         INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  21250 |  21264 |00:00:00.01 |      62 |
|  98 |        TABLE ACCESS FULL                  | CF11           |      1 |   1096K|   1096K|00:00:00.11 |    8204 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ITM_NO"="G"."ITM_NO")
   4 - filter("G"."GOAL_YY"='2023')
   7 - access("D"."SLE_NO"="H"."SLE_NO")
   9 - access("H"."SLE_DT">='20230101' AND "H"."SLE_DT"<='20230131')
  … (덩어리마다 같은 모양 · 97 - access("H"."SLE_DT">='20231201' AND "H"."SLE_DT"<='20231231'))

문제

지금 SQL이 판매상품 테이블을 몇 번 읽는지 실행계획으로 세고, 조민우 판에서 줄이 사라진 까닭을 UNPIVOT의 기본 동작으로 설명하시오.
판매 테이블을 한 번씩만 읽으면서 지금 SQL과 같은 1,308줄을 돌려주도록 SQL을 고치시오.
고친 SQL이 지금 SQL과 같은 답인지 확인하는 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 목표 : UNPIVOT INCLUDE NULLS 로 세운다 (목표가 빈 달도 줄로 남는다 · 109 × 12 = 1,308 줄)
-- 실적 : CF10, CF11 을 한 번씩 읽어 (상품, 월) 로 한꺼번에 묶는다
SELECT g.ITM_NO, g.MM, g.GOAL_QTY, a.ACT_QTY
  FROM (SELECT ITM_NO, MM, GOAL_QTY
          FROM CF_ITM_GOAL
       UNPIVOT INCLUDE NULLS
             (GOAL_QTY FOR MM IN (M01_QTY AS '01', M02_QTY AS '02', M03_QTY AS '03', M04_QTY AS '04',
                                  M05_QTY AS '05', M06_QTY AS '06', M07_QTY AS '07', M08_QTY AS '08',
                                  M09_QTY AS '09', M10_QTY AS '10', M11_QTY AS '11', M12_QTY AS '12'))
         WHERE GOAL_YY = '2023') g,
       (SELECT d.ITM_NO, SUBSTR(h.SLE_DT, 5, 2) AS MM, SUM(d.SLE_QTY) AS ACT_QTY
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN '20230101' AND '20231231'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO, SUBSTR(h.SLE_DT, 5, 2)) a
 WHERE a.ITM_NO(+) = g.ITM_NO
   AND a.MM(+)     = g.MM
 ORDER BY g.ITM_NO, g.MM;

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

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |      1 |        |   1308 |00:00:00.31 |   12718 |          |
|   1 |  SORT ORDER BY           |             |      1 |   1308 |   1308 |00:00:00.31 |   12718 |  124K (0)|
|*  2 |   HASH JOIN OUTER        |             |      1 |   1308 |   1308 |00:00:00.31 |   12718 | 1298K (0)|
|*  3 |    VIEW                  |             |      1 |   1308 |   1308 |00:00:00.01 |       4 |          |
|   4 |     UNPIVOT              |             |      1 |        |   2616 |00:00:00.01 |       4 |          |
|   5 |      TABLE ACCESS FULL   | CF_ITM_GOAL |      1 |    218 |    218 |00:00:00.01 |       4 |          |
|   6 |    VIEW                  |             |      1 |   1056 |   1031 |00:00:00.30 |   12714 |          |
|   7 |     HASH GROUP BY        |             |      1 |   1056 |   1031 |00:00:00.30 |   12714 | 1394K (0)|
|*  8 |      HASH JOIN           |             |      1 |    548K|    548K|00:00:00.24 |   12714 | 9216K (0)|
|*  9 |       TABLE ACCESS FULL  | CF10        |      1 |    250K|    250K|00:00:00.03 |    4510 |          |
|  10 |       TABLE ACCESS FULL  | CF11        |      1 |   1096K|   1096K|00:00:00.11 |    8204 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ITM_NO"="G"."ITM_NO" AND "A"."MM"="G"."MM")
   3 - filter("GOAL_YY"='2023')
   8 - access("D"."SLE_NO"="H"."SLE_NO")
   9 - filter(("H"."SLE_DT">='20230101' AND "H"."SLE_DT"<='20231231'))

모범 답안 서술

지금 SQL은 달마다 같은 일을 처음부터 한다. 덩어리 하나가 그달 판매(CF10)를 인덱스로 고르고, 판매상품 CF11을 처음부터 끝까지 읽어(8,204블록) 해시 조인한 뒤 상품별로 묶는다. 실행계획의 10번 줄과 98번 줄처럼 덩어리 12개가 저마다 CF11 FULL을 하므로, 판매상품 테이블을 열두 번 읽어 합계 약 104K블록이다. 하루 400번이면 약 4,200만 블록이다. 조민우 판은 판매 테이블을 한 번씩만 읽고 상품, 월로 한꺼번에 묶는 데까지는 맞다. 줄이 사라진 것은 UNPIVOT의 기본 동작 때문이다. UNPIVOT은 기본이 EXCLUDE NULLS라서 칸 값이 NULL이면 그 칸을 줄로 만들지 않는다. 목표가 빈 47칸이 줄째 빠졌고, 실적은 목표 줄에 외부 조인으로 붙으므로 그 가운데 실적이 있던 19칸의 실적도 함께 사라졌다. 1,308 − 47 = 1,261줄이다. 고친 SQL은 UNPIVOT INCLUDE NULLS로 목표가 빈 달도 줄로 세우고(GOAL_QTY는 NULL), 실적은 CF10, CF11을 한 번씩 읽어 (상품, 월)로 한꺼번에 묶은 뒤 외부 조인으로 붙인다. 2023년이 판매의 절반이라 CF10도 FULL로 읽는 편이 맞다. 줄 수는 지금 SQL과 같은 1,308줄이고, Buffers는 약 104K → 12,718, 1.9초 → 0.3초가 된다. 같은 답인지는 세 가지로 확인한다. 줄 수(1,308 = 1,308), 목표 합계와 실적 합계, 그리고 두 SQL 결과의 MINUS 양방향(0 / 0)이다. 집합 연산은 NULL끼리를 같은 값으로 보므로, 목표가 NULL인 줄도 MINUS로 그대로 대조할 수 있다.

해설

1 ~ 12를 만들어 곱해도 된다: CONNECT BY LEVEL <= 12로 달 번호를 만들어 목표 표와 곱하고 CASE로 달 칸을 고르는 방법(데이터 복제)도 NULL 칸을 지우지 않는다. UNPIVOT은 그 일을 문법으로 해 주는 것이고, 기본 동작만 조심하면 된다.
목표 표를 세로로 바꾸는 것이 근본이다: 가로형 표는 달이 칸이라서 '달마다 덩어리 하나'라는 SQL을 부른다. 목표 표를 (연도, 상품, 월, 목표) 세로형으로 바꾸면 UNPIVOT 없이 조인 한 번이다. 다른 화면이 이 표를 쓰는지 확인하고, 당장은 세로형 뷰를 하나 두는 방법도 있다.
2019년 SQL이 틀린 답을 낸 것은 아니다. 맞는 답을 열두 번 계산했을 뿐이다. 그래서 고친 SQL을 내기 전에 지금 SQL의 결과를 정답으로 삼아 대조한다(교재 08화 §10).
실적을 묶는 키에 SUBSTR(h.SLE_DT, 5, 2)를 쓴 것은 연도를 하나로 고정했기 때문이다. 여러 해를 함께 보는 화면이라면 SUBSTR(h.SLE_DT, 1, 6)으로 연월을 묶는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황