PART-025

판매상품도 월별 칸으로 옮기려는데 판매상품 테이블에는 날짜가 없다

실전DDL유통400 / 700

상황

유통 본사는 지난달 판매 CF10을 월별 파티션 CF10_P로 옮겼다. 이번에는 판매상품 CF11을 같은 달 단위로 나눠, 보관 기간(24개월)이 지난 달을 두 테이블에서 함께 칸째 들어내려 한다. 지금은 CF11을 매달 DELETE로 지운다. 그런데 CF11에는 날짜 컬럼이 없다. 조민우가 아래 초안을 냈고, 시험 DB 리허설 결과가 아래에 있다. 박팀장: "초안 말고 다른 길도 같이 비교해서 가져와."

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_NO VARCHAR2(15) · ITM_NO · SLE_QTY · SLE_UPRC · PK CF11_PK (SLE_NO, ITM_NO)(높이 3). 한 달 약 45,700행, 영수증 하나에 상품 약 2.2개다.
CF10_P 판매: CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눴다. 칸 하나 FULL ≈ 191블록, P202306(18번째 칸)은 20,953행이다. PK CF10_P_PK (SLE_NO, SLE_DT) LOCAL. 판매번호는 판매일자 8자리 + - + 일련번호 4자리('20230615-0042')이고, CHECK SUBSTR(SLE_NO, 1, 8) = SLE_DT가 걸려 있다.
조회 둘: ① 매장 POS 「영수증 상세」 WHERE SLE_NO = :sle_no (하루 약 3,000번) ② 본사 「월 상품 매출」 CF10_P와 판매상품을 한 달로 조인(하루 몇 번)
운영은 ARCHIVELOG다. 다른 테이블이 CF11을 FK로 참조하지 않는다.

원본 SQL

sql

-- 초안 (조민우) : 판매상품에 판매일자를 붙이고 같은 키로 나눈다
ALTER TABLE CF11 ADD (SLE_DT VARCHAR2(8));

UPDATE CF11 d
   SET d.SLE_DT = (SELECT h.SLE_DT FROM CF10_P h WHERE h.SLE_NO = d.SLE_NO);
COMMIT;

