재고 이력에 전일 재고 칸을 채우는 UPDATE가 40만 행에 블록 319만 개를 읽는다

기초MERGE유통448 / 700

상황

본사 레거시 유통의 물류팀은 매장 상품별로 하루 사이에 재고가 얼마나 줄었는지 보려고, 재고 이력 CF18_HIS에 전일 재고 칸(PRV_INV_QTY)을 만들었다. 2년 치를 채우는 UPDATE를 개발 서버에서 돌렸더니 11초 넘게 걸렸다. 물류팀은 재고 실사 정정이 들어올 때마다 이 문장을 다시 돌리겠다고 한다. 개발자: "앞 날짜의 재고를 가져오는 서브쿼리가 PK를 타니까 문제없을 줄 알았어요."

테이블과 인덱스

CF18_HIS 일별상품재고 이력(이 문제를 위해 둔 파생 테이블, 일반 힙 테이블): CF18과 같은 컬럼(SHP_NO · ITM_NO NUMBER · INV_BS_DT VARCHAR2(8) · INV_QTY)에 PRV_INV_QTY NUMBER(지금은 모두 NULL)를 더했다 · 매장 5곳 × 상품 109개 × 730일 = 397,850행 · 1,720블록
PK CF18_HIS_PK (SHP_NO, ITM_NO, INV_BS_DT) · BLEVEL 2. 다른 인덱스는 없다.
매장 상품마다 첫날(2022-01-01)은 앞 날짜가 없다(545행). 칸 자리를 남겨 두고(PCTFREE 20) 만든 테이블이라 칸을 채워도 행 이전은 생기지 않는다. 통계는 최신이다.

원본 SQL

sql

UPDATE CF18_HIS h
   SET h.PRV_INV_QTY =
       (SELECT p.INV_QTY                                   -- 같은 매장 · 상품의 바로 앞 날짜 재고
          FROM CF18_HIS p
         WHERE p.SHP_NO    = h.SHP_NO
           AND p.ITM_NO    = h.ITM_NO
           AND p.INV_BS_DT = (SELECT MAX(q.INV_BS_DT)
                                FROM CF18_HIS q
                               WHERE q.SHP_NO    = h.SHP_NO
                                 AND q.ITM_NO    = h.ITM_NO
                                 AND q.INV_BS_DT < h.INV_BS_DT));
COMMIT;

실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블

xplan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |             |      1 |        |      0 |00:00:11.42 |   3191K |
|   1 |  UPDATE                         | CF18_HIS    |      1 |        |      0 |00:00:11.42 |   3191K |
|   2 |   TABLE ACCESS FULL             | CF18_HIS    |      1 |    397K|    397K|00:00:00.05 |    1720 |
|   3 |   TABLE ACCESS BY INDEX ROWID   | CF18_HIS    |    397K|      1 |    397K|00:00:06.88 |   2784K |
|*  4 |    INDEX UNIQUE SCAN            | CF18_HIS_PK |    397K|      1 |    397K|00:00:05.61 |   2387K |
|   5 |     SORT AGGREGATE              |             |    397K|      1 |    397K|00:00:02.47 |   1194K |
|   6 |      FIRST ROW                  |             |    397K|      1 |    397K|00:00:02.03 |   1194K |
|*  7 |       INDEX RANGE SCAN (MIN/MAX)| CF18_HIS_PK |    397K|      1 |    397K|00:00:01.71 |   1194K |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("P"."SHP_NO"=:B1 AND "P"."ITM_NO"=:B2 AND "P"."INV_BS_DT"=
              (SELECT MAX("Q"."INV_BS_DT") FROM "CF18_HIS" "Q" WHERE "Q"."INV_BS_DT"<:B3 AND "Q"."ITM_NO"=:B4 AND "Q"."SHP_NO"=:B5))
   7 - access("Q"."SHP_NO"=:B1 AND "Q"."ITM_NO"=:B2 AND "Q"."INV_BS_DT"<:B3)

[세션 통계]  갱신된 행 397,850 · 경과 11.4초

문제

Buffers 3,191K가 어디서 나오는지 실행계획의 Starts로 설명하시오.
테이블을 한 번만 읽어 같은 결과를 내도록 다시 작성하시오. 매장 상품의 첫날 행과, 재고를 세지 않아 날짜가 빠진 경우에도 결과가 원본과 같은지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 테이블을 한 번 읽고 매장 · 상품별로 날짜순 정렬 → 바로 위 줄의 재고가 전일 재고
MERGE INTO CF18_HIS t
USING (SELECT ROWID AS rid,
              LAG(INV_QTY) OVER (PARTITION BY SHP_NO, ITM_NO ORDER BY INV_BS_DT) AS prv_qty
         FROM CF18_HIS) s
   ON (t.ROWID = s.rid)                                   -- 계산한 값을 원래 행에 돌려놓는 짝
 WHEN MATCHED THEN UPDATE SET t.PRV_INV_QTY = s.prv_qty;
COMMIT;

