PART-028

새해 0시가 지나자 쇼핑몰 주문 등록이 모두 ORA-14400으로 실패한다

기초DDL한빛유통403 / 700

상황

2025년 1월 1일 00:00:03부터 쇼핑몰 주문이 하나도 저장되지 않는다. 애플리케이션 로그에는 ORA-14400만 줄지어 찍혔고, 새해 0시 특가 때문에 12분 동안 실패가 312건 쌓였다. 당직 DBA가 00:12에 전화를 받았다. 주문 테이블은 2년 전에 월별 파티션으로 바꿨다. 해마다 12월에 담당자가 다음 해 12칸을 손으로 더해 왔는데, 이 일은 운영 일정표 어디에도 없었고 그 담당자는 지난여름 다른 팀으로 옮겼다.

테이블과 인덱스

T_ORD_RNG 주문(파티션 복사본, 문제 설정): T_ORD 100,000행을 PARTITION BY RANGE (ORD_DT) 월별 24칸(P202301 ~ P202412)으로 나눴다(STORAGE (INITIAL 64K)). MAXVALUE 칸은 없다. 잘못된 날짜를 막으려고 일부러 두지 않았다고 한다.
칸 하나는 약 4,170행이고 FULL로 약 38블록이다. ORD_DT는 DATE이고 시분초가 있다(SYSDATE로 넣는다). 평소 하루 주문은 약 137건이다.
인덱스는 LOCAL PK_T_ORD_RNG (ORD_SEQ, ORD_DT)뿐이다. 통계는 최신이다.
보관 기간은 36개월이다. 월 정리 스크립트는 지울 칸의 이름을 날짜로 만든다('P' || TO_CHAR(ADD_MONTHS(SYSDATE, -36), 'YYYYMM')).

원본 SQL

sql

-- 주문 등록 (애플리케이션 · 주문마다 한 번)
INSERT INTO T_ORD_RNG (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
VALUES (SQ_ORD_SEQ.NEXTVAL, :cus_id, SYSDATE, 'PENDING', :amt);

-- 「오늘 주문 현황」 (운영 화면 · 5분마다)
SELECT ORD_ST, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_RNG
 WHERE ORD_DT >= TRUNC(SYSDATE)
   AND ORD_DT <  TRUNC(SYSDATE) + 1
 GROUP BY ORD_ST;

실행계획 학습용 예시

xplan

[2025-01-01 00:00:03 이후 · 주문 등록]
  ORA-14400: inserted partition key does not map to any partition
  00:00 ~ 00:12 실패 312건 (주문 번호는 시퀀스에서 받았다가 버려졌다)

[USER_TAB_PARTITIONS · 마지막 세 칸]
  PARTITION_NAME  POSITION  HIGH_VALUE                                    NUM_ROWS
  P202410               22  TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-…')       4,203
  P202411               23  TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-…')       4,118
  P202412               24  TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-…')       4,236

[2024-12-31 23:55 · 「오늘 주문 현황」]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      3 | 00:00:00.01 |      38 |        |       |
|   1 |  HASH GROUP BY              |           |      1 |      3 |      3 | 00:00:00.01 |      38 |        |       |
|*  2 |   FILTER                    |           |      1 |        |    139 | 00:00:00.01 |      38 |        |       |
|   3 |    PARTITION RANGE ITERATOR |           |      1 |    137 |    139 | 00:00:00.01 |      38 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_RNG |      1 |    137 |    139 | 00:00:00.01 |      38 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(SYSDATE@!)<TRUNC(SYSDATE@!)+1)
   4 - filter(("ORD_DT">=TRUNC(SYSDATE@!) AND "ORD_DT"<TRUNC(SYSDATE@!)+1))

  (실행 때 고른 칸 — 24번 P202412)

문제

ORA-14400이 왜 났는지 칸 정의로 설명하고, 지금 바로 주문을 다시 받으려면 무엇을 해야 하는지 쓰시오.
해마다 같은 일이 생기지 않도록 테이블을 고치고, 그렇게 바꿀 때 함께 고쳐야 할 것과 MAXVALUE 칸을 두는 방법과의 차이를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 지금 바로 (00:15) : 범위 칸을 월 단위 INTERVAL 로 바꾼다 · 사전만 고치는 DDL 이라 바로 끝난다
ALTER TABLE T_ORD_RNG SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

-- 1월 칸은 다음 주문이 들어오는 순간 저절로 생긴다 (애플리케이션은 그대로 다시 시도한다)

-- 함께 고칠 것 ① 새 칸 이름은 SYS_Pnnnn 으로 붙는다 → 월 정리는 이름 대신 값으로 칸을 지목한다
--   (2028년 1월) ALTER TABLE T_ORD_RNG DROP PARTITION FOR (DATE '2025-01-01');

-- 함께 고칠 것 ② 엉뚱한 미래 날짜가 칸을 만들지 못하게 막는다
ALTER TABLE T_ORD_RNG ADD CONSTRAINT CK_T_ORD_RNG_DT
  CHECK (ORD_DT < DATE '2100-01-01');

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

