PART-043

6월분을 교환으로 넣은 뒤 6월 영수증 재조회만 느리고 6월 재전송 주문은 ORA-01502로 막힌다

기초DDL버거418 / 700

상황

버거 본사 분석 DB의 ORD_RANGE는 지난달부터 월 마감 적재를 교환으로 바꿨다. 매월 2일 운영 ORD에서 지난달 주문을 CTAS로 떠서 미리 만든 빈 칸과 맞바꾼다. 처음에는 교환 문장에 INCLUDING INDEXES를 붙였다가 ORA-14098이 나서 그 절을 뺐고, 그러자 교환이 됐다. 7월 2일 6월분을 넣은 뒤로 6월 주문의 「영수증 재조회」만 느려졌다. 매장이 밤에 다시 보내는 6월 주문 몇 건은 ORA-01502로 들어가지 않는다. 5월 이전 주문은 그대로 빠르다.

테이블과 인덱스

ORD_RANGE 주문(파티션 복사본, 교재 14화): ORD의 컬럼 뒤에 ORD_YM을 붙인 모양이고, PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312과 빈 P_DEFAULT)으로 나눴다. 7월 2일 현재 P201201 ~ P201306 18칸에 데이터가 있다. 칸 하나는 약 41,000행이다.
LOCAL 인덱스 둘: PK ORD_RANGE_PK (ORD_NO, ORD_YM) 유니크 · ORD_RANGE_X01 (ORD_DT, ORD_HMS). 칸마다 높이 2다. GLOBAL 인덱스는 없다. SKIP_UNUSABLE_INDEXES는 기본값(TRUE)이다.
6월 주문은 41,040건이다. 「영수증 재조회」는 POS 배포 뒤로 주문번호와 주문월을 함께 넘긴다(하루 약 6만 번, 평소 3블록).

원본 SQL

sql

-- 매월 2일 · 지난달 주문을 교환으로 넣는다 (7월 2일 = 6월분)
CREATE TABLE ORD_X201306 AS
SELECT o.*, SUBSTR(o.ORD_DT, 1, 6) AS ORD_YM
  FROM ORD o
 WHERE o.ORD_DT BETWEEN '20130601' AND '20130630';

ALTER TABLE ORD_X201306 ADD CONSTRAINT ORD_X201306_PK PRIMARY KEY (ORD_NO, ORD_YM);

-- INCLUDING INDEXES 를 붙였더니 ORA-14098 이 나서 뺐다
ALTER TABLE ORD_RANGE EXCHANGE PARTITION P201306 WITH TABLE ORD_X201306;

DROP TABLE ORD_X201306 PURGE;

실행계획 학습용 예시 · 교재 14화 규모

xplan

[USER_IND_PARTITIONS · 7월 3일]
  INDEX_NAME      PARTITION_NAME  STATUS
  ORD_RANGE_PK    P201305         USABLE
  ORD_RANGE_PK    P201306         UNUSABLE
  ORD_RANGE_X01   P201305         USABLE
  ORD_RANGE_X01   P201306         UNUSABLE

[영수증 재조회 · :ord_no = 803311, :ord_ym = '201306']
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |      1 |        |      1 | 00:00:00.01 |     972 |        |       |
|   1 |  PARTITION RANGE SINGLE   |           |      1 |      1 |      1 | 00:00:00.01 |     972 |    KEY |   KEY |
|*  2 |   TABLE ACCESS FULL       | ORD_RANGE |      1 |      1 |      1 | 00:00:00.01 |     972 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_NO"=:ORD_NO AND "ORD_YM"=:ORD_YM))

[같은 조회 · 5월 주문]   PARTITION RANGE SINGLE · INDEX UNIQUE SCAN ORD_RANGE_PK · 3블록

[6월 재전송 주문 INSERT · 7월 3일 밤]
  ORA-01502: index 'DEV.ORD_RANGE_PK' or partition of such index is in unusable state

문제

6월 칸에서만 조회가 FULL로 바뀌고 INSERT가 ORA-01502로 막히는 까닭을 교환 문장과 인덱스 상태로 설명하시오.
지금 6월을 되살리는 SQL과, 다음 달부터 인덱스까지 함께 교환되도록 고친 적재 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 지금 : 6월 칸의 로컬 인덱스만 다시 짓는다 (다른 17칸은 손대지 않는다)
ALTER TABLE ORD_RANGE MODIFY PARTITION P201306 REBUILD UNUSABLE LOCAL INDEXES;

-- (2) 다음 달부터 : 칸의 로컬 인덱스 둘과 같은 모양을 교환용 테이블에 만들고 인덱스까지 함께 바꾼다
CREATE TABLE ORD_X201307 AS
SELECT o.*, SUBSTR(o.ORD_DT, 1, 6) AS ORD_YM
  FROM ORD o
 WHERE o.ORD_DT BETWEEN '20130701' AND '20130731';