개선 후 실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |          |      1 |        |      0 |00:00:01.92 |    409K |          |
|   1 |  MERGE                 | CF18_HIS |      1 |        |      0 |00:00:01.92 |    409K |          |
|   2 |   VIEW                 |          |      1 |        |    397K|00:00:00.74 |    3440 |          |
|*  3 |    HASH JOIN           |          |      1 |    397K|    397K|00:00:00.69 |    3440 |   23M (0)|
|   4 |     VIEW               |          |      1 |    397K|    397K|00:00:00.41 |    1720 |          |
|   5 |      WINDOW SORT       |          |      1 |    397K|    397K|00:00:00.36 |    1720 |   15M (0)|
|   6 |       TABLE ACCESS FULL| CF18_HIS |      1 |    397K|    397K|00:00:00.04 |    1720 |          |
|   7 |     TABLE ACCESS FULL  | CF18_HIS |      1 |    397K|    397K|00:00:00.04 |    1720 |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T".ROWID="S"."RID")

[세션 통계]  갱신된 행 397,850 · 경과 1.9초 · 원본과 MINUS 0행 (첫날 545행은 둘 다 NULL)

모범 답안 서술

원본은 고칠 행마다 SET 절의 서브쿼리를 한 번씩 실행하고, 그 안에서 또 '바로 앞 날짜'를 찾는 서브쿼리를 실행한다. 7번 줄은 행마다 CF18_HIS_PK를 내려가 앞 날짜의 최댓값을 찾고(Starts 397K, 한 번에 3블록, 1,194K), 4번 줄은 그 날짜로 PK를 다시 내려가며(3블록), 3번 줄은 테이블 블록 하나를 읽는다. 한 행에 블록 7개씩, 397,850행이면 2,784K다. 여기에 397,850행을 고치는 몫(약 405K)과 전체 스캔 1,720블록이 더해져 1번 줄 Buffers가 3,191K가 된다. 이웃 행의 값 하나를 얻으려고 행마다 인덱스를 두 번 내려가는 셈이다. 매장 상품별로 날짜순으로 줄을 세우면 바로 앞 날짜의 재고는 바로 위 줄의 값이다. LAG(INV_QTY) OVER (PARTITION BY SHP_NO, ITM_NO ORDER BY INV_BS_DT)는 테이블을 한 번 읽고 한 번 정렬해서 모든 행의 앞 행 값을 구한다. 이 값을 원래 행에 돌려놓을 때는 ROWID로 짝을 짓는다. 원천에 행마다 ROWID를 담아 ON (t.ROWID = s.rid)로 MERGE하면 한 행에 정확히 한 짝이 맞으므로, 키 컬럼으로 다시 맞추거나 행마다 찾아갈 필요가 없다. Buffers는 3,191K → 409K, 경과는 11.4초 → 1.9초로 준다. 남은 409K는 거의 다 397,850행을 고치는 몫이다. 결과는 원본과 같다. 매장 상품마다 첫날(545행)은 앞 행이 없어 원본의 MAX 서브쿼리도 NULL을, LAG도 NULL을 돌려준다. 재고를 세지 않아 날짜가 빠진 날이 있어도, 원본은 INV_BS_DT < 그날인 행 가운데 최댓값(있는 행 가운데 바로 앞)을 찾고 LAG도 줄 세운 집합의 바로 위 행을 가져오므로 같은 값이 나온다. PK가 (매장, 상품, 날짜)라서 같은 날짜가 두 행일 수 없어 동률도 없다.

해설

함정: "PK를 타니까 빠르다"는 한 번의 비용 이야기다. 한 번에 블록 7개여도 40만 번이면 280만 블록이다. 같은 테이블의 이웃 행을 행마다 찾아가는 서브쿼리는 NL 조인의 반복과 같은 모양이다(교재 06화).
키로 MERGE해도 된다. ON (t.SHP_NO = s.SHP_NO AND t.ITM_NO = s.ITM_NO AND t.INV_BS_DT = s.INV_BS_DT)도 맞는 답이다. 다만 원천에 키 컬럼을 모두 담아야 하고 해시 조인의 키가 셋이 된다. 원천과 대상이 같은 테이블이면 ROWID가 가장 짧은 짝이다.
ON 절에는 ROWID만 쓰고 SET은 PRV_INV_QTY만 고치므로 ORA-38104가 나지 않는다. ON 절에 쓴 칸은 SET에서 고칠 수 없다(DML-021).
실사 정정 뒤에 다시 돌릴 때는 WHEN MATCHED THEN UPDATE … WHERE DECODE(t.PRV_INV_QTY, s.prv_qty, 0, 1) = 1을 더해 값이 바뀐 행만 고친다. 그러면 고치는 몫(약 405K)이 정정된 행 수만큼으로 준다(DML-019).
전일 재고를 저장하지 않고 조회할 때 LAG로 계산하는 방법도 있다. 정정이 들어와도 다시 맞출 일이 없지만, 조회할 때마다 정렬이 든다(SORT 단원의 분석 함수).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 이력 테이블은 CF18을 2년 치로 쌓았다고 둔 파생 테이블 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황