판매 금액 칸을 상세 합계로 맞추는 조인 뷰 UPDATE가 ORA-01779로 막힌다

중급MERGE유통449 / 700

상황

본사 레거시 유통의 판매(CF10)에는 화면 표시용으로 판매 금액(SLE_AMT)과 상품 종류 수(ITM_CNT) 칸이 있다. 6월 반품 정정으로 판매상품(CF11)의 수량이 바뀐 판매가 생겨서, 6월분 두 칸을 상세 합계로 다시 맞춘다. 지금 SQL은 2.4초에 Buffers 178K를 읽는다. 개발자가 조인 뷰 UPDATE로 바꾸려 했더니 ORA-01779가 났다. 개발자: "CF11에도 PK가 있고 CF10에도 PK가 있는데 왜 키 보존이 아니죠?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 2023년 6월 20,953건 · 이 문제에서는 SLE_AMT, ITM_CNT 칸이 더 있다(둘 다 NOT NULL)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 6월분 45,912행(판매 한 건에 평균 2.2행)
6월 판매는 모두 상세가 있다(확인함). 반품 정정으로 두 칸 값이 바뀌는 판매는 312건이다. 통계는 최신이다.

원본 SQL

sql

UPDATE CF10 h
   SET h.SLE_AMT = (SELECT SUM(d.SLE_QTY * d.SLE_UPRC) FROM CF11 d WHERE d.SLE_NO = h.SLE_NO),
       h.ITM_CNT = (SELECT COUNT(*)                    FROM CF11 d WHERE d.SLE_NO = h.SLE_NO)
 WHERE h.SLE_DT BETWEEN '20230601' AND '20230630';

-- 개발자가 바꿔 본 조인 뷰 → ORA-01779: cannot modify a column which maps to a non key-preserved table
-- UPDATE (SELECT h.SLE_AMT, d.SLE_QTY * d.SLE_UPRC AS AMT
--           FROM CF10 h, CF11 d
--          WHERE d.SLE_NO = h.SLE_NO
--            AND h.SLE_DT BETWEEN '20230601' AND '20230630')
--    SET SLE_AMT = AMT;

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                      |                |      1 |        |      0 |00:00:02.41 |    178K |
|   1 |  UPDATE                               | CF10           |      1 |        |      0 |00:00:02.41 |    178K |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |  20953 |  20953 |00:00:00.02 |     257 |
|*  3 |    INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |  20953 |  20953 |00:00:00.01 |      62 |
|   4 |   SORT AGGREGATE                      |                |  20953 |      1 |  20953 |00:00:00.71 |   83812 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF11           |  20953 |      2 |  45912 |00:00:00.62 |   83812 |
|*  6 |     INDEX RANGE SCAN                  | CF11_PK        |  20953 |      2 |  45912 |00:00:00.31 |   62859 |
|   7 |   SORT AGGREGATE                      |                |  20953 |      1 |  20953 |00:00:00.33 |   62859 |
|*  8 |    INDEX RANGE SCAN                   | CF11_PK        |  20953 |      2 |  45912 |00:00:00.29 |   62859 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230630')
   6 - access("D"."SLE_NO"=:B1)
   8 - access("D"."SLE_NO"=:B1)

  갱신된 행 20,953 (값이 바뀐 판매 312)

문제

조인 뷰 UPDATE가 ORA-01779로 막힌 이유를 두 테이블의 관계(1:M)로 설명하시오. 막히지 않았다면 결과가 어떻게 되었을지도 쓰시오.
판매 한 건마다 CF11을 두 번 찾아가지 않도록, CF11을 판매별로 먼저 묶어 한 문장으로 다시 작성하시오. 값이 바뀌는 판매만 고치도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