xplan

[00:15 · ALTER TABLE … SET INTERVAL]  경과 0.03초 · 옮긴 행 0

[00:15:21 · 다시 시도한 첫 주문 INSERT 성공 · USER_TAB_PARTITIONS 마지막 두 칸]
  PARTITION_NAME  POSITION  HIGH_VALUE                                    INTERVAL
  P202412               24  TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-…')    NO
  SYS_P4121             25  TO_DATE(' 2025-02-01 00:00:00', 'SYYYY-…')    YES

[01:00 · 「오늘 주문 현황」]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 | 00:00:00.01 |       3 |        |       |
|   1 |  HASH GROUP BY              |           |      1 |      1 |      1 | 00:00:00.01 |       3 |        |       |
|*  2 |   FILTER                    |           |      1 |        |     46 | 00:00:00.01 |       3 |        |       |
|   3 |    PARTITION RANGE ITERATOR |           |      1 |     46 |     46 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | T_ORD_RNG |      1 |     46 |     46 | 00:00:00.01 |       3 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

  (실행 때 고른 칸 — 25번 SYS_P4121 · 테이블 전체를 읽는 계획이면 Pstart 1 · Pstop 1048575 로 찍힌다)

모범 답안 서술

넣으려는 행의 ORD_DT는 2025-01-01 00:00:03이다. 범위 칸은 VALUES LESS THAN의 상한 미만만 받는데, 마지막 칸 P202412의 상한이 2025-01-01이라 이 값은 어느 칸에도 들어가지 못한다. MAXVALUE 칸도 없으니 오라클은 행을 받을 칸이 없다며 ORA-14400을 낸다. 조회는 멀쩡하다(12월 31일의 「오늘 주문 현황」은 24번 칸 하나, 38블록). 막힌 것은 1월 1일 이후의 값을 넣는 적재 쪽이다. 지금 바로 할 일은 1월 칸을 만드는 것이다. ADD PARTITION으로 P202501 하나만 더해도 주문은 다시 들어오지만, 2월 1일 0시에 똑같이 멈춘다. 그래서 ALTER TABLE T_ORD_RNG SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))로 범위 파티션을 월 단위 INTERVAL로 바꾼다. 사전만 고치는 DDL이라 데이터를 옮기지 않고 바로 끝난다(0.03초). 기존 24칸은 범위 구간으로 남고, 새 달 칸은 그달 첫 행이 들어올 때 저절로 생긴다. 다시 시도한 첫 주문과 함께 2025년 1월 칸 SYS_P4121이 생겼고, 「오늘 주문 현황」은 KEY로 그 칸 하나를 읽는다(3블록). 함께 고칠 것이 둘이다. ① 새 칸의 이름은 사람이 정하지 않고 SYS_P4121처럼 붙는다. 칸 이름을 날짜로 만드는 월 정리 스크립트는 2028년 1월에 P202501을 찾다가 실패한다. 이름 대신 ALTER TABLE T_ORD_RNG DROP PARTITION FOR (DATE '2025-01-01')처럼 값으로 칸을 지목하게 바꾼다. ② INTERVAL은 어떤 미래 날짜가 와도 칸을 만들어 받는다. 예전에는 잘못된 날짜(예: 9999-12-31)가 ORA-14400으로 막혔지만, 이제는 엉뚱한 칸이 조용히 생긴다. 그래서 CHECK (ORD_DT < DATE '2100-01-01') 제약으로 막는다. MAXVALUE 칸(PMAX)을 두는 방법도 에러는 막는다. 하지만 새 달 주문이 모두 그 한 칸에 섞여 쌓이고, 나중에 달을 떼어 내려면 SPLIT으로 행을 옮겨 써야 하며 그동안 인덱스까지 다시 고쳐야 한다. 칸 만들기를 잊었을 때 에러가 나는 대신 조용히 무거워질 뿐이다. INTERVAL은 칸 만들기 자체를 사람 손에서 뗀다.

해설

판독: ORA-14400은 "넣을 칸이 없다"는 뜻이다. 범위 칸은 상한 미만만 받으므로 마지막 칸 뒤의 값은 갈 곳이 없다. 그래서 에러는 조회가 아니라 적재에서 난다.
SET INTERVAL은 키가 DATE나 NUMBER 컬럼 하나이고 MAXVALUE 칸이 없을 때만 쓸 수 있다. 버거 ORD_RANGE처럼 문자 키에 MAXVALUE 칸이 있는 테이블은 사정이 다르다(PART-027).
INTERVAL 테이블에서 칸을 전부 여는 계획은 Pstop이 1048575로 찍힌다. 앞으로 생길 수 있는 칸 번호의 끝이다(교재 14화 §6).
새 칸은 테이블의 기본 속성(INITIAL 64K, 테이블스페이스)을 물려받는다. 칸을 여러 테이블스페이스에 돌려 두려면 STORE IN (…)을 준다.
사람의 기억에 기대는 연례 작업은 인수인계에서 가장 먼저 빠진다(교재 01화). 운영 일정표에 없는 일이면 DDL로 없애는 편이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황