PART-024

2022년 칸의 인덱스를 끄는 스크립트가 오류 없이 끝났는데 인덱스가 한 블록도 줄지 않았다

중급DDL한빛유통399 / 700

상황

오과장은 다음 달 2022년 칸 12개를 압축하기로 했다. 압축으로 칸을 옮겨 쓰면 그 칸의 로컬 인덱스도 다시 만들어야 하는데, 2022년 주문은 거의 조회되지 않으니 그 칸의 인덱스부터 없애겠다고 했다. 그래서 2022년 칸마다 INDEXING OFF를 주는 스크립트를 돌렸다. 스크립트는 오류 없이 끝났지만 인덱스 크기는 그대로다. 박팀장은 제대로 되게 고치되, 이 테이블을 읽는 화면들이 어떻게 되는지까지 정리해 오라고 했다.

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX, 모두 37칸. 2022년은 1 ~ 12번째 칸이다. 칸 하나를 FULL로 읽으면 약 161블록이다. 2022년 칸에는 DML이 없다.
LOCAL 인덱스 IX_P14_CUS_DT (CUS_ID, ORD_DT): 3,240블록(칸마다 약 90블록, 높이 2). LOCAL 인덱스 IX_P14_ST_DT (ORD_ST, ORD_DT): 2,880블록(칸마다 약 80블록). GLOBAL 유니크 인덱스 IX_P14_SEQ (ORD_SEQ): 2,100블록. 세 인덱스 모두 파티션 전환 때 INDEXING 절 없이 만들었다.
고객은 2,000명이고 고객마다 약 500건이 36칸에 고르게 흩어져 있다(한 해 약 167건). 행마다 다른 블록에 있다.
이 테이블을 읽는 SQL(V$SQL, 하루 기준)
- Q1 상담 「최근 주문」: CUS_ID = :c AND ORD_DT >= :v_from(최근 12개월), IX_P14_CUS_DT, 2만 번
- Q2 「처리 대기 모니터」: ORD_ST = 'PENDING' AND ORD_DT >= :v_from(최근 7일), IX_P14_ST_DT, 288번
- Q3 민원 대응 「고객 연도별 주문 요약」: CUS_ID = :c(전 기간), IX_P14_CUS_DT, 20번
- Q4 「주문번호 조회」: ORD_SEQ = :seq, IX_P14_SEQ, 3만 번

원본 SQL

sql

-- 파티션 전환 때 만든 인덱스 (INDEXING 절 없음)
CREATE INDEX IX_P14_CUS_DT ON T_ORD_BIG_P14 (CUS_ID, ORD_DT) LOCAL;
CREATE INDEX IX_P14_ST_DT ON T_ORD_BIG_P14 (ORD_ST, ORD_DT) LOCAL;
CREATE UNIQUE INDEX IX_P14_SEQ ON T_ORD_BIG_P14 (ORD_SEQ);

-- 오과장의 스크립트 (P202201 ~ P202212, 칸마다 한 줄)
ALTER TABLE T_ORD_BIG_P14 MODIFY PARTITION P202201 INDEXING OFF;
ALTER TABLE T_ORD_BIG_P14 MODIFY PARTITION P202202 INDEXING OFF;
-- … P202212 까지

-- Q3 「고객 연도별 주문 요약」
SELECT TO_CHAR(ORD_DT, 'YYYY') AS ord_yy, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG_P14
 WHERE CUS_ID = :v_cus
 GROUP BY TO_CHAR(ORD_DT, 'YYYY')

실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36)

xplan

[스크립트 뒤 · 사전 정보]
  USER_TAB_PARTITIONS   P202201 ~ P202212  INDEXING = OFF · P202301 ~ PMAX  INDEXING = ON
  USER_INDEXES          IX_P14_CUS_DT  INDEXING = FULL · IX_P14_ST_DT  INDEXING = FULL · IX_P14_SEQ  INDEXING = FULL
  USER_IND_PARTITIONS   IX_P14_CUS_DT, IX_P14_ST_DT 의 37칸 모두 USABLE
  USER_SEGMENTS         IX_P14_CUS_DT 3,240 → 3,240블록 · IX_P14_ST_DT 2,880 → 2,880블록 (스크립트 전 → 뒤)

[Q3 · 스크립트 전과 뒤가 같다]
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |      1 |        |      3 | 00:00:00.01 |     572 |        |       |
|   1 |  HASH GROUP BY                               |               |      1 |      3 |      3 | 00:00:00.01 |     572 |        |       |
|   2 |   PARTITION RANGE ALL                        |               |      1 |    500 |    500 | 00:00:00.01 |     572 |      1 |    37 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |     36 |    500 |    500 | 00:00:00.01 |     572 |      1 |    37 |
|*  4 |     INDEX RANGE SCAN                         | IX_P14_CUS_DT |     36 |    500 |    500 | 00:00:00.01 |      72 |      1 |    37 |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("CUS_ID"=:V_CUS)