MERGE INTO CF10 h
USING (SELECT d.SLE_NO,
              SUM(d.SLE_QTY * d.SLE_UPRC) AS SLE_AMT,
              COUNT(*)                    AS ITM_CNT
         FROM CF10 x, CF11 d
        WHERE x.SLE_DT BETWEEN '20230601' AND '20230630'
          AND d.SLE_NO = x.SLE_NO
        GROUP BY d.SLE_NO) s                                   -- 판매 한 건에 한 행
   ON (h.SLE_NO = s.SLE_NO)
 WHEN MATCHED THEN UPDATE
      SET h.SLE_AMT = s.SLE_AMT,
          h.ITM_CNT = s.ITM_CNT
    WHERE h.SLE_AMT <> s.SLE_AMT OR h.ITM_CNT <> s.ITM_CNT;     -- 바뀌는 312건만 (두 칸 모두 NOT NULL)

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

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                          |                |      1 |        |      0 |00:00:00.19 |   13513 |
|   1 |  MERGE                                   | CF10           |      1 |        |      0 |00:00:00.19 |   13513 |
|   2 |   VIEW                                   |                |      1 |        |  20953 |00:00:00.18 |   13044 |
|*  3 |    HASH JOIN                             |                |      1 |  20953 |  20953 |00:00:00.17 |   13044 |
|   4 |     VIEW                                 |                |      1 |  20953 |  20953 |00:00:00.09 |    8461 |
|   5 |      HASH GROUP BY                       |                |      1 |  20953 |  20953 |00:00:00.09 |    8461 |
|*  6 |       HASH JOIN                          |                |      1 |  45912 |  45912 |00:00:00.08 |    8461 |
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |  20953 |  20953 |00:00:00.01 |     257 |
|*  8 |         INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |  20953 |  20953 |00:00:00.01 |      62 |
|   9 |        TABLE ACCESS FULL                 | CF11           |      1 |   1096K|   1096K|00:00:00.05 |    8204 |
|  10 |     TABLE ACCESS FULL                    | CF10           |      1 |    500K|    500K|00:00:00.03 |    4583 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("H"."SLE_NO"="S"."SLE_NO")
   6 - access("D"."SLE_NO"="X"."SLE_NO")
   8 - access("X"."SLE_DT">='20230601' AND "X"."SLE_DT"<='20230630')

  갱신된 행 312 (값이 같은 20,641건은 건너뜀)

모범 답안 서술

CF10과 CF11은 1:M이다. 판매 한 건에 판매상품이 평균 2.2행이므로 조인 결과에서 CF10의 한 행이 두세 번씩 나온다. 오라클이 조인 뷰로 고치게 해 주는 것은 키 보존 테이블, 곧 조인 결과에서 자기 행이 한 번씩만 나오는 테이블의 칸뿐이다. 이 조인에서 키 보존 테이블은 CF11이다(조인 상대 CF10의 SLE_NO가 CF10_PK로 유일하다). CF10은 키 보존이 아니므로 SLE_AMT를 고치려 하면 ORA-01779가 난다. 두 테이블에 PK가 있는지가 아니라 어느 쪽이 '1'인지가 문제다. 막히지 않았다면 판매 한 건을 상세 행 수만큼 여러 번 덮어, 합계가 아니라 마지막에 맞물린 상품 한 줄의 금액이 남는다. 조인 뷰로는 합계를 낼 수 없다. 원본은 6월 판매 20,953건마다 SET 절의 서브쿼리 두 개가 CF11_PK를 따로 찾아간다(4번과 7번 줄 Starts 20,953). 서브쿼리 몫이 83,812 + 62,859블록이고, 바뀌지 않은 판매까지 2만 건을 다 고치는 몫이 더해져 Buffers가 178K다. CF11을 판매별로 먼저 묶으면(GROUP BY SLE_NO) 판매 한 건이 한 행이 되어 대상과 원천이 1:1로 맞물린다. 이것을 원천으로 MERGE 한 문장을 쓰고, WHEN MATCHED의 WHERE로 값이 바뀌는 312건만 고친다. Buffers는 178K → 13,513, 고친 행은 20,953 → 312로 준다.

해설

방향을 바꾸면 조인 뷰가 된다. 같은 조인이라도 CF10의 값을 가져와 CF11의 칸(예: 판매일을 상세에 복사해 둔 칸)을 고치는 것은 조인 뷰로 된다. 고치려는 쪽이 M쪽이고 조인 상대(1쪽)의 키가 유일하기 때문이다.
묶은 결과를 조인 상대로 쓰는 UPDATE (SELECT … FROM CF10 h, (SELECT SLE_NO, SUM(…) … GROUP BY SLE_NO) s …)는 키 보존을 인정받지 못할 수 있다. 원천을 묶어 써야 하는 갱신은 MERGE가 안전하다.
MERGE 원천의 GROUP BY 키가 ON 키와 같다. 그래서 판매 한 건에 원천 한 행이 보장되고 ORA-30926이 나지 않는다. 묶지 않은 CF11을 그대로 원천으로 쓰면 MERGE도 ORA-30926으로 멈춘다.
상세가 없는 판매가 있다면 결과가 달라진다. 원본은 그런 판매의 SLE_AMT를 NULL로 고치려다 ORA-01407로 멈추고(NOT NULL 칸), MERGE는 원천에 없는 판매를 건드리지 않는다. 이 문제에서는 확인했으므로 괜찮다. 확인할 수 없다면 CF10에서 출발해 바깥 조인으로 원천을 만든다.
반정규화 칸은 원천(CF11)을 고칠 때마다 같이 고쳐야 한다. 정정이 잦다면 화면에서 CF11을 묶어 계산하는 쪽도 견줘 본다(교재 14화, DML의 부수비용).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황