PART-026

이관 리허설에서 첫 월 정리를 돌리자 인덱스 넷이 한꺼번에 UNUSABLE이 되었다

실전DDL한빛유통401 / 700

상황

한빛유통은 T_ORD_BIG을 월별 파티션 T_ORD_BIG_P14로 옮긴다. 보관은 36개월이고, 매월 1일 가장 오래된 달을 DROP한다. 이관 스크립트는 운영 인덱스 넷을 같은 문장으로 다시 만든다. 시험 DB에서 리허설 삼아 첫 월 정리를 돌렸더니 인덱스 넷이 모두 UNUSABLE이 되었고, 아래 네 SQL이 전부 FULL로 바뀌었다. 박팀장: "인덱스마다 LOCAL로 할지, GLOBAL로 할지, 아예 없앨지 근거를 붙여서 정해 와. 월 정리 스크립트도 같이."

테이블과 인덱스

T_ORD_BIG_P14 주문(이관 대상, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX, 모두 37칸. 칸 하나 FULL ≈ 161블록이다.
ORD_DT는 DATE이고 전부 자정이다. 하루 주문은 약 913건, ORD_ST는 COMPLETE 90%, PENDING 8%, CANCEL 2%다. 고객은 2,000명이고 고객마다 약 500건(한 달 약 14건)이다. ORD_SEQ는 유일하다.
운영 T_ORD_BIG(파티션 없음)의 인덱스: IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · UX_ORD_BIG_SEQ (ORD_SEQ) UNIQUE · IX_ORD_BIG_CUS (CUS_ID) · IX_ORD_BIG_ST (ORD_ST)
SQL 넷의 호출 빈도: ① 일 매출(정산 화면) 하루 수천 번 ② 주문 단건(고객센터, 주문번호만 안다) 하루 약 2만 번 ③ 미결 독촉(배치) 하루 1번 ④ 고객 최근 3개월(상담 화면) 하루 약 3,000번
이관 뒤 주문 적재는 매일 밤 하루치(약 913건)를 넣는다.

원본 SQL

sql

-- ① 일 매출
SELECT COUNT(*), SUM(ORD_AMT) FROM T_ORD_BIG_P14
 WHERE ORD_DT >= :d AND ORD_DT < :d + 1;

-- ② 주문 단건 (주문번호만 안다)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG_P14
 WHERE ORD_SEQ = :seq;

-- ③ 미결 독촉 (지난 7일의 PENDING)
SELECT ORD_SEQ, CUS_ID, ORD_AMT FROM T_ORD_BIG_P14
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= TRUNC(SYSDATE) - 7 AND ORD_DT < TRUNC(SYSDATE);

-- ④ 고객 최근 3개월 (이번 달 포함)
SELECT ORD_SEQ, ORD_DT, ORD_AMT FROM T_ORD_BIG_P14
 WHERE CUS_ID = :cus
   AND ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2)
 ORDER BY ORD_DT DESC;

-- 이관 스크립트의 인덱스 (운영과 같은 문장)
CREATE INDEX IX_ORD_BIG_DT_AMT ON T_ORD_BIG_P14 (ORD_DT, ORD_AMT);
CREATE UNIQUE INDEX UX_ORD_BIG_SEQ ON T_ORD_BIG_P14 (ORD_SEQ);
CREATE INDEX IX_ORD_BIG_CUS ON T_ORD_BIG_P14 (CUS_ID);
CREATE INDEX IX_ORD_BIG_ST ON T_ORD_BIG_P14 (ORD_ST);

-- 월 정리 (매월 1일)
ALTER TABLE T_ORD_BIG_P14 DROP PARTITION P202201;

실행계획 학습용 예시 · 운영 수치는 교재 02화 · 06화 · 14화 규모

xplan

[운영 T_ORD_BIG · 지금의 네 SQL]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |      1 |       5 |
|   1 |  SORT AGGREGATE                       |                   |      1 |      1 |      1 |       5 |
|*  2 |   INDEX RANGE SCAN                    | IX_ORD_BIG_DT_AMT |      1 |    913 |    913 |       5 |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |      1 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID          | T_ORD_BIG         |      1 |      1 |      1 |       4 |
|*  2 |   INDEX UNIQUE SCAN                   | UX_ORD_BIG_SEQ    |      1 |      1 |      1 |       3 |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |    511 |    5826 |
|*  1 |  TABLE ACCESS FULL                    | T_ORD_BIG         |      1 |    520 |    511 |    5826 |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |     42 |     504 |
|   1 |  SORT ORDER BY                        |                   |      1 |     42 |     42 |     504 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |      1 |     42 |     42 |     504 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_CUS    |      1 |    500 |    500 |       4 |
--------------------------------------------------------------------------------------------------------
  (위에서부터 ① ② ③ ④ · ③ 은 PENDING 이 3년 치 8만 건이라 IX_ORD_BIG_ST 를 쓰지 않는다)

