PART-042

본사 월별 판매 추이 리포트의 8월 건수가 매장 마감 합계보다 702건 많다

중급DDL유통417 / 700

상황

유통 본사 리포트 DB는 2023년 7월분부터 판매 사본 CF10_P의 월 적재를 파티션 교환으로 바꿨다. 매월 2일 01:00에 운영 CF10에서 지난달 판매를 스테이징 테이블로 떠서, 미리 만들어 둔 빈 칸과 맞바꾼다. 교환은 매번 0.1초 안에 끝난다. 9월 4일 회계팀이 본사 「월별 판매 추이」의 8월 건수가 매장 마감 보고 합계보다 702건 많다고 알려 왔다. 그런데 매장 포털의 「8월 매출」과 「일 매출」 화면은 매장 마감과 맞는다.

테이블과 인덱스

CF10_P 판매 사본(리포트 DB, 문제 설정): CF10과 같은 컬럼(SLE_NO · SLE_DT · SLE_HMS · SLE_DOW · SHP_NO)을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312, 경계는 VALUES LESS THAN ('20220201') 꼴)과 빈 PMAX로 나눴다. P202307은 19번째, P202308은 20번째 칸이다. 9월 4일 현재 데이터가 있는 칸은 P202201 ~ P202308 20칸이고, 칸 하나는 약 20,800행, FULL로 약 191블록이다.
CF10 운영 판매: SLE_DT는 VARCHAR2(8)이다. 매장 마감 보고는 7월 20,686건, 8월 21,104건이고, 8월 1일은 702건, 9월 1일은 688건이다.
PK CF10_P_PK (SLE_NO, SLE_DT): LOCAL 유니크 인덱스. GLOBAL 인덱스는 없다.
교환 뒤 스테이징 테이블에는 원래 칸의 내용(비어 있던 칸)이 남으므로 바로 지운다.

원본 SQL

sql

-- 매월 2일 01:00 · 지난달 판매를 교환으로 넣는다 (9월 2일 : :v_from = '20230801', :v_to = '20230901')
CREATE TABLE CF10_STG AS
SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10
 WHERE SLE_DT BETWEEN :v_from AND :v_to;

ALTER TABLE CF10_STG ADD CONSTRAINT CF10_STG_PK PRIMARY KEY (SLE_NO, SLE_DT);

ALTER TABLE CF10_P
  EXCHANGE PARTITION P202308 WITH TABLE CF10_STG
  INCLUDING INDEXES WITHOUT VALIDATION;

DROP TABLE CF10_STG PURGE;

-- 본사 「월별 판매 추이」
SELECT SUBSTR(SLE_DT, 1, 6) AS ym, COUNT(*) AS cnt
  FROM CF10_P
 GROUP BY SUBSTR(SLE_DT, 1, 6)
 ORDER BY ym;

실행계획 학습용 예시

xplan

[본사 「월별 판매 추이」 · 9월 4일]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |     21 | 00:00:00.09 |    3851 |        |       |
|   1 |  SORT GROUP BY               |        |      1 |     20 |     21 | 00:00:00.09 |    3851 |        |       |
|   2 |   PARTITION RANGE ALL        |        |      1 |   417K |   418K | 00:00:00.06 |    3851 |      1 |    25 |
|   3 |    TABLE ACCESS FULL         | CF10_P |     20 |   417K |   418K | 00:00:00.05 |    3851 |      1 |    25 |
---------------------------------------------------------------------------------------------------------------------
  결과 일부 :  202307  20,686   ·   202308  21,806   ·   202309  688

