PART-041

교환용 테이블을 똑같이 떴는데 EXCHANGE가 ORA-14097로 거부된다

기초DDL한빛유통416 / 700

상황

한빛유통 정산팀은 주문 테이블에서 효과를 본 교환 방식을 정산 파티션 테이블 T_ORD_STLM_P에도 쓰기로 했다. 7월 정산부터 교환용 테이블을 채워 미리 만들어 둔 빈 7월 칸과 맞바꾼다. 교환용 테이블은 SELECT * … WHERE 1 = 0으로 껍데기만 떴고, DESC 결과도 두 테이블이 똑같다. 그런데 교환 문장이 ORA-14097로 거부된다. 조민우: "컬럼 이름도 타입도 크기도 다 같은데요."

테이블과 인덱스

T_ORD_STLM_P 정산(파티션 복사본, 문제 설정): T_ORD_STLM 100,000행 · 640블록을 PARTITION BY RANGE (STLM_DT) 월별 24칸(P202301 ~ P202412)과 빈 PMAX로 나눴다. 칸 하나는 약 4,170행이다. 7월 칸 P202407은 미리 만들어 둔 빈 칸이다.
보이는 컬럼은 STLM_SEQ NUMBER(10) · ORD_SEQ NUMBER(10) · CUS_ID VARCHAR2(40) · STLM_DT DATE · STLM_AMT NUMBER(12)이다.
2023년 3월, 쓰지 않게 된 비고 컬럼 STLM_MEMO VARCHAR2(200)을 업무 시간에 지우려고 ALTER TABLE T_ORD_STLM_P SET UNUSED (STLM_MEMO)를 돌렸다(소스 주석).
LOCAL 유니크 인덱스 UX_STLM_P (ORD_SEQ, STLM_DT). 제약과 GLOBAL 인덱스는 없다.
T_ORD: 100,000행 · PAY_DT는 결제일(시분초 없음)이다. 2024년 7월 결제는 4,212건이다.

원본 SQL

sql

-- 7월 정산을 교환으로 넣는다
CREATE TABLE T_STLM_X202407 AS
SELECT * FROM T_ORD_STLM_P WHERE 1 = 0;

