메뉴별 판매 매장 목록이 하루치는 같은 매장 이름을 되풀이하고, 한 달로 넓히면 ORA-01489로 멈춘다

실전SELECT버거347 / 700

상황

본사 메뉴팀의 「메뉴별 판매 매장」 화면은 기간을 고르면 메뉴마다 그 기간에 그 메뉴를 판 매장 이름을 쉼표로 이어 한 칸에 보여 준다. 하루를 고르면 '강남구청점,교대점,교대점,…'처럼 같은 매장이 여러 번 나오고, 한 달을 고르면 ORA-01489: result of string concatenation is too long으로 멈춘다. 메뉴팀은 월 단위로 보려고 만든 화면이라 지금은 쓸 수 없다고 한다. 고칠 안이 셋 올라왔다. 개발자는 (가) LISTAGG(DISTINCT …)로 바꾸자고 하고, 운영 DBA는 (나) MAX_STRING_SIZE를 EXTENDED로 바꿔 문자열 한도를 32,767바이트로 늘리자고 한다. 조민우는 (다) 먼저 (메뉴, 매장)으로 줄인 뒤 이어 붙이고 ON OVERFLOW TRUNCATE를 덧붙이자고 한다. 박팀장은 셋을 결과, 일량, 부작용으로 견주어 하나를 고르라고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO · ORD_DT VARCHAR2(8) · SHOP_NO · 인덱스 ORD_X01 (ORD_DT, ORD_HMS) · 2013년 12월 주문 41,540건, 2013-12-30 하루 1,344건
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID)(리프 약 5,520블록) · 12월 주문상품 103,251줄, 12월 30일 3,306줄
SHOP 매장: 200행 · 5블록 · SHOP_NM VARCHAR2(100) · 이름은 9 ~ 30바이트(평균 11.4바이트, 200곳을 쉼표로 다 이으면 약 2,500바이트) · 서로 다른 매장 SH0070과 SH0089의 이름이 둘 다 '여의도점'이다
ITEM 상품: 92행 · 5블록 · 12월에는 92개 메뉴가 모두 팔렸고, 메뉴마다 196 ~ 200곳의 매장에서 팔렸다
이 DB의 MAX_STRING_SIZE는 STANDARD(SQL의 VARCHAR2 한도 4,000바이트)다. 통계는 최신이다.

원본 SQL

sql

SELECT i.ITEM_ID, m.ITEM_NM,
       LISTAGG(s.SHOP_NM, ',') WITHIN GROUP (ORDER BY s.SHOP_NM) AS SHOP_LIST
  FROM ORD o, ORD_ITEM i, SHOP s, ITEM m
 WHERE o.ORD_DT BETWEEN :d_from AND :d_to
   AND i.ORD_NO = o.ORD_NO
   AND s.SHOP_NO = o.SHOP_NO
   AND m.ITEM_ID = i.ITEM_ID
 GROUP BY i.ITEM_ID, m.ITEM_NM
 ORDER BY i.ITEM_ID

실행계획 학습용 예시 · 건수, 바이트, Buffers는 교재 환경 ora-burger에서 잰 값

xplan

[하루 · :d_from = :d_to = '20131230']
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |             |      1 |        |     92 |00:00:00.12 |    5676 |       |       |          |
|   1 |  SORT GROUP BY                            |             |      1 |   3381 |     92 |00:00:00.12 |    5676 |   372K|   372K|  330K (0)|
|*  2 |   FILTER                                  |             |      1 |        |   3306 |00:00:00.12 |    5676 |       |       |          |
|*  3 |    HASH JOIN                              |             |      1 |   3381 |   3306 |00:00:00.12 |    5676 |  1355K|  1355K| 1702K (0)|
|   4 |     TABLE ACCESS FULL                     | ITEM        |      1 |     92 |     92 |00:00:00.01 |       6 |       |       |          |
|*  5 |     HASH JOIN                             |             |      1 |   3381 |   3306 |00:00:00.12 |    5670 |  1506K|  1506K| 1364K (0)|
|*  6 |      HASH JOIN                            |             |      1 |   1370 |   1344 |00:00:00.01 |      46 |  1449K|  1449K| 1717K (0)|
|   7 |       TABLE ACCESS FULL                   | SHOP        |      1 |    200 |    200 |00:00:00.01 |       6 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED | ORD         |      1 |   1370 |   1344 |00:00:00.01 |      40 |       |       |          |
|*  9 |        INDEX RANGE SCAN                   | ORD_X01     |      1 |   1370 |   1344 |00:00:00.01 |       8 |       |       |          |
|  10 |      INDEX FAST FULL SCAN                 | ORD_ITEM_PK |      1 |   2472K|   2472K|00:00:00.16 |    5624 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:D_FROM<=:D_TO)
   3 - access("M"."ITEM_ID"="I"."ITEM_ID")
   5 - access("I"."ORD_NO"="O"."ORD_NO")
   6 - access("S"."SHOP_NO"="O"."SHOP_NO")
   9 - access("O"."ORD_DT">=:D_FROM AND "O"."ORD_DT"<=:D_TO)

