취소 정리를 세 문장으로 바꿨더니 처리됨인데 판매가 남은 건이 생겼다

중급DELETE유통440 / 700

상황

유통 본사는 DW 이관 전에 2년 치 취소 판매를 운영 테이블에서 지우기로 했다. 지금 배치는 취소 접수를 한 건씩 돌면서 판매상품과 판매를 지우고 접수를 '처리됨'으로 바꾼 뒤 건마다 커밋한다(14,812건에 6.1초, 커밋 14,812번). 개발자가 세 문장과 커밋 한 번으로 바꿔 리허설했더니 0.9초로 줄었다. 그런데 대조해 보니 3건이 어긋났다. 리허설 중에도 콜센터를 흉내 낸 부하가 취소 접수를 계속 넣었다(콜센터는 24시간 접수한다).

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO). CF11과 CF10 사이에 FK 제약은 걸려 있지 않다(레거시).
CF10_CNCL 취소 접수(이 문제용 테이블): 15,236행 · 52블록 · PK SLE_NO · RCV_DT · PROC_YN('N' 미처리 · 'Y' 처리됨) · PROC_DT. 미처리 14,812건이고 그 판매상품은 32,450행이다. 회차 번호 칸 BAT_ID를 더해도 된다.
보조 인덱스는 없다.

원본 SQL

sql

-- 지금 배치 (6.1초 · 커밋 14,812번)
BEGIN
  FOR r IN (SELECT SLE_NO FROM CF10_CNCL WHERE PROC_YN = 'N') LOOP
    DELETE FROM CF11 WHERE SLE_NO = r.SLE_NO;
    DELETE FROM CF10 WHERE SLE_NO = r.SLE_NO;
    UPDATE CF10_CNCL SET PROC_YN = 'Y', PROC_DT = SYSDATE WHERE SLE_NO = r.SLE_NO;
    COMMIT;
  END LOOP;
END;

-- 개발자가 바꾼 판 (0.9초 · 대조 불일치 3건)
DELETE FROM CF11 WHERE SLE_NO IN (SELECT SLE_NO FROM CF10_CNCL WHERE PROC_YN = 'N');
DELETE FROM CF10 WHERE SLE_NO IN (SELECT SLE_NO FROM CF10_CNCL WHERE PROC_YN = 'N');
UPDATE CF10_CNCL SET PROC_YN = 'Y', PROC_DT = SYSDATE WHERE PROC_YN = 'N';
COMMIT;

실행계획 학습용 예시

xplan

[지금 배치 — 세션 통계]
  경과 시간                 6.0 ~ 6.3 초
  recursive calls           약 59,400
  execute count             약 44,450   (DELETE 29,624 · UPDATE 14,812)
  user commits              14,812
  session logical reads     약 412,000
  redo size                 약 24.1 MB

[바꾼 판 — 세션 통계]
  경과 0.9초 · execute count 4 · user commits 1 · redo 약 17.6 MB

[리허설 뒤 대조 — 원장 기준]
  CF10_CNCL 이 'Y' 인데 CF10 에 판매가 그대로 남은 접수        2건
  CF10 은 지워졌는데 CF11 에 판매상품이 남은 접수              1건
  (셋 다 리허설 중에 콜센터 흉내 부하가 넣은 접수 · 21:04:11 ~ 21:04:12)

문제

바꾼 판에서 3건이 어긋난 이유를 설명하시오.
지금 배치의 비용이 어디서 나오는지 설명하시오.
정리하는 동안에도 접수가 들어온다는 점을 고려해, 빠르면서도 어긋나지 않게 다시 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 이번 회차가 처리할 접수를 먼저 한 문장으로 찍는다 (한 문장 = 한 시점)
UPDATE CF10_CNCL
   SET PROC_YN = 'P', BAT_ID = :bat
 WHERE PROC_YN = 'N';

-- ② ③ 찍어 둔 대상만 — 자식(판매상품)부터
DELETE FROM CF11 WHERE SLE_NO IN (SELECT SLE_NO FROM CF10_CNCL WHERE BAT_ID = :bat);
DELETE FROM CF10 WHERE SLE_NO IN (SELECT SLE_NO FROM CF10_CNCL WHERE BAT_ID = :bat);

-- ④ 같은 회차만 처리됨으로
UPDATE CF10_CNCL
   SET PROC_YN = 'Y', PROC_DT = SYSDATE
 WHERE BAT_ID = :bat;

