6월 단가 소급 조인 뷰가 ORA-01779로 막혀 서브쿼리 UPDATE로 바꿨더니 1만 5천 행을 고친다

중급MERGE유통452 / 700

상황

본사 레거시 유통은 2023년 6월 1일부로 판매가를 바꿨다(상품 38개). 그런데 POS 세 대가 6월 1일 ~ 10일 판매를 옛 단가로 보내서, 판매상품(CF11)의 판매단가(SLE_UPRC)를 상품가격 이력(CF05)의 6월 1일 기준 단가로 소급해 고친다. 처음에는 조인 뷰 UPDATE를 썼는데 ORA-01779로 막혔다. 인터넷에서 찾은 BYPASS_UJVC 힌트를 붙여도 마찬가지였다. 그래서 지금은 아래의 서브쿼리 UPDATE로 돌린다. 매장 정산 담당: "고친 행이 15,301건이라는데, 옛 단가로 들어온 건 4천 건쯤이에요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 6월 1일 ~ 10일 판매 6,984건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 6월 1일 ~ 10일분 15,301행, 그중 옛 단가로 들어온 행 4,112행
CF05 상품가격: 157행 · 5블록 · PK (ITM_NO, UPRC_KN_CD, APL_DT)(CF05_PK, 리프 블록 하나) · 판매가(UPRC_KN_CD = '01') 이력은 상품마다 1 ~ 3행 · 6월 2일 ~ 10일에 시작하는 이력은 없다
통계는 최신이다.

원본 SQL

sql

-- 처음 판 (조인 뷰) → ORA-01779 · BYPASS_UJVC 힌트를 붙여도 같다
-- UPDATE (SELECT d.SLE_UPRC, p.ITM_UPRC
--           FROM CF10 h, CF11 d, CF05 p
--          WHERE h.SLE_DT BETWEEN '20230601' AND '20230610'
--            AND d.SLE_NO = h.SLE_NO
--            AND p.ITM_NO = d.ITM_NO
--            AND p.UPRC_KN_CD = '01'
--            AND p.APL_DT <= '20230601')
--    SET SLE_UPRC = ITM_UPRC;

-- 지금 판
UPDATE CF11 d
   SET d.SLE_UPRC = (SELECT p.ITM_UPRC
                       FROM CF05 p
                      WHERE p.ITM_NO = d.ITM_NO
                        AND p.UPRC_KN_CD = '01'
                        AND p.APL_DT = (SELECT MAX(x.APL_DT)
                                          FROM CF05 x
                                         WHERE x.ITM_NO = d.ITM_NO
                                           AND x.UPRC_KN_CD = '01'
                                           AND x.APL_DT <= '20230601'))
 WHERE d.SLE_NO IN (SELECT h.SLE_NO FROM CF10 h
                     WHERE h.SLE_DT BETWEEN '20230601' AND '20230610');

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                       |                |      1 |        |      0 |00:00:00.37 |   89742 |
|   1 |  UPDATE                                | CF11           |      1 |        |      0 |00:00:00.37 |   89742 |
|   2 |   NESTED LOOPS                         |                |      1 |  15300 |  15301 |00:00:00.04 |   21039 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |   6980 |   6984 |00:00:00.01 |      87 |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |   6980 |   6984 |00:00:00.01 |      22 |
|*  5 |    INDEX RANGE SCAN                    | CF11_PK        |   6984 |      2 |  15301 |00:00:00.02 |   20952 |
|   6 |   TABLE ACCESS BY INDEX ROWID          | CF05           |  15301 |      1 |  15301 |00:00:00.08 |   45903 |
|*  7 |    INDEX UNIQUE SCAN                   | CF05_PK        |  15301 |      1 |  15301 |00:00:00.06 |   30602 |
|   8 |     SORT AGGREGATE                     |                |  15301 |      1 |  15301 |00:00:00.03 |   15301 |
|   9 |      FIRST ROW                         |                |  15301 |      1 |  15301 |00:00:00.02 |   15301 |
|* 10 |       INDEX RANGE SCAN (MIN/MAX)       | CF05_PK        |  15301 |      1 |  15301 |00:00:00.02 |   15301 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230610')
   5 - access("D"."SLE_NO"="H"."SLE_NO")
   7 - access("P"."ITM_NO"=:B1 AND "P"."UPRC_KN_CD"='01' AND "P"."APL_DT"=(SELECT MAX("X"."APL_DT") FROM "CF05" "X" ...))
  10 - access("X"."ITM_NO"=:B1 AND "X"."UPRC_KN_CD"='01' AND "X"."APL_DT"<='20230601')

  갱신된 행 15,301 (단가가 실제로 바뀐 행 4,112)