[화면 · 하루]  1 한우불고기버거 | 강남구청점,경복궁점,교대점,교대점,구로디지털단지점,구리점,…   (메뉴당 평균 36줄, 가장 긴 칸 636바이트)
[한 달 · 2013-12-01 ~ 12-31]  ORA-01489 · 3번 줄까지 103,251줄 · 메뉴당 약 1,120줄 × 약 12.4바이트 ≈ 1만 4천 바이트

문제

하루치에서 같은 매장이 되풀이되는 까닭과 한 달에서 ORA-01489가 나는 까닭을 실행계획의 줄 수(A-Rows)와 바이트로 설명하시오.
(가), (나), (다)를 결과, 일량, 부작용으로 비교하고 하나를 고르시오.
고른 안을 SQL로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (다) 이어 붙이기 전에 (메뉴, 매장번호)로 줄인다 : 12월 103,251줄 → 18,334쌍
--      매장은 번호로 지운다 (이름이 같은 '여의도점'이 두 곳) · 매장 이름은 줄인 뒤에 붙인다
SELECT v.ITEM_ID, m.ITEM_NM,
       COUNT(*) AS SHOP_CNT,
       LISTAGG(s.SHOP_NM, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT)
         WITHIN GROUP (ORDER BY s.SHOP_NM, s.SHOP_NO) AS SHOP_LIST
  FROM (SELECT i.ITEM_ID, o.SHOP_NO
          FROM ORD o, ORD_ITEM i
         WHERE o.ORD_DT BETWEEN :d_from AND :d_to
           AND i.ORD_NO = o.ORD_NO
         GROUP BY i.ITEM_ID, o.SHOP_NO) v,
       SHOP s, ITEM m
 WHERE s.SHOP_NO = v.SHOP_NO
   AND m.ITEM_ID = v.ITEM_ID
 GROUP BY v.ITEM_ID, m.ITEM_NM
 ORDER BY v.ITEM_ID

개선 후 실행계획 학습용 예시 · 건수, 바이트, Buffers는 교재 환경 ora-burger에서 잰 값

xplan

[한 달 · 2013-12-01 ~ 12-31]
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |             |      1 |        |     92 |00:00:00.17 |    6721 |       |       |          |
|   1 |  SORT GROUP BY                             |             |      1 |     92 |     92 |00:00:00.17 |    6721 |  1541K|   776K| 1369K (0)|
|*  2 |   HASH JOIN                                |             |      1 |  10032 |  18334 |00:00:00.16 |    6721 |  1355K|  1355K| 1702K (0)|
|   3 |    TABLE ACCESS FULL                       | ITEM        |      1 |     92 |     92 |00:00:00.01 |       6 |       |       |          |
|*  4 |    HASH JOIN                               |             |      1 |  10032 |  18334 |00:00:00.16 |    6715 |  1449K|  1449K| 1728K (0)|
|   5 |     TABLE ACCESS FULL                      | SHOP        |      1 |    200 |    200 |00:00:00.01 |       6 |       |       |          |
|   6 |     VIEW                                   |             |      1 |  10032 |  18334 |00:00:00.16 |    6709 |       |       |          |
|   7 |      HASH GROUP BY                         |             |      1 |  10032 |  18334 |00:00:00.16 |    6709 |  2194K|  2194K| 2130K (0)|
|*  8 |       FILTER                               |             |      1 |        |    103K|00:00:00.14 |    6709 |       |       |          |
|*  9 |        HASH JOIN                           |             |      1 |  10032 |    103K|00:00:00.14 |    6709 |  3629K|  2324K| 2786K (0)|
|  10 |         TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |      1 |   4065 |  41540 |00:00:00.01 |    1085 |       |       |          |
|* 11 |          INDEX RANGE SCAN                  | ORD_X01     |      1 |   4065 |  41540 |00:00:00.01 |     160 |       |       |          |
|  12 |         INDEX FAST FULL SCAN               | ORD_ITEM_PK |      1 |   2472K|   2472K|00:00:00.14 |    5624 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("M"."ITEM_ID"="V"."ITEM_ID")
   4 - access("S"."SHOP_NO"="V"."SHOP_NO")
   8 - filter(:D_FROM<=:D_TO)
   9 - access("I"."ORD_NO"="O"."ORD_NO")
  11 - access("O"."ORD_DT">=:D_FROM AND "O"."ORD_DT"<=:D_TO)

[(가) LISTAGG(DISTINCT) · 한 달]  SORT GROUP BY 1,369K (0) · Buffers 6,714 · 두 '여의도점'이 한 번만 나온다