COMMIT;   -- 한 번. 실패하면 ① 까지 되돌아가 'N' 으로 남는다 · 그사이 들어온 접수는 'N' 그대로 다음 회차로

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

xplan

[② 판매상품 지우기 — DISPLAY_CURSOR (ALLSTATS LAST)]
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |           |      1 |        |      0 |00:00:00.38 |    109K |
|   1 |  DELETE             | CF11      |      1 |        |      0 |00:00:00.38 |    109K |
|   2 |   NESTED LOOPS      |           |      1 |  32580 |  32450 |00:00:00.07 |   44488 |
|*  3 |    TABLE ACCESS FULL| CF10_CNCL |      1 |  14812 |  14812 |00:00:00.01 |      52 |
|*  4 |    INDEX RANGE SCAN | CF11_PK   |  14812 |      2 |  32450 |00:00:00.05 |   44436 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("BAT_ID"=:BAT)
   4 - access("CF11"."SLE_NO"="CF10_CNCL"."SLE_NO")

[회차 전체 — 같은 리허설 부하]
  경과 0.9초 · execute count 4 · user commits 1 · redo 약 17.9 MB
  대조 불일치 0건 · 리허설 중 들어온 접수 41건은 'N' 그대로 남아 다음 회차가 처리

모범 답안 서술

지금 배치는 취소 한 건마다 SQL 세 문장과 커밋 하나를 실행한다. 14,812건이면 DELETE 29,624번, UPDATE 14,812번, 커밋 14,812번이고, 커밋할 때마다 커밋 레코드와 redo 기록이 더해져 6.1초가 걸린다. 바꾼 판이 어긋난 것은 세 문장이 저마다 다른 시점의 데이터를 보기 때문이다. 오라클의 읽기 일관성은 문장 수준이라서, 각 문장은 자기가 시작한 순간의 CF10_CNCL을 읽는다. 첫 DELETE가 시작된 뒤 콜센터가 넣고 커밋한 접수는 두 번째 DELETE나 마지막 UPDATE에는 보인다. 그래서 두 번째 DELETE와 UPDATE 사이에 들어온 접수 2건은 판매가 그대로인데 'Y'가 됐고, 첫 DELETE와 두 번째 DELETE 사이에 들어온 1건은 판매만 지워지고 판매상품이 남았다. FK가 없어서 에러도 나지 않았다. 고친 판은 먼저 이번 회차가 처리할 접수를 한 문장으로 정해 둔다(PROC_YN = 'P', BAT_ID = :bat). 한 문장은 한 시점만 보므로 이 순간의 대상 14,812건이 회차 번호로 묶인다. 그다음 판매상품, 판매 순서로(자식부터) BAT_ID = :bat인 것만 지우고, 마지막에 같은 회차를 'Y'로 바꾼 뒤 한 번 커밋한다. 그사이 들어온 접수는 'N'으로 남아 다음 회차가 처리한다. 중간에 실패하면 표시까지 모두 되돌아가 'N'으로 남으므로 그대로 다시 돌리면 된다. 경과 시간은 6.1초 → 0.9초, 커밋은 14,812번 → 1번으로 준다.

해설

함정: 루프를 집합 문장 여러 개로 바꿀 때, 루프에서는 한 건 안에서 저절로 맞던 대상이 문장마다 따로 정해진다. 같은 조건을 세 번 쓰면 세 번 다른 답을 받을 수 있다. 문장이 여럿이면 대상부터 고정한다.
다른 답: 대상 키를 BULK COLLECT로 배열에 담은 뒤 FORALL로 지우고 고쳐도 대상이 한 시점으로 고정된다. 트랜잭션을 SERIALIZABLE로 열면 세 문장이 같은 시점을 보지만, 다른 세션이 고친 행을 만나면 ORA-08177로 실패할 수 있다.
①이 14,812행의 잠금을 커밋까지 쥔다. 콜센터는 새 접수를 넣기만 하므로 막히지 않지만, 접수를 고치는 화면이 있다면 회차를 작게 나눈다(예: RCV_DT 한 달씩).
FK가 있었다면 두 번째 DELETE가 ORA-02292로 멈춰 어긋남이 에러로 드러났을 것이다. 제약이 없는 레거시에서는 건수 대조가 마지막 그물이다(교재 08화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황