문제

처음 판의 조인 뷰가 ORA-01779로 막힌 이유를 CF05의 키로 설명하시오. 막히지 않았다면(BYPASS_UJVC가 듣던 시절) 결과가 어떻게 되었을지도 쓰시오.
상품마다 한 행인 단가를 원천으로 MERGE 한 문장을 작성하고, 옛 단가로 들어온 행만 고치도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

MERGE INTO CF11 d
USING (SELECT i.ROWID AS rid, p.ITM_UPRC
         FROM CF10 h
         JOIN CF11 i ON i.SLE_NO = h.SLE_NO
         JOIN (SELECT ITM_NO,
                      MAX(ITM_UPRC) KEEP (DENSE_RANK LAST ORDER BY APL_DT) AS ITM_UPRC
                 FROM CF05
                WHERE UPRC_KN_CD = '01'
                  AND APL_DT <= '20230601'          -- 6/1 인상 한 번뿐이라 기간 내내 같은 단가
                GROUP BY ITM_NO) p                  -- 상품마다 한 행
           ON p.ITM_NO = i.ITM_NO
        WHERE h.SLE_DT BETWEEN '20230601' AND '20230610') s
   ON (d.ROWID = s.rid)                             -- 대상 행을 다시 찾지 않고 주소로 간다
 WHEN MATCHED THEN UPDATE
      SET d.SLE_UPRC = s.ITM_UPRC
    WHERE DECODE(d.SLE_UPRC, s.ITM_UPRC, 0, 1) = 1;   -- 옛 단가로 들어온 행만

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |                |      1 |        |      0 |00:00:00.16 |   42513 |
|   1 |  MERGE                                 | CF11           |      1 |        |      0 |00:00:00.16 |   42513 |
|   2 |   VIEW                                 |                |      1 |        |  15301 |00:00:00.07 |   36345 |
|   3 |    NESTED LOOPS                        |                |      1 |  15300 |  15301 |00:00:00.06 |   36345 |
|*  4 |     HASH JOIN                          |                |      1 |  15300 |  15301 |00:00:00.03 |   21044 |
|   5 |      VIEW                              |                |      1 |    109 |    109 |00:00:00.01 |       5 |
|   6 |       SORT GROUP BY                    |                |      1 |    109 |    109 |00:00:00.01 |       5 |
|*  7 |        TABLE ACCESS FULL               | CF05           |      1 |    131 |    131 |00:00:00.01 |       5 |
|   8 |      NESTED LOOPS                      |                |      1 |  15300 |  15301 |00:00:00.02 |   21039 |
|   9 |       TABLE ACCESS BY INDEX ROWID BATCHED| CF10         |      1 |   6980 |   6984 |00:00:00.01 |      87 |
|* 10 |        INDEX RANGE SCAN                | IX_CF10_SLE_DT |      1 |   6980 |   6984 |00:00:00.01 |      22 |
|* 11 |       INDEX RANGE SCAN                 | CF11_PK        |   6984 |      2 |  15301 |00:00:00.01 |   20952 |
|  12 |     TABLE ACCESS BY USER ROWID         | CF11           |  15301 |      1 |  15301 |00:00:00.02 |   15301 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("P"."ITM_NO"="I"."ITM_NO")
   7 - filter(("UPRC_KN_CD"='01' AND "APL_DT"<='20230601'))
  10 - access("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230610')
  11 - access("I"."SLE_NO"="H"."SLE_NO")

  갱신된 행 4,112 (이미 새 단가인 11,189행은 건너뜀)

모범 답안 서술

CF05의 PK는 (ITM_NO, UPRC_KN_CD, APL_DT) 세 칸이다. 조인 뷰가 CF11의 칸을 고치려면 판매상품 한 행에 맞는 가격 행이 많아야 하나라는 것이 정의로 보장돼야 한다. 그런데 처음 판은 ITM_NO와 UPRC_KN_CD만 등치로 묶고 APL_DT는 <= 범위로 묶었다. PK 칸 전부가 등치로 묶이지 않았으니 한 판매상품에 이력 1 ~ 3행이 맞물릴 수 있고, 그래서 CF11은 키 보존 테이블이 아니어서 ORA-01779가 났다. 데이터가 아니라 조인 조건과 키의 문제라서 힌트로 풀 일이 아니다. BYPASS_UJVC가 듣던 시절이었다면 문장은 돌았겠지만, 이력이 둘 이상인 상품의 판매상품은 맞물린 가격 행 수만큼 여러 번 덮이고 마지막에 맞물린 행의 단가가 남는다. 2022년 단가가 남을 수도 있고, 어느 쪽이 남을지는 조인 순서에 달려 있다. 이 힌트는 문서화되지 않았고 최신 버전에서는 듣지 않는다. 고친 판은 CF05를 먼저 상품마다 한 행으로 줄인다. KEEP (DENSE_RANK LAST ORDER BY APL_DT)로 6월 1일까지 시작한 이력 가운데 가장 늦은 단가 하나만 남기면 판매상품 한 행에 단가 하나가 맞물린다. 이것과 6월 1일 ~ 10일 판매상품을 조인해 원천을 만들고 MERGE 한 문장으로 고친다. 원천에 CF11의 ROWID를 담아 ON (d.ROWID = s.rid)로 맞추면 대상 행을 CF11_PK로 다시 찾지 않고 주소로 바로 간다. MERGE는 실행 중에 같은 대상 행이 두 번 맞물리면 ORA-30926으로 멈추므로, 원천을 한 행으로 줄이는 것은 MERGE의 전제이기도 하다. WHEN MATCHED의 WHERE로 단가가 실제로 다른 행만 고치면 고친 행이 15,301 → 4,112로 준다. 행마다 돌던 가격 서브쿼리 두 개(6번과 8번 줄 Starts 15,301)가 사라져 Buffers도 89,742 → 42,513으로 준다.

해설

키 보존을 살리는 다른 길: 상품마다 한 행인 단가를 PK(ITM_NO)가 있는 작업 테이블에 담으면 CF11이 다시 키 보존 테이블이 되어 조인 뷰 UPDATE가 된다. 문장이 둘이 되고 작업 테이블을 관리해야 하는 대가가 있다.
ON (d.SLE_NO = s.SLE_NO AND d.ITM_NO = s.ITM_NO)로 맞춰도 결과는 같다. 대상 행을 CF11_PK로 다시 찾으므로 행마다 3블록 안팎이 더 든다. ROWID로 맞추는 쪽이 싸다.
기간 안에 단가가 또 바뀌었다면 '6월 1일 기준 한 행'으로는 틀린다. 그때는 판매일마다 그날 유효한 단가를 붙이는 이력 조인을 써야 한다(점이력, JOIN 단원).
값이 같은 행 건너뛰기: 11,189행은 이미 새 단가로 들어와 있었다. 그 행을 고치지 않으면 redo와 잠금이 그만큼 준다. SLE_UPRC가 NULL일 수 있으므로 <> 대신 DECODE로 비교했다.
조인이 행을 불리는 모양(한 행에 여러 행이 맞물린다)은 교재 08화의 50배와 같은 원인이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황