모범 답안 서술

3번 줄 조인은 주문상품 한 줄마다 그 주문을 받은 매장 이름을 하나씩 붙인다. 하루치(2013-12-30)는 3,306줄, 메뉴 하나에 평균 36줄이라 같은 매장에서 두 번 넘게 팔린 메뉴는 그 매장 이름이 그만큼 되풀이된다. LISTAGG는 묶음 안의 줄을 하나도 빼지 않고 잇기 때문이다. 한 달이면 103,251줄, 메뉴 하나에 약 1,120줄이고, 이름 평균 약 12.4바이트(쉼표 포함)를 곱하면 약 1만 4천 바이트다. SQL에서 VARCHAR2의 한도(STANDARD에서 4,000바이트)를 넘으므로 ORA-01489가 난다. 화면이 묻는 것은 '어느 매장에서 팔렸나'인데 SQL은 '몇 번 팔렸나'만큼 이름을 잇는다. (가) LISTAGG(DISTINCT s.SHOP_NM, ',')(19c 이상)는 이어 붙이기 전에 같은 이름을 지운다. 일량은 거의 같다(Buffers 6,714, 정렬 1.3MB). 하지만 이름으로 지우므로 서로 다른 매장 SH0070과 SH0089의 '여의도점'이 하나로 합쳐진다. 매장이 늘어 이름을 다 이은 길이가 4,000바이트에 닿으면 다시 오류가 난다. (나) EXTENDED는 한도를 32,767바이트로 늘린다. 하지만 DB를 UPGRADE 모드로 다시 띄워 utl32k.sql을 돌려야 하고 되돌릴 수 없으며, DB 전체의 VARCHAR2 정의와 인덱스 키 길이에 영향을 준다. 그렇게 해도 되풀이 목록은 그대로이고, 한 달 1만 4천 바이트는 들어가도 1년(약 16만 바이트)이면 다시 넘는다. 화면 하나의 잘못된 SQL을 DB 설정으로 받는 일이라 고르지 않는다. (다)를 고른다. 주문과 주문상품을 조인한 103,251줄을 먼저 (메뉴, 매장번호)로 묶어 18,334쌍으로 줄이고(7번 줄 HASH GROUP BY, 2.1MB), 매장 이름은 줄인 뒤에 붙인다. 메뉴 하나에 매장은 많아야 200곳이고 200곳을 다 이어도 약 2,500바이트라 4,000바이트 안에 든다. 매장번호로 지우므로 '여의도점' 두 곳도 따로 남고, COUNT(*)로 매장 수를 함께 보여 준다. 일량은 Buffers 6,721로 (가)와 같고, 결과는 화면이 묻는 뜻과 같다. ON OVERFLOW TRUNCATE '...' WITH COUNT는 매장이 320곳쯤으로 늘어 4,000바이트를 넘는 날 오류 대신 앞부분과 '...(남은 수)'를 내게 하는 안전장치다. 다만 중복을 둔 채 이것만 붙이면 가나다순 앞쪽 매장 이름이 대여섯 번씩 되풀이된 앞부분 약 4,000바이트와 '...(843)'만 남는다. 잘라 내기는 줄인 뒤에 붙이는 장치이지 중복의 처방이 아니다.

해설

요구부터 다시 본다: 12월에는 메뉴마다 196 ~ 200곳에서 팔려 목록이 거의 모든 매장이다. 메뉴팀이 보려는 것이 '안 판 매장'이라면 매장 표에서 판매 쌍을 빼는(NOT EXISTS) 몇 줄짜리 목록이 더 쓸모 있다.
이름이 같은 매장: 번호로 줄여도 화면에는 '여의도점'이 두 번 보인다. 구별하려면 이름이 겹치는 매장에만 번호를 덧붙인다. 모든 이름에 번호를 붙이면 200곳에 약 2,000바이트가 늘어 4,000바이트를 넘으므로 ON OVERFLOW가 곧바로 일하게 된다.
CLOB으로 받는 길: XMLAGG나 JSON_ARRAYAGG(… RETURNING CLOB)은 한도가 없지만 행마다 LOB을 만들어 느리고 화면이 다루기 어렵다. 줄인 뒤 4,000바이트 안에 드는 목록이면 쓸 일이 없다.
일량의 대부분은 주문상품 PK를 FAST FULL SCAN하는 5,624블록이고, 하루치에서도 같은 블록을 읽는다. 자주 연다면 (일, 메뉴, 매장) 요약을 쌓아 두는 것이 다음 단계다. 이 문제의 바탕은 조인이 줄을 곱한다는 것이다(교재 08화 §1).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수, 바이트, Buffers는 교재 환경 ora-burger에서 잰 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수, 바이트, Buffers는 교재 환경 ora-burger에서 잰 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황