문제

스크립트가 오류 없이 끝났는데도 인덱스가 줄지 않은 까닭을 사전 정보로 설명하고, 2022년 칸의 로컬 인덱스가 실제로 사라지게 하는 DDL을 쓰시오.
고친 뒤 Q1 ~ Q4가 각각 어떻게 되는지 쓰고, Q3의 실행계획과 Buffers가 어떻게 바뀌는지 설명하시오.
이 안으로 얻는 것과 잃는 것을 숫자로 정리하고, IX_P14_SEQ도 같은 방법으로 줄일 수 있는지와 칸의 인덱스를 끄기 전에 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 칸의 INDEXING OFF 는 INDEXING PARTIAL 로 만든 인덱스에만 적용된다 → 두 로컬 인덱스를 부분 인덱스로 다시 만든다
--     Q1 이 쉬는 야간에, DML 을 막지 않도록 ONLINE 으로
DROP INDEX IX_P14_CUS_DT;
CREATE INDEX IX_P14_CUS_DT ON T_ORD_BIG_P14 (CUS_ID, ORD_DT) LOCAL INDEXING PARTIAL ONLINE;
DROP INDEX IX_P14_ST_DT;
CREATE INDEX IX_P14_ST_DT ON T_ORD_BIG_P14 (ORD_ST, ORD_DT) LOCAL INDEXING PARTIAL ONLINE;
--     2022년 칸은 이미 INDEXING OFF → 그 칸의 인덱스 칸은 UNUSABLE, 세그먼트 없이 만들어진다

-- (2) IX_P14_SEQ 는 유니크라 부분 인덱스가 될 수 없다 → 그대로 둔다

-- (3) 월 정리 스크립트에 한 줄 : 24개월이 지난 칸은 인덱스를 끈다 (2025년 1월에는 P202301)
ALTER TABLE T_ORD_BIG_P14 MODIFY PARTITION P202301 INDEXING OFF;

-- (4) 확인
SELECT index_name, indexing FROM user_indexes WHERE table_name = 'T_ORD_BIG_P14';
SELECT index_name, partition_name, status, segment_created
  FROM user_ind_partitions
 WHERE index_name IN ('IX_P14_CUS_DT', 'IX_P14_ST_DT')
 ORDER BY index_name, partition_position;

개선 후 실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36)

xplan

[고친 뒤 · 사전 정보]
  USER_INDEXES          IX_P14_CUS_DT  INDEXING = PARTIAL · IX_P14_ST_DT  INDEXING = PARTIAL · IX_P14_SEQ  INDEXING = FULL
  USER_IND_PARTITIONS   두 로컬 인덱스의 P202201 ~ P202212  UNUSABLE · SEGMENT_CREATED = NO
  USER_SEGMENTS         IX_P14_CUS_DT 3,240 → 2,160블록 · IX_P14_ST_DT 2,880 → 1,920블록 (합쳐 2,040블록, 약 16MB 줄어듦)

[Q1 · Q2 · Q4]  인덱스가 켜진 칸만 읽으므로 계획과 Buffers 그대로