INSERT /*+ APPEND */ INTO T_STLM_X202407 (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT SQ_STLM.NEXTVAL, o.ORD_SEQ, o.CUS_ID, o.PAY_DT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.PAY_DT >= DATE '2024-07-01'
   AND o.PAY_DT <  DATE '2024-08-01';
COMMIT;

CREATE UNIQUE INDEX UX_STLM_X202407 ON T_STLM_X202407 (ORD_SEQ, STLM_DT);

ALTER TABLE T_ORD_STLM_P
  EXCHANGE PARTITION P202407 WITH TABLE T_STLM_X202407
  INCLUDING INDEXES WITH VALIDATION;

실행계획 학습용 예시

xplan

[실행 결과]
  CREATE TABLE T_STLM_X202407 AS SELECT * ... WHERE 1 = 0       Table created.
  INSERT /*+ APPEND */ INTO T_STLM_X202407 ...                   4,212 rows created.
  CREATE UNIQUE INDEX UX_STLM_X202407 ...                        Index created.
  ALTER TABLE T_ORD_STLM_P EXCHANGE PARTITION P202407 ...
  ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

[DESC · 두 테이블이 같다]
  STLM_SEQ NUMBER(10) · ORD_SEQ NUMBER(10) · CUS_ID VARCHAR2(40) · STLM_DT DATE · STLM_AMT NUMBER(12)

[USER_TAB_COLS · 숨은 컬럼까지]
  TABLE_NAME       INTERNAL_COLUMN_ID  COLUMN_NAME                   HIDDEN_COLUMN  DATA_TYPE
  T_ORD_STLM_P     1 ~ 5               STLM_SEQ ~ STLM_AMT           NO
  T_ORD_STLM_P     6                   SYS_C00006_23031410:12:55$    YES            VARCHAR2(200)
  T_STLM_X202407   1 ~ 5               STLM_SEQ ~ STLM_AMT           NO

[USER_UNUSED_COL_TABS]
  TABLE_NAME       COUNT
  T_ORD_STLM_P     1

문제

DESC로는 같은 두 테이블을 오라클이 왜 다르다고 보는지 설명하시오.
교환이 되도록 교환용 테이블을 다시 만드는 SQL을 작성하고, T_ORD_STLM_P의 숨은 컬럼을 실제로 지우는 방법과 비교해 각각의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

DROP TABLE T_STLM_X202407 PURGE;

-- 교환 상대와 똑같은 구조(숨은 UNUSED 컬럼, 컬럼 순서, 타입과 크기까지)의 빈 테이블 (12.2 이상)
CREATE TABLE T_STLM_X202407 FOR EXCHANGE WITH TABLE T_ORD_STLM_P;

INSERT /*+ APPEND */ INTO T_STLM_X202407 (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT SQ_STLM.NEXTVAL, o.ORD_SEQ, o.CUS_ID, o.PAY_DT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.PAY_DT >= DATE '2024-07-01'
   AND o.PAY_DT <  DATE '2024-08-01';
COMMIT;

-- 인덱스는 만들어 주지 않는다 : 칸의 로컬 유니크 인덱스와 같은 모양을 따로 만든다
CREATE UNIQUE INDEX UX_STLM_X202407 ON T_STLM_X202407 (ORD_SEQ, STLM_DT);

ALTER TABLE T_ORD_STLM_P
  EXCHANGE PARTITION P202407 WITH TABLE T_STLM_X202407
  INCLUDING INDEXES WITH VALIDATION;

-- 확인 : 숨은 컬럼까지 두 테이블이 같은지
SELECT table_name, internal_column_id, column_name, hidden_column, data_type, data_length
  FROM user_tab_cols
 WHERE table_name IN ('T_ORD_STLM_P', 'T_STLM_X202407')
 ORDER BY table_name, internal_column_id;

-- (다른 방법 · 작업 창에서) 숨은 컬럼을 실제로 지운다 : 24칸 10만 행을 모두 고쳐 쓴다
--   ALTER TABLE T_ORD_STLM_P DROP UNUSED COLUMNS;

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

xplan

[실행 결과]
  CREATE TABLE T_STLM_X202407 FOR EXCHANGE WITH TABLE T_ORD_STLM_P    Table created.
  INSERT /*+ APPEND */ ...                                              4,212 rows created.
  ALTER TABLE T_ORD_STLM_P EXCHANGE PARTITION P202407 ...              Table altered. (0.05초)

[USER_TAB_COLS · 교환 전]
  T_STLM_X202407   6   SYS_C00006_23031410:12:55$   YES   VARCHAR2(200)   ← 같은 자리에 숨은 컬럼이 생겼다

[비교 · ALTER TABLE T_ORD_STLM_P DROP UNUSED COLUMNS (시험 DB 어림)]
  24칸 100,000행을 모두 고쳐 쓴다 · redo 약 30 MB · 경과 수 초 · 도는 동안 이 테이블의 DML 은 기다린다

모범 답안 서술

DESC와 USER_TAB_COLUMNS는 보이는 컬럼만 보여 준다. 그러나 교환은 숨은 컬럼까지 포함한 실제 컬럼 구성이 같아야 한다. T_ORD_STLM_P에는 2023년 3월 SET UNUSED로 숨긴 STLM_MEMO가 SYS_C00006_…$라는 이름의 숨은 컬럼으로 남아 있다. SET UNUSED는 사전에서 컬럼을 숨길 뿐 행 안의 값을 지우지 않으므로 업무 시간에도 금방 끝났고, 대신 컬럼 자리는 그대로 남았다. USER_TAB_COLS의 HIDDEN_COLUMN = YES인 행과 USER_UNUSED_COL_TABS에서 확인할 수 있다. 교환용 테이블은 CREATE TABLE … AS SELECT * … WHERE 1 = 0으로 떴다. SELECT *는 보이는 컬럼 다섯만 고르므로 새 테이블에는 숨은 컬럼이 없다. 그래서 두 테이블의 컬럼 구성이 달라 ORA-14097로 거부되었다. 처방은 CREATE TABLE T_STLM_X202407 FOR EXCHANGE WITH TABLE T_ORD_STLM_P로 교환용 테이블을 만드는 것이다(12.2 이상). 숨은 컬럼과 컬럼 순서, 타입과 크기까지 교환 상대와 똑같은 빈 테이블이 생긴다. 이 문법은 인덱스를 만들어 주지 않으므로, 칸의 로컬 유니크 인덱스와 같은 모양의 인덱스는 따로 만들고 INCLUDING INDEXES로 교환한다. 다른 방법은 ALTER TABLE T_ORD_STLM_P DROP UNUSED COLUMNS로 숨은 컬럼을 실제로 지우는 것이다. 그러면 SELECT * CTAS로 뜬 테이블과도 교환된다. 하지만 이 문장은 24칸 10만 행을 모두 고쳐 써서 redo가 수십 MB 나오고, 도는 동안 테이블을 잠근다. 2023년 3월에 SET UNUSED로 미뤄 둔 비용을 이제 치르는 셈이다. 그래서 배치는 언제나 FOR EXCHANGE로 교환용 테이블을 만들게 하고, 숨은 컬럼 정리는 필요하면 작업 창에 따로 한다.

해설

컬럼 수가 다르면 ORA-14096, 타입이나 크기가 다르면 ORA-14097이 난다. DB 링크 너머의 테이블에서 CTAS로 받으면 NUMBER(12)가 NUMBER로 오는 식으로 크기가 달라져 같은 에러가 난다(PART-040의 정산계 테이블). 그때도 FOR EXCHANGE로 만든 테이블에 INSERT하면 값이 칸의 타입에 맞춰 들어간다.
12.2 전 버전에는 FOR EXCHANGE가 없다. 교환용 테이블에도 같은 타입의 컬럼을 같은 자리에 더한 뒤 SET UNUSED로 숨겨 구성을 맞춘다.
숨긴 컬럼 말고도 보이지 않는 컬럼(INVISIBLE)이나 가상 컬럼이 있으면 같은 문제가 생긴다. 교환이 거부되면 DESC가 아니라 USER_TAB_COLS를 두 테이블 나란히 놓고 본다.
SET UNUSED는 업무 시간의 안전한 선택이었다. 다만 '나중에 치를 정리가 있다'는 사실을 테이블 이력에 남겨 두어야 다음 사람이 같은 에러 앞에서 헤매지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황