리콜 추적용 개수 단위 이력이 주문상품 수량 합계보다 2,835개 적다

중급INSERT버거301 / 700

상황

버거 품질팀은 리콜에 대비해 주문 상품을 개수 단위로 한 줄씩 남긴다(ITEM_UNIT_TRC). 수량 3개짜리 주문상품은 1번, 2번, 3번 세 줄이 된다. 지난주 원천 정정으로 2013년 12월 한 달을 다시 만들었는데, 품질팀이 "12월 이력이 주문상품 수량 합계보다 2,835개 적다"라고 알려 왔다. 적재는 3초가 걸린다. 적재 SQL은 5년 전 "수량은 많아야 5개"라는 말을 듣고 짠 것이다. 지금은 단체 주문이 늘어 한 줄 수량이 수십 개인 주문상품도 있다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_NO · ITEM_ID · ORD_ITEM_QTY · ORD_DT · ORD_HMS · PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS): 날짜순으로 쌓여 있어 한 달 범위를 읽기 좋다
2013년 12월: 104,860줄 · 수량 합계 150,688개 · 수량 6개 이상인 줄 315개(최대 40개) · POS는 한 줄 수량을 99개까지만 받는다
ITEM_UNIT_TRC 개수 단위 이력 (이 문제에서 쓰는 파생 테이블): ORD_NO · ITEM_ID · UNIT_SEQ · ORD_DT · PK (ORD_NO, ITEM_ID, UNIT_SEQ) · 다시 만들 달은 먼저 지운다 · 야간에만 적재한다
통계 최신 · 화면은 그달 1일과 말일(:d1, :d2, 'YYYYMMDD')을 넘긴다

원본 SQL

sql

INSERT INTO ITEM_UNIT_TRC (ORD_NO, ITEM_ID, UNIT_SEQ, ORD_DT)
SELECT ORD_NO, ITEM_ID, 1, ORD_DT FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :d1 AND :d2 AND ORD_ITEM_QTY >= 1
UNION ALL
SELECT ORD_NO, ITEM_ID, 2, ORD_DT FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :d1 AND :d2 AND ORD_ITEM_QTY >= 2
UNION ALL
SELECT ORD_NO, ITEM_ID, 3, ORD_DT FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :d1 AND :d2 AND ORD_ITEM_QTY >= 3
UNION ALL
SELECT ORD_NO, ITEM_ID, 4, ORD_DT FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :d1 AND :d2 AND ORD_ITEM_QTY >= 4
UNION ALL
SELECT ORD_NO, ITEM_ID, 5, ORD_DT FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :d1 AND :d2 AND ORD_ITEM_QTY >= 5

실행계획 학습용 예시

xplan

[INSERT 안의 SELECT만 떼어 돌린 계획 · 2013년 12월]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        | 147853 | 00:00:00.29 |   13450 |
|   1 |  UNION-ALL                             |              |      1 |        | 147853 | 00:00:00.29 |   13450 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   103K | 104860 | 00:00:00.07 |    2690 |
|*  3 |    INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   103K | 104860 | 00:00:00.01 |     346 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |    29K |  29361 | 00:00:00.06 |    2690 |
|*  5 |    INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   103K | 104860 | 00:00:00.01 |     346 |
|*  6 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   9300 |   9437 | 00:00:00.06 |    2690 |
|*  7 |    INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   103K | 104860 | 00:00:00.01 |     346 |
|*  8 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   3100 |   3146 | 00:00:00.05 |    2690 |
|*  9 |    INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   103K | 104860 | 00:00:00.01 |     346 |
|* 10 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   1030 |   1049 | 00:00:00.05 |    2690 |
|* 11 |    INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   103K | 104860 | 00:00:00.01 |     346 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_ITEM_QTY">=1)
   3 - access("ORD_DT">=:D1 AND "ORD_DT"<=:D2)
   4 - filter("ORD_ITEM_QTY">=2)
   5 - access("ORD_DT">=:D1 AND "ORD_DT"<=:D2)
   (6 ~ 11번 줄도 같은 모양 · 수량 조건만 3, 4, 5)

[적재 전체]  3.02초 · 이력 147,853행 (SELECT 0.29초 + 테이블과 PK 인덱스에 넣는 일 2.73초)

문제

이 적재가 같은 주문상품을 몇 번 읽는지 실행계획의 줄 번호로 설명하고, 이력이 2,835개 모자란 이유를 설명하시오.
주문상품을 한 번만 읽고 수량만큼 행을 만드는 INSERT로 고치시오. 복제할 번호의 상한을 어떻게 정하고 지킬지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 주문상품은 한 번만 읽고, 번호 1 ~ 99 와 '번호 <= 수량' 으로 조인해 줄마다 수량만큼 복제한다
-- 상한 99 = POS 가 받는 한 줄 수량의 상한 (바뀌면 이 숫자도 바꾼다 · 적재 뒤 대조로 지킨다)
INSERT /*+ APPEND */ INTO ITEM_UNIT_TRC (ORD_NO, ITEM_ID, UNIT_SEQ, ORD_DT)
SELECT i.ORD_NO, i.ITEM_ID, c.NO, i.ORD_DT
  FROM ORD_ITEM i,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 99) c
 WHERE i.ORD_DT BETWEEN :d1 AND :d2
   AND c.NO <= i.ORD_ITEM_QTY;
COMMIT;