[Q3 · 고친 뒤 (테이블 확장)]
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |               |      1 |        |      3 | 00:00:00.03 |    2313 |        |       |
|   1 |  HASH GROUP BY                                 |               |      1 |      3 |      3 | 00:00:00.03 |    2313 |        |       |
|   2 |   VIEW                                         | VW_TE_2       |      1 |    500 |    500 | 00:00:00.03 |    2313 |        |       |
|   3 |    UNION-ALL                                   |               |      1 |        |    500 | 00:00:00.03 |    2313 |        |       |
|   4 |     PARTITION RANGE ITERATOR                   |               |      1 |    333 |    333 | 00:00:00.01 |     381 |     13 |    37 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |     24 |    333 |    333 | 00:00:00.01 |     381 |     13 |    37 |
|*  6 |       INDEX RANGE SCAN                         | IX_P14_CUS_DT |     24 |    333 |    333 | 00:00:00.01 |      48 |     13 |    37 |
|   7 |     PARTITION RANGE ITERATOR                   |               |      1 |    167 |    167 | 00:00:00.02 |    1932 |      1 |    12 |
|*  8 |      TABLE ACCESS FULL                         | T_ORD_BIG_P14 |     12 |    167 |    167 | 00:00:00.02 |    1932 |      1 |    12 |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("CUS_ID"=:V_CUS AND "ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - filter(("CUS_ID"=:V_CUS AND "ORD_DT"<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

사전 정보를 보면 칸은 INDEXING = OFF인데 세 인덱스는 모두 INDEXING = FULL이다. 칸의 INDEXING 속성은 INDEXING PARTIAL로 만든 인덱스(부분 인덱스)에만 적용된다. INDEXING 절 없이 만든 인덱스는 기본값 FULL이라 칸의 속성과 상관없이 모든 칸을 색인한다. 그래서 스크립트는 오류 없이 칸의 속성만 바꿨고, 두 로컬 인덱스의 2022년 칸 24개는 USABLE인 채로 남아 크기가 3,240블록과 2,880블록 그대로였다. 고치려면 두 로컬 인덱스를 LOCAL INDEXING PARTIAL로 다시 만든다. 2022년 칸은 이미 INDEXING OFF이므로 그 칸의 인덱스 칸은 UNUSABLE로, 세그먼트 없이 만들어진다. 인덱스는 3,240 → 2,160블록, 2,880 → 1,920블록으로 합쳐 2,040블록(약 16MB)이 준다. 다음 달 2022년 칸을 압축할 때 다시 만들 로컬 인덱스 칸도 없다. 다시 만드는 동안에는 Q1이 인덱스 없이 돌게 되므로 Q1이 쉬는 야간에 ONLINE으로 만든다. Q1(최근 12개월)과 Q2(최근 7일)는 인덱스가 켜진 칸만 읽으므로 그대로다. Q4는 IX_P14_SEQ로 그대로다. 유니크 인덱스는 부분 인덱스로 만들 수 없다(일부 칸을 빼고는 유일성을 지킬 수 없다). 그래서 IX_P14_SEQ는 FULL로 남고, 이 안으로 줄일 수 없다. 달라지는 것은 전 기간을 보는 Q3다. 옵티마이저는 테이블 확장으로 칸을 둘로 나눠 읽는다(VW_TE_2, UNION-ALL). 인덱스가 있는 13 ~ 37번 칸은 IX_P14_CUS_DT로 읽고(Starts 24, 381블록), 인덱스가 없는 2022년 1 ~ 12번 칸은 FULL로 읽는다(Starts 12, 1,932블록). 그래서 Q3는 572 → 2,313블록이 되고, 하루 20번이면 11,440 → 46,260블록이다. 정리하면 얻는 것은 인덱스 2,040블록과 2022년 칸을 압축할 때의 인덱스 재생성 시간이고, 잃는 것은 Q3가 하루 약 3만 5천 블록을 더 읽는 것이다. 하루 20번인 민원 조회라 받아들일 만하다. 칸의 인덱스를 끄기 전에는 V$SQL과 AWR에서 그 인덱스로 옛 칸을 읽는 SQL이 무엇이고 하루 몇 번 도는지 확인한다. Q3가 하루 2만 번이었다면 이 안은 맞지 않는다. 또 칸을 다시 켜면(INDEXING ON) 그 칸의 인덱스를 새로 만들어야 하므로 쉽게 오갈 설정이 아니다.

해설

다른 답: ALTER INDEX IX_P14_CUS_DT MODIFY PARTITION P202201 UNUSABLE로 인덱스 칸을 하나씩 깨뜨려도 세그먼트가 사라지고 Q3는 같은 테이블 확장 계획이 된다. 다만 그것은 선언이 아니라 상태라서, 깨진 인덱스 칸을 찾아 다시 만드는 점검 스크립트(PART-022의 REBUILD 루프)가 언제든 되살린다. 부분 인덱스는 칸의 속성으로 남는다.
GLOBAL 인덱스도 부분 인덱스가 될 수 있다. GLOBAL … INDEXING PARTIAL이나 파티션하지 않은 인덱스에 INDEXING PARTIAL을 주면 INDEXING OFF 칸의 행은 인덱스에 넣지 않는다. 유니크가 아니어야 한다는 조건은 같다.
테이블 확장은 칸에 걸친 조회에서만 생긴다. 인덱스가 꺼진 칸만 보는 조회(2022년 한 고객의 주문)는 그 칸들을 FULL로 읽고, 켜진 칸만 보는 조회(Q1, Q2)는 지금과 같다.
보관 36개월 가운데 최근 24개월만 색인한다는 규칙은 월 정리 스크립트에 넣는다. 새 칸은 테이블의 기본값(INDEXING ON)을 따르므로 손댈 일이 없고, 24개월이 지난 칸에 INDEXING OFF를 주는 한 줄만 더한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황