ALTER TABLE ORD_X201307 ADD CONSTRAINT ORD_X201307_PK PRIMARY KEY (ORD_NO, ORD_YM);   -- ORD_RANGE_PK 짝
CREATE INDEX ORD_X201307_X01 ON ORD_X201307 (ORD_DT, ORD_HMS);                        -- ORD_RANGE_X01 짝

ALTER TABLE ORD_RANGE
  EXCHANGE PARTITION P201307 WITH TABLE ORD_X201307
  INCLUDING INDEXES WITH VALIDATION;

-- (3) 교환 뒤 확인 : 그 칸의 로컬 인덱스가 모두 USABLE 인가 (0행이어야 한다)
SELECT index_name, status FROM user_ind_partitions
 WHERE partition_name = 'P201307' AND status <> 'USABLE';

DROP TABLE ORD_X201307 PURGE;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[영수증 재조회 · 6월 주문 · 인덱스를 다시 지은 뒤]
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |      1 | 00:00:00.01 |       3 |        |       |
|   1 |  PARTITION RANGE SINGLE             |              |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | ORD_RANGE    |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  3 |    INDEX UNIQUE SCAN                | ORD_RANGE_PK |      1 |      1 |      1 | 00:00:00.01 |       2 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_NO"=:ORD_NO AND "ORD_YM"=:ORD_YM)

[REBUILD UNUSABLE LOCAL INDEXES · P201306]   두 인덱스의 6월 칸만 · 약 1초
[6월 재전송 주문 INSERT]                      들어간다

모범 답안 서술

교환 문장에 INCLUDING INDEXES가 없으면 기본값 EXCLUDING INDEXES로 교환한다. 이때 오라클은 테이블 칸만 맞바꾸고, 교환된 칸에 붙어 있던 로컬 인덱스 칸은 새 데이터와 맞지 않으므로 UNUSABLE로 표시한다. 그래서 USER_IND_PARTITIONS에서 두 인덱스의 P201306 칸만 UNUSABLE이고, 5월 이전 칸은 USABLE이다. 두 증상은 깨진 인덱스를 대하는 방식이 달라서 생긴다. 조회는 SKIP_UNUSABLE_INDEXES = TRUE라서 옵티마이저가 깨진 인덱스 칸을 건너뛰고 6월 칸을 FULL로 읽는다(PARTITION RANGE SINGLE + TABLE ACCESS FULL, 3 → 972블록). 하루 6만 번 중 6월 주문만큼 그렇게 된다. 반면 INSERT는 유니크 인덱스인 ORD_RANGE_PK를 건너뛸 수 없다. 건너뛰면 중복을 막을 장치가 없어지므로 ORA-01502로 멈춘다. 처음의 ORA-14098은 교환용 테이블의 인덱스가 칸의 로컬 인덱스와 짝이 맞지 않아서 났다. 교환용 테이블에는 PK 인덱스 하나뿐이었고 ORD_RANGE_X01과 짝이 되는 (ORD_DT, ORD_HMS) 인덱스가 없었다. 절을 빼서 에러를 피한 것이 인덱스를 깨뜨렸다. 지금은 ALTER TABLE ORD_RANGE MODIFY PARTITION P201306 REBUILD UNUSABLE LOCAL INDEXES로 6월 칸의 인덱스만 다시 짓는다. 다른 17칸은 건드리지 않으므로 칸 하나 크기만큼만 일한다. 다음 달부터는 교환용 테이블에 PK와 (ORD_DT, ORD_HMS) 인덱스를 모두 만든 뒤 INCLUDING INDEXES로 교환한다. 그러면 교환용 테이블의 인덱스가 그대로 그 칸의 로컬 인덱스가 되어 교환 직후부터 USABLE이다. 배치 끝에서 그 칸의 인덱스 상태를 확인한다.

해설

교환된 칸은 CTAS로 지은 세그먼트라서 FULL로 읽어도 972블록이다. APPEND로 채운 다른 칸(약 1,980블록, 빈 블록 약 1,006 포함)보다 작다(교재 14화 §8.5). 교환 적재의 덤이다.
INCLUDING INDEXES로 바꾸려면 교환용 테이블의 인덱스가 칸의 로컬 인덱스와 컬럼, 순서, 유니크 여부까지 하나씩 짝이 맞아야 한다. PK 같은 유니크 제약도 짝이 있어야 한다(없으면 ORA-14130).
에러를 없애려고 절을 빼는 것은 흔한 실수다. 교환 뒤 USER_IND_PARTITIONS의 상태 확인을 배치의 한 단계로 넣으면 이런 일이 다음 날 아침 전화로 드러나지 않는다.
GLOBAL 인덱스가 있었다면 UPDATE GLOBAL INDEXES도 붙여야 한다. INCLUDING INDEXES는 로컬 인덱스만 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황