[시험 DB · 이관 스크립트 그대로 · 첫 월 정리 뒤]
  USER_INDEXES   네 인덱스 모두 PARTITIONED NO · STATUS UNUSABLE
  ① 161  (6월 칸 FULL)      ② 5,707 (35칸 FULL)
  ③ 322  (두 칸 FULL)       ④ 483  (세 칸 FULL)
  밤 적재 INSERT            ORA-01502 (UX_ORD_BIG_SEQ)

문제

리허설에서 인덱스 넷이 모두 UNUSABLE이 된 까닭을 쓰시오.
SQL ① ~ ④마다 인덱스를 LOCAL로 둘지, GLOBAL로 둘지, 만들지 않을지 정해 DDL로 작성하고, 각 SQL이 몇 칸을 열고 몇 블록을 읽을지 어림하시오.
그 설계에 맞는 월 정리 스크립트를 작성하고, 설계의 대가(월 정리, 적재)를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 파티션 키(ORD_DT)가 선두이고 등치 같은 하루 범위 → LOCAL Prefixed · 칸 하나에서 끝난다
CREATE INDEX IX_P14_DT_AMT ON T_ORD_BIG_P14 (ORD_DT, ORD_AMT) LOCAL;

-- ② 파티션 키를 모른다 → GLOBAL 유니크 (LOCAL 이면 칸 36개를 뒤지고, 유니크로 만들지도 못한다)
CREATE UNIQUE INDEX UX_P14_SEQ ON T_ORD_BIG_P14 (ORD_SEQ);

-- ③ 인덱스를 만들지 않는다 : 지난 7일 = 칸 한두 개 FULL (IX_ORD_BIG_ST 는 옮기지 않는다)

-- ④ 고객이 등치, 날짜가 범위 → LOCAL Nonprefixed · 칸 세 개
CREATE INDEX IX_P14_CUS_DT ON T_ORD_BIG_P14 (CUS_ID, ORD_DT) LOCAL;

-- 월 정리 (매월 1일) : 글로벌 인덱스는 ② 하나 · DDL 이 같이 고치게 한다
ALTER TABLE T_ORD_BIG_P14 DROP PARTITION P202201 UPDATE GLOBAL INDEXES;
--   다음 달 칸은 빈 PMAX 를 미리 쪼개 만든다 (옮길 행이 없어 인덱스 일이 거의 없다)
ALTER TABLE T_ORD_BIG_P14 SPLIT PARTITION PMAX AT (DATE '2025-02-01')
  INTO (PARTITION P202501, PARTITION PMAX) UPDATE INDEXES;
--   끝 : 깨진 인덱스가 없는지 확인한다
SELECT index_name, status FROM user_indexes
 WHERE table_name = 'T_ORD_BIG_P14' AND status NOT IN ('VALID', 'N/A');
SELECT index_name, partition_name FROM user_ind_partitions
 WHERE index_name IN ('IX_P14_DT_AMT', 'IX_P14_CUS_DT') AND status <> 'USABLE';

개선 후 실행계획 학습용 예시 · 운영 수치는 교재 02화 · 06화 · 14화 규모

xplan

[② 주문 단건 · UX_P14_SEQ (GLOBAL)]
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |      1 | 00:00:00.01 |       4 |        |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID | T_ORD_BIG_P14 |      1 |      1 |      1 | 00:00:00.01 |       4 |  ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                 | UX_P14_SEQ    |      1 |      1 |      1 | 00:00:00.01 |       3 |        |       |
---------------------------------------------------------------------------------------------------------------------------------

[③ 미결 독촉 · 인덱스 없이 · 지난 7일이 두 달에 걸친 날]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |    511 | 00:00:00.01 |     322 |        |       |
|*  1 |  FILTER                    |               |      1 |        |    511 | 00:00:00.01 |     322 |        |       |
|   2 |   PARTITION RANGE ITERATOR |               |      1 |    520 |    511 | 00:00:00.01 |     322 |    KEY |   KEY |
|*  3 |    TABLE ACCESS FULL       | T_ORD_BIG_P14 |      2 |    520 |    511 | 00:00:00.01 |     322 |    KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------

[④ 고객 최근 3개월 · IX_P14_CUS_DT (LOCAL)]
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |      1 |        |     42 | 00:00:00.01 |      48 |        |       |
|   1 |  SORT ORDER BY                               |               |      1 |     42 |     42 | 00:00:00.01 |      48 |        |       |
|   2 |   PARTITION RANGE ITERATOR                   |               |      1 |     42 |     42 | 00:00:00.01 |      48 |    KEY |    37 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |      3 |     42 |     42 | 00:00:00.01 |      48 |    KEY |    37 |
|*  4 |     INDEX RANGE SCAN                         | IX_P14_CUS_DT |      3 |     42 |     42 | 00:00:00.01 |       6 |    KEY |    37 |
------------------------------------------------------------------------------------------------------------------------------------------
  (Pstop 37 = 빈 PMAX 까지 · 세그먼트가 없어 시작하지 않는다)