[매장 포털 「8월 매출」 · :v_from = '20230801', :v_to = '20230831']
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 | 00:00:00.01 |     193 |        |       |
|   1 |  SORT AGGREGATE              |        |      1 |      1 |      1 | 00:00:00.01 |     193 |        |       |
|*  2 |   FILTER                     |        |      1 |        |  21104 | 00:00:00.01 |     193 |        |       |
|   3 |    PARTITION RANGE ITERATOR  |        |      1 |  21104 |  21104 | 00:00:00.01 |     193 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL        | CF10_P |      1 |  21104 |  21104 | 00:00:00.01 |     193 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   4 - filter(("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO))

  (실행 때 고른 칸: 20번 P202308 하나 · 「일 매출」 SLE_DT = '20230801' 도 이 칸만 읽고 702건)

[매장 마감 보고]  7월 20,686  ·  8월 21,104  ·  9월은 아직 마감 전

문제

칸을 고르는 화면은 맞는데 전체 칸을 읽는 리포트만 702건 많은 까닭을 적재 SQL과 실행계획으로 설명하시오. 9월 행 688건이 벌써 보이는 까닭도 쓰시오.
지금 잘못 들어간 행을 찾아 바로잡는 SQL과, 같은 일이 다시 생기지 않는 적재 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 칸에 잘못 들어간 행부터 찾는다 : 칸 이름을 주면 그 칸만 읽는다
SELECT SLE_DT, COUNT(*) FROM CF10_P PARTITION (P202307)
 WHERE SLE_DT >= '20230801' GROUP BY SLE_DT;           -- 20230801  702
SELECT SLE_DT, COUNT(*) FROM CF10_P PARTITION (P202308)
 WHERE SLE_DT >= '20230901' GROUP BY SLE_DT;           -- 20230901  688

-- (2) 바로잡는다 : 8월 1일 행은 P202308 에 제대로 들어 있으므로 P202307 쪽을 지우고,
--     9월 1일 행은 10월 2일 9월 적재 때 들어오므로 P202308 쪽을 지운다
DELETE FROM CF10_P PARTITION (P202307) WHERE SLE_DT >= '20230801';
DELETE FROM CF10_P PARTITION (P202308) WHERE SLE_DT >= '20230901';
COMMIT;

-- (3) 적재 : 상한은 '다음 달 1일 미만' · 교환은 검증을 켠다 (범위 밖 행이 있으면 ORA-14099 로 멈추고 칸은 그대로다)
CREATE TABLE CF10_STG AS
SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10
 WHERE SLE_DT >= :v_from
   AND SLE_DT <  :v_to;

ALTER TABLE CF10_STG ADD CONSTRAINT CF10_STG_PK PRIMARY KEY (SLE_NO, SLE_DT);

ALTER TABLE CF10_P
  EXCHANGE PARTITION P202309 WITH TABLE CF10_STG
  INCLUDING INDEXES WITH VALIDATION;

DROP TABLE CF10_STG PURGE;

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

xplan

[바로잡은 뒤 · 본사 「월별 판매 추이」]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |     20 | 00:00:00.09 |    3846 |        |       |
|   1 |  SORT GROUP BY               |        |      1 |     20 |     20 | 00:00:00.09 |    3846 |        |       |
|   2 |   PARTITION RANGE ALL        |        |      1 |   417K |   417K | 00:00:00.06 |    3846 |      1 |    25 |
|   3 |    TABLE ACCESS FULL         | CF10_P |     20 |   417K |   417K | 00:00:00.05 |    3846 |      1 |    25 |
---------------------------------------------------------------------------------------------------------------------
  결과 일부 :  202307  20,686   ·   202308  21,806 → 21,104   ·   202309  688 → (없음)

[적재 · 검증을 켠 교환 (10월 2일, 9월분)]
  EXCHANGE ... WITH VALIDATION   약 0.2초 (CF10_STG 를 한 번 읽어 칸 범위를 확인한다 · 약 190블록)
  범위 밖 행이 하나라도 있으면     ORA-14099: all rows in table do not qualify for specified partition → 칸은 그대로

모범 답안 서술

적재 SQL의 SLE_DT BETWEEN :v_from AND :v_to에 상한으로 다음 달 1일('20230901')을 넘겼다. BETWEEN은 끝 값을 포함하므로, 9월 2일에 뜬 스테이징에는 8월 판매 21,104건에 9월 1일 판매 688건이 더 들어갔다. 8월 2일의 7월 적재도 같은 식으로 8월 1일 판매 702건을 품었다. 교환은 WITHOUT VALIDATION이라 행이 칸 범위 안에 있는지 확인하지 않았고, 범위 밖의 행이 다른 달 칸에 그대로 들어갔다. 그래서 8월 1일 702건은 P202307과 P202308 두 칸에 하나씩 있고, 9월 1일 688건은 P202308에 미리 들어와 있다. 칸을 고르는 화면은 맞는다. 「8월 매출」은 PARTITION RANGE ITERATOR로 20번 칸 하나만 읽고, 그 칸 안의 9월 1일 행은 SLE_DT <= '20230831' 조건으로 걸러지므로 21,104건이다. 「일 매출」의 SLE_DT = '20230801'도 칸 경계를 믿고 20번 칸만 읽으므로 702건이다. 반면 「월별 판매 추이」는 PARTITION RANGE ALL로 20칸을 모두 읽고 SUBSTR(SLE_DT, 1, 6)으로 묶으므로, 두 칸에 있는 8월 1일을 두 번 세어 8월이 21,806건(702건 많음)이 되고, 아직 마감 전인 9월이 688건으로 나온다. PK가 이 중복을 막지 못한 것은 LOCAL 유니크 인덱스라서다. 칸마다 따로 있는 인덱스는 자기 칸 안의 중복만 검사하고, 다른 칸에 같은 (SLE_NO, SLE_DT)가 있는지는 모른다. 칸 경계가 지켜진다는 전제 위에서만 LOCAL 유니크가 전체 유일성을 보장한다. 바로잡는 일은 칸 이름을 지정해서 한다. CF10_P PARTITION (P202307)에서 SLE_DT >= '20230801'인 702건을 지우고(진짜는 P202308에 있다), P202308에서 9월 1일 688건을 지운다(10월 적재 때 제자리로 들어온다). 적재 SQL은 상한을 SLE_DT < :v_to로 바꾸고, 교환은 검증을 켠다. 검증은 스테이징을 한 번 읽는 일(약 190블록, 0.1초 남짓)이고, 범위 밖 행이 하나라도 있으면 ORA-14099로 멈춰 칸을 건드리지 않는다.

해설

WITHOUT VALIDATION의 뜻: '확인하지 않을 테니 칸 범위는 적재 SQL이 책임진다'는 약속이다. 이 크기에서는 검증이 싸므로 켜 두는 편이 낫다. 칸이 아주 커서 검증을 꺼야 한다면 교환 직전에 MIN(SLE_DT), MAX(SLE_DT)를 칸 경계와 비교하는 단계를 둔다.
프루닝은 칸 경계를 믿는다. 경계 밖의 행이 칸 안에 있으면, 같은 데이터라도 칸을 고르는 조회와 전부 읽는 조회의 답이 달라진다. 숫자가 화면마다 다를 때는 PARTITION (칸)으로 칸 안의 MIN, MAX를 먼저 본다.
상한은 BETWEEN … 말일보다 < 다음 달 1일이 안전하다. 날짜가 문자열이어도 같다(교재 02화의 '< 다음 날', 03화의 문자 날짜).
교환 뒤 스테이징에는 원래 칸의 내용이 남는다. 이 배치는 빈 칸과 바꾸므로 바로 지워도 되지만, 데이터가 있던 칸과 바꿨다면 확인하기 전에는 지우지 않는다(PART-040).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황