-- 적재 뒤 대조 : 두 숫자가 같아야 한다 (150,688 = 150,688)
SELECT (SELECT SUM(ORD_ITEM_QTY) FROM ORD_ITEM      WHERE ORD_DT BETWEEN :d1 AND :d2) AS qty_sum,
       (SELECT COUNT(*)          FROM ITEM_UNIT_TRC WHERE ORD_DT BETWEEN :d1 AND :d2) AS unit_cnt
  FROM DUAL;

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

xplan

[INSERT 안의 SELECT만 떼어 돌린 계획]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        | 150688 | 00:00:00.11 |    2690 |
|   1 |  MERGE JOIN                             |              |      1 |   519K | 150688 | 00:00:00.11 |    2690 |
|   2 |   SORT JOIN                             |              |      1 |      1 |     99 | 00:00:00.01 |       0 |
|   3 |    VIEW                                 |              |      1 |      1 |     99 | 00:00:00.01 |       0 |
|   4 |     CONNECT BY WITHOUT FILTERING        |              |      1 |        |     99 | 00:00:00.01 |       0 |
|   5 |      FAST DUAL                          |              |      1 |      1 |      1 | 00:00:00.01 |       0 |
|*  6 |   SORT JOIN                             |              |     99 |   103K | 150688 | 00:00:00.09 |    2690 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM     |      1 |   103K | 104860 | 00:00:00.05 |    2690 |
|*  8 |     INDEX RANGE SCAN                    | ORD_ITEM_X01 |      1 |   103K | 104860 | 00:00:00.01 |     346 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("C"."NO"<="I"."ORD_ITEM_QTY")
       filter("C"."NO"<="I"."ORD_ITEM_QTY")
   8 - access("I"."ORD_DT">=:D1 AND "I"."ORD_DT"<=:D2)

[적재 전체 · APPEND]  0.62초 · 이력 150,688행

모범 답안 서술

갈래 다섯 개가 같은 12월 주문상품 104,860줄을 각각 읽는다. 3번, 5번, 7번, 9번, 11번 줄이 같은 인덱스 범위(346블록)를, 2번, 4번, 6번, 8번, 10번 줄이 같은 테이블 블록을 다시 읽는다. 갈래마다 다른 것은 ORD_ITEM_QTY >= n이라는 테이블 필터와 넣는 번호(1 ~ 5)뿐이다. 그래서 SELECT 부분만 5 × 2,690 = 13,450블록이다. 더 큰 문제는 답이 틀린다는 것이다. 갈래가 5번까지만 있으므로 수량 6개 이상인 줄은 5줄만 만들어진다. 12월에 그런 줄이 315개이고 6번째 이후 개수가 2,835개라, 이력이 147,853행으로 수량 합계 150,688개보다 2,835개 적다. 에러 없이 조용히 빠졌다. 주문상품은 한 번만 읽고, 번호 1 ~ 99가 든 행 집합과 c.NO <= i.ORD_ITEM_QTY 조건으로 조인해 줄마다 수량만큼 복제한다. 수량 3인 줄은 번호 1, 2, 3과 짝지어져 세 줄이 되고, 수량 40인 단체 주문 줄은 40줄이 된다. 번호는 CONNECT BY LEVEL <= 99로 만들고, 상한 99는 POS가 받는 한 줄 수량의 상한에서 정했다. SELECT 부분의 Buffers는 13,450 → 2,690이고, 이력은 147,853 → 150,688행으로 수량 합계와 맞는다. 복제 번호의 상한은 반드시 데이터의 최댓값을 덮어야 한다. 원래 SQL의 5와 같은 함정이 99에서 다시 생기지 않도록, 적재 뒤에 수량 합계와 이력 건수를 대조하는 SQL을 함께 두고, POS 상한이 바뀌면 이 숫자도 바꾼다. 상한을 SQL에 박아 두고 싶지 않으면 CONNECT BY LEVEL <= (SELECT MAX(ORD_ITEM_QTY) …)로 구하거나 충분히 큰 복제용 테이블을 쓴다. 대상 달을 지우고 다시 넣는 야간 배치라 APPEND로 직접 경로 적재를 하면 넣는 일(2.73초)도 줄어 적재 전체가 3.02 → 0.62초가 된다. 대신 적재하는 동안 다른 세션은 이 테이블에 DML을 할 수 없다.

해설

수량이 0이거나 음수인 줄: 원래 SQL은 그런 줄을 한 줄도 만들지 않는다. 고친 SQL도 c.NO <= 0을 만족하는 번호가 없어 만들지 않는다. 결과가 같은지 이런 경계까지 확인한다.
루프로 짰다면: 커서로 주문상품을 읽고 수량만큼 INSERT를 반복하는 PL/SQL로도 답은 맞는다. 하지만 INSERT를 150,688번 실행한다. 한 문장으로 복제하는 쪽이 같은 결과를 훨씬 적은 호출로 낸다(교재 15화).
복제 위치: 이 문제는 원본 줄 하나하나가 결과 행이라 원본을 그대로 복제해야 한다. 복제한 뒤 다시 묶는 SQL이라면 SUBQ-063처럼 먼저 묶고 복제할 수 있는지부터 본다.
APPEND의 대가: 직접 경로 적재 중에는 테이블에 배타 잠금(TM 6)이 걸리고, 커밋하기 전에는 같은 세션도 그 테이블을 읽지 못한다. 품질팀 화면이 이 테이블을 낮에 읽으므로 야간에만 돌린다(교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황