CREATE TABLE CF11_P
PARTITION BY RANGE (SLE_DT)
( PARTITION P202201 VALUES LESS THAN ('20220201')
  -- … P202312 까지 월별
, PARTITION PMAX VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM CF11;

ALTER TABLE CF11_P ADD CONSTRAINT CF11_P_PK
  PRIMARY KEY (SLE_NO, ITM_NO, SLE_DT) USING INDEX LOCAL;

실행계획 학습용 예시 · UPDATE 리허설 수치는 어림값

xplan

[리허설 ① · 판매일자를 채우는 UPDATE (시험 DB)]
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                     |           |      1 |        |      0 | 00:06:12.40 |     27M |        |       |
|   1 |  UPDATE                              | CF11      |      1 |        |      0 | 00:06:12.40 |     27M |        |       |
|   2 |   TABLE ACCESS FULL                  | CF11      |      1 |  1096K |  1096K | 00:00:00.41 |    8204 |        |       |
|   3 |   PARTITION RANGE ALL                |           |   500K |      1 |   500K | 00:01:51.20 |   24.5M |      1 |    25 |
|   4 |    TABLE ACCESS BY LOCAL INDEX ROWID | CF10_P    |    12M |      1 |   500K | 00:01:48.90 |   24.5M |      1 |    25 |
|*  5 |     INDEX RANGE SCAN                 | CF10_P_PK |    12M |      1 |   500K | 00:01:30.10 |     24M |      1 |    25 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("H"."SLE_NO"=:B1)

  redo 약 260MB · 행 이주(migrated rows) 약 31만 행 · 그동안 CF11 전체에 행 잠금

[리허설 ② · 초안 CF11_P에서 영수증 상세 (WHERE SLE_NO = :sle_no)]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      2 | 00:00:00.01 |      50 |        |       |
|   1 |  PARTITION RANGE ALL                |           |      1 |      2 |      2 | 00:00:00.01 |      50 |      1 |    25 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | CF11_P    |     24 |      2 |      2 | 00:00:00.01 |      50 |      1 |    25 |
|*  3 |    INDEX RANGE SCAN                 | CF11_P_PK |     24 |      2 |      2 | 00:00:00.01 |      48 |      1 |    25 |
-----------------------------------------------------------------------------------------------------------------------------

   3 - access("SLE_NO"=:SLE_NO)

  (지금 CF11 에서는 CF11_PK 로 5블록)

문제

초안의 비용과 위험을 셋(판매일자를 채우는 UPDATE, PK의 뜻, 영수증 상세 조회)으로 나눠 위 숫자로 설명하시오.
날짜 컬럼을 붙이지 않고 CF11을 월별로 나누는 설계를 DDL로 작성하고, 영수증 상세와 월 상품 매출이 각각 몇 칸을 여는지 쓰시오.
초안, 위 설계, GLOBAL PK를 두는 안을 월 정리 비용과 부작용으로 비교하고, 고른 안을 지키려면 무엇을 확인해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매번호 앞 8자리 = 판매일자 → 판매번호 자체로 월을 나눈다 (경계는 CF10_P 와 같은 달)
CREATE TABLE CF11_P
PARTITION BY RANGE (SLE_NO)
( PARTITION P202201 VALUES LESS THAN ('20220201')
, PARTITION P202202 VALUES LESS THAN ('20220301')
  -- … 월별
, PARTITION P202312 VALUES LESS THAN ('20240101')
, PARTITION PMAX    VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM CF11;

-- 파티션 키(SLE_NO)가 PK 의 선두 → LOCAL Prefixed 유니크 · 유일성의 뜻은 그대로
ALTER TABLE CF11_P ADD CONSTRAINT CF11_P_PK PRIMARY KEY (SLE_NO, ITM_NO) USING INDEX LOCAL;

-- 칸 배치의 전제(판매번호 형식)를 제약으로 못 박는다
ALTER TABLE CF11_P ADD CONSTRAINT CF11_P_CK_NO
  CHECK (REGEXP_LIKE(SLE_NO, '^[0-9]{8}-[0-9]{4}$'));

-- ① 영수증 상세 : SQL 그대로 · 판매번호만으로 칸 하나
SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC FROM CF11_P WHERE SLE_NO = :sle_no;

-- ② 월 상품 매출 : 판매상품 쪽은 판매번호 범위로 같은 달 칸을 고른다
SELECT d.ITM_NO, SUM(d.SLE_QTY * d.SLE_UPRC) AS amt
  FROM CF10_P h, CF11_P d
 WHERE h.SLE_DT BETWEEN '20230601' AND '20230630'
   AND d.SLE_NO >= '20230601' AND d.SLE_NO < '20230701'
   AND d.SLE_NO = h.SLE_NO
 GROUP BY d.ITM_NO;

-- 월 정리 : 두 테이블의 같은 달을 함께 들어낸다 (글로벌 인덱스가 없어 인덱스 작업이 없다)
ALTER TABLE CF11_P DROP PARTITION P202201;
ALTER TABLE CF10_P DROP PARTITION P202201;

개선 후 실행계획 학습용 예시 · UPDATE 리허설 수치는 어림값

xplan

[① 영수증 상세 · CF11_P (RANGE (SLE_NO))]
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |           |      1 |        |      2 | 00:00:00.01 |       4 |        |       |
|   1 |  PARTITION RANGE SINGLE                     |           |      1 |      2 |      2 | 00:00:00.01 |       4 |    KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | CF11_P    |      1 |      2 |      2 | 00:00:00.01 |       4 |    KEY |   KEY |
|*  3 |    INDEX RANGE SCAN                         | CF11_P_PK |      1 |      2 |      2 | 00:00:00.01 |       2 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------------
   3 - access("SLE_NO"=:SLE_NO)

[② 월 상품 매출 · 2023년 6월]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |      1 |        |    109 | 00:00:00.05 |     539 |        |       |
|   1 |  HASH GROUP BY            |        |      1 |    109 |    109 | 00:00:00.05 |     539 |        |       |
|*  2 |   HASH JOIN               |        |      1 |  45700 |  45912 | 00:00:00.04 |     539 |        |       |
|   3 |    PARTITION RANGE SINGLE |        |      1 |  20953 |  20953 | 00:00:00.01 |     192 |     18 |    18 |
|*  4 |     TABLE ACCESS FULL     | CF10_P |      1 |  20953 |  20953 | 00:00:00.01 |     192 |     18 |    18 |
|   5 |    PARTITION RANGE SINGLE |        |      1 |  45700 |  45912 | 00:00:00.01 |     347 |     18 |    18 |
|*  6 |     TABLE ACCESS FULL     | CF11_P |      1 |  45700 |  45912 | 00:00:00.01 |     347 |     18 |    18 |
----------------------------------------------------------------------------------------------------------------
   2 - access("D"."SLE_NO"="H"."SLE_NO")
   4 - filter(("H"."SLE_DT">='20230601' AND "H"."SLE_DT"<='20230630'))
   6 - filter(("D"."SLE_NO">='20230601' AND "D"."SLE_NO"<'20230701'))

[월 정리 · DROP PARTITION 두 문장]  redo 약 0.02MB · 지금의 DELETE(한 달 45,700행)를 대신한다

모범 답안 서술

초안의 비용은 셋이다. ① 판매일자를 채우는 UPDATE: 110만 행을 모두 고치는데, 서브쿼리가 판매번호만으로 CF10_P_PK를 찾는다. 그 PK는 (SLE_NO, SLE_DT) LOCAL(Nonprefixed)이라 서브쿼리 한 번에 칸 24개를 뒤진다(3번 줄 Starts 500K, 5번 줄 Starts 12M, 24M블록). UPDATE 전체는 약 27M블록, 6분이고, 행마다 8바이트가 늘어 약 31만 행이 이주하며 redo가 약 260MB 나간다. 그동안 CF11의 모든 행이 잠긴다. ② PK의 뜻: CF11_P_PK (SLE_NO, ITM_NO, SLE_DT)는 LOCAL로 만들려고(ORA-14039를 피하려고) 파티션 키를 붙인 것이다. 지키는 것은 세 컬럼 조합의 유일성이라, 판매일자를 잘못 채운 행이 생기면 같은 판매번호와 상품이 두 번 들어가도 막지 못한다. ③ 영수증 상세: 조회는 판매번호만 주는데 PK의 파티션 키가 뒤에 있으니 칸 24개를 뒤진다(5 → 50블록, 하루 3,000번이면 15만 블록). 게다가 새 컬럼을 CF10_P와 늘 맞춰 넣어야 한다. 날짜 컬럼 없이 나누는 길이 있다. 판매번호 앞 8자리가 판매일자이고 문자열 비교는 앞에서부터 하므로, PARTITION BY RANGE (SLE_NO)에 VALUES LESS THAN ('20220201') 같은 경계를 주면 그달 판매번호가 모두 그달 칸에 들어간다. 경계를 CF10_P와 같은 달로 맞추면 두 테이블의 칸이 짝을 이룬다. 파티션 키 SLE_NO가 PK의 선두이므로 (SLE_NO, ITM_NO) LOCAL은 Prefixed 유니크가 되고, 유일성의 뜻이 그대로다. UPDATE도, 새 컬럼도 필요 없다. ① 영수증 상세는 SQL을 고치지 않아도 판매번호만으로 칸 하나를 고른다(RANGE SINGLE, 4블록). ② 월 상품 매출은 판매상품 쪽 조건을 판매번호 범위(d.SLE_NO >= '20230601' AND d.SLE_NO < '20230701')로 주면 두 테이블 모두 18번 칸 하나씩만 읽는다(192 + 347 = 539블록). 월 정리는 두 테이블의 같은 달 칸을 DROP하면 끝이고, 글로벌 인덱스가 없으므로 UPDATE GLOBAL INDEXES도 필요 없다(DELETE 대신 redo 약 0.02MB). GLOBAL PK 안(CF11을 판매일자 없이 다른 방식으로 나누고 (SLE_NO, ITM_NO)를 GLOBAL 유니크로 두는 안)은 유일성과 영수증 조회(5블록)를 지키지만, 칸을 정리할 때마다 UPDATE GLOBAL INDEXES가 110만 행 인덱스를 고쳐야 하고, 빠뜨리면 UNUSABLE이 되어 판매 적재가 멈춘다. 그래서 RANGE (SLE_NO) 안을 권한다. 이 안의 전제는 판매번호 형식 규칙이다. 앞 8자리가 날짜가 아닌 번호가 하나라도 들어오면 엉뚱한 칸에 들어가므로 CHECK 제약으로 형식을 못 박고, 이관 전에 CF10_P와 판매번호 앞 8자리가 다른 판매상품이 0건인지 확인한다. 월 단위 조회는 앞으로 판매번호 범위로 써야 칸을 고른다는 것도 개발 가이드에 적는다.

해설

파티션 키를 PK 안에서 찾는다. 유니크 키 안에 날짜가 이미 들어 있으면, 그 키로 범위 파티션을 하면 LOCAL Prefixed 유니크가 저절로 된다. 날짜 컬럼을 새로 만들어 키에 붙이는 것보다 싸고 뜻도 바뀌지 않는다.
함정: 판매번호 범위의 상한을 <= '20230630'으로 주면 '20230630-0001'이 빠진다. 문자열에서는 '20230630' < '20230630-0001'이다. 상한은 다음 달 첫날 < '20230701'로 준다.
참조 파티션(PARTITION BY REFERENCE)도 검토 대상이다. 자식이 FK로 부모의 칸을 물려받으므로 날짜 컬럼이 필요 없지만, FK가 살아 있어야 하고 부모 쪽 PK 설계에 묶인다.
두 테이블을 같은 키로 나눴다면 칸끼리 짝지어 조인하는 파티션 와이즈 조인이 가능하다. 여기서는 키가 SLE_DT와 SLE_NO로 달라 경계만 같으므로, 그 이점까지 원하면 CF10_P도 SLE_NO 범위로 나누는 것을 검토한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · UPDATE 리허설 수치는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · UPDATE 리허설 수치는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황