[정리]   ① 5 → 5 (칸 하나)   ② 4 → 4   ③ 5,826 → 322   ④ 504 → 48
         월 정리 뒤 네 인덱스 모두 VALID / USABLE · 밤 적재가 고치는 인덱스 넷 → 셋

모범 답안 서술

이관 스크립트의 CREATE INDEX에는 LOCAL이 없다. 파티션 테이블에 이렇게 만든 인덱스는 칸으로 나뉘지 않은 GLOBAL 인덱스 하나가 된다. 월 정리는 UPDATE GLOBAL INDEXES 없이 DROP PARTITION을 했으므로, 지운 칸의 키를 들고 있던 글로벌 인덱스 넷이 모두 UNUSABLE이 되었다. 조회는 에러 없이 FULL로 바뀌었고, 유니크인 UX_ORD_BIG_SEQ 때문에 밤 적재는 ORA-01502로 멈췄다. 인덱스는 SQL마다 조건에 파티션 키 ORD_DT가 있는지로 정한다. 파티션 키가 있는 SQL은 테이블이 칸을 고르므로 LOCAL 인덱스로 그 칸 안만 보면 되고, LOCAL은 월 정리 DDL이 건드리지 않는다. 파티션 키가 없는 SQL만 GLOBAL을 쓴다. ① 일 매출은 하루 범위라 칸 하나만 연다. 선두가 파티션 키인 LOCAL Prefixed (ORD_DT, ORD_AMT)를 두면 그 칸에서 하루 치 키만 읽어 테이블 없이 끝난다(5 → 5블록). ② 주문 단건은 주문번호만 안다. LOCAL이면 칸 36개를 뒤지고(약 73블록), 파티션 키가 없어서 LOCAL 유니크로 만들 수도 없다. 그래서 GLOBAL 유니크 (ORD_SEQ)로 둔다(4 → 4블록). ③ 미결 독촉은 지난 7일이라 칸 한두 개만 연다. 그 안의 PENDING은 약 511건으로 칸 크기의 대부분을 흩어 읽게 되므로, 인덱스보다 칸 FULL이 낫다(5,826 → 322블록). IX_ORD_BIG_ST는 옮기지 않는다. ④ 고객 최근 3개월은 고객이 등치, 날짜가 범위다. LOCAL Nonprefixed (CUS_ID, ORD_DT)면 칸 세 개에서 2블록씩 읽고 테이블 42블록을 더해 504 → 48블록이다. 기간이 3개월이라 GLOBAL로 해도 6 → 3블록밖에 줄지 않으므로 LOCAL로 둔다. 월 정리는 ALTER TABLE T_ORD_BIG_P14 DROP PARTITION P202201 UPDATE GLOBAL INDEXES로 바꾼다. 글로벌 인덱스는 ② 하나뿐이고, 23ai는 지운 키를 고아 항목으로 두었다가 나중에 정리하므로 DDL은 금방 끝난다. 다음 달 칸은 비어 있는 PMAX를 미리 쪼개 만든다. 옮길 행이 없어 인덱스 일이 거의 없다. 스크립트 끝에서 USABLE이 아닌 인덱스나 칸이 없는지 확인한다. 대가는 둘이다. 첫째, ②의 GLOBAL 인덱스 때문에 월 정리 DDL에 절 하나를 반드시 붙여야 하고, 빠뜨리면 리허설처럼 적재가 멈춘다. 둘째, 밤 적재는 인덱스 셋을 고친다(IX_ORD_BIG_ST를 뺐으므로 넷 → 셋). ③을 인덱스 없이 두는 대신 미결 독촉이 하루 몇 번으로 늘어나면 (ORD_ST, ORD_DT) LOCAL을 다시 검토한다.

해설

설계 순서: SQL마다 ⓐ 파티션 키 조건이 있는가(칸을 고를 수 있나) ⓑ 칸 안에서 무엇이 등치이고 무엇이 범위인가 ⓒ 칸을 넘어선 순서나 유일성이 필요한가를 본다. ⓐ가 없거나 ⓒ가 필요할 때만 GLOBAL을 쓴다.
파티션이 인덱스를 대신하기도 한다. ③처럼 칸 한두 개 안에서 많은 행을 읽는 조회는 인덱스 없이 칸 FULL이 낫다. 파티션 없는 테이블에서는 5,826블록을 읽던 조회다(교재 14화 §7: 파티션은 '한 칸만 열면' 될 뿐, 칸 안은 다시 인덱스 문제다).
①을 GLOBAL로 두어도 조회 블록은 같다(교재 14화 실측: LOCAL Prefixed 89 · 파티션 없는 커버링 91). 그렇다면 월 정리에 영향이 없는 LOCAL이 낫다.
②를 없애려면 고객센터 화면이 주문일을 같이 넘겨야 한다. 그러면 LOCAL (ORD_SEQ, ORD_DT) 유니크로 바꿀 수 있지만, 유일성의 뜻이 '주문번호와 주문일의 조합'으로 바뀌므로 채번 방식을 함께 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 운영 수치는 교재 02화 · 06화 · 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 운영 수치는 교재 02화 · 06화 · 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황