2022년 판매를 지운 다음 날부터 가장 오래된 판매일 한 칸이 인덱스를 700블록 읽는다

중급SELECT유통139 / 700

상황

본사 레거시 유통은 판매 보관 기간을 1년으로 줄이면서, 지난 토요일 밤 CF10에서 2022년 판매 250,412건을 DELETE 한 문장으로 지웠다. 월요일 아침부터 본사 「보관 현황」 화면이 굼뜨다는 말이 나왔다. 화면 맨 위 칸은 보관 중인 가장 오래된 판매일인데, 5분마다 새로 고쳐 하루 약 290번 실행된다. 조민우는 이렇게 말했다. "많이 지워서 인덱스가 망가졌다. 이참에 매주 일요일 인덱스 REBUILD 작업을 걸자."

테이블과 인덱스

CF10 판매: 지우기 전 500,000행 · 4,583블록 · 판매 날짜순으로 쌓였다 · 지금 249,588행(2023-01-01 ~ 2023-12-31) · POS가 새 판매를 하루 약 700건 INSERT하며, 새 판매일은 늘 지금 있는 값보다 크다
IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393블록(교재 03화 실측, 리프 한 블록에 약 359항목) · 「일별 판매 목록」(하루 706건에 18블록)도 이 인덱스를 쓴다
지운 2022년 판매의 항목은 IX_CF10_SLE_DT의 왼쪽 리프 약 697블록을 채우고 있었다 · CF10_PK (SLE_NO)도 SLE_NO가 판매일로 시작하므로 왼쪽 절반이 같은 모양이다
테이블스페이스는 ASSM이고, 인덱스는 모두 LOGGING이다

원본 SQL

sql

SELECT MIN(SLE_DT) AS old_dt
  FROM CF10

실행계획 학습용 예시 · 인덱스 규모는 교재 03화 실측

xplan

[지우기 전 · 지난 금요일]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE            |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IX_CF10_SLE_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------

[지운 뒤 · 월요일]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |      1 |        |      1 |00:00:00.03 |     700 |
|   1 |  SORT AGGREGATE            |                |      1 |      1 |      1 |00:00:00.03 |     700 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IX_CF10_SLE_DT |      1 |      1 |      1 |00:00:00.03 |     700 |
-----------------------------------------------------------------------------------------------------

[같은 월요일 「일별 판매 목록」 2023-12-15 · IX_CF10_SLE_DT 범위 스캔]  Buffers 18 (지우기 전과 같다)

[일요일에 뜬 복사본에서 ANALYZE INDEX IX_CF10_SLE_DT VALIDATE STRUCTURE → INDEX_STATS]
  HEIGHT 3 · LF_BLKS 1,393 · LF_ROWS 500,000 · DEL_LF_ROWS 250,412

문제

행은 절반으로 줄었는데 가장 오래된 판매일 한 칸이 3블록에서 700블록이 된 이유를 INDEX FULL SCAN (MIN/MAX)이 리프를 읽는 순서와 INDEX_STATS로 설명하시오. 같은 인덱스를 쓰는 「일별 판매 목록」은 왜 그대로인지도 설명하시오.
이 인덱스를 고치는 SQL을 작성하시오. COALESCE, SHRINK SPACE, REBUILD가 각각 무엇을 하고 무엇이 다른지 쓰시오.
조민우의 제안(매주 REBUILD)을 받아들일지 판단하시오. 그냥 두면 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 크게 지운 뒤 한 번 — 지운 항목만 남은 리프를 합쳐 인덱스 구조에서 걷어 낸다 (온라인 · 인덱스 하나에 수 초)
ALTER INDEX IX_CF10_SLE_DT COALESCE;
ALTER INDEX CF10_PK COALESCE;            -- SLE_NO 도 판매일로 시작해 왼쪽 절반이 같은 모양으로 비었다
-- 세그먼트 공간까지 돌려받으려면 : ALTER INDEX IX_CF10_SLE_DT SHRINK SPACE;

SELECT MIN(SLE_DT) AS old_dt             -- SQL 은 그대로
  FROM CF10;

-- 매주 REBUILD 작업은 만들지 않는다 · 보관 정리 프로시저의 DELETE 뒤에 COALESCE 두 줄을 붙인다

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 03화 실측

xplan

[COALESCE 뒤]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE            |                |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IX_CF10_SLE_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------

[복사본 INDEX_STATS]  LF_BLKS 1,393 → 697 · DEL_LF_ROWS 250,412 → 0 · 세그먼트 크기는 그대로(SHRINK SPACE를 하면 준다)

모범 답안 서술

INDEX FULL SCAN (MIN/MAX)은 인덱스의 맨 왼쪽 리프로 내려간 뒤, 살아 있는 첫 항목이 나올 때까지 오른쪽으로 리프를 따라간다. 지우기 전에는 맨 왼쪽 리프의 첫 항목이 답이라 루트 1 + 브랜치 1 + 리프 1 = 3블록이었다. DELETE는 인덱스에서도 2022년 항목 250,412개에 지운 표시만 남긴다(INDEX_STATS의 DEL_LF_ROWS 250,412, LF_BLKS는 1,393 그대로). 항목이 다 지워진 리프 블록은 다시 쓸 수 있다고 표시되지만, 다시 쓰이기 전까지는 인덱스 구조에 그대로 이어져 있다. 그래서 MIN을 찾으려고 지운 항목만 남은 왼쪽 리프 약 697블록을 모두 지나간 뒤에야 2023-01-01 항목을 만난다(2 + 697 + 1 = 700). 「일별 판매 목록」은 2023-12-15 항목이 있는 리프로 바로 내려가 그 날짜만 읽으므로 빈 블록을 지나지 않는다(18블록 그대로). 빈 리프가 문제 되는 것은 인덱스 왼쪽 끝부터 읽는 MIN/MAX 스캔과 INDEX FULL SCAN, 지운 구간을 걸치는 범위 조건이다. 고치는 방법은 셋이다. COALESCE는 제자리에서 이웃한 리프의 항목을 합치고, 빈 블록을 구조에서 떼어 다시 쓸 수 있게 돌려놓는다. 온라인으로 돌고 잠금이 짧지만 세그먼트 크기는 그대로다. SHRINK SPACE는 거기에 더해 남는 공간을 테이블스페이스에 돌려준다(ASSM에서만 된다). REBUILD는 인덱스를 처음부터 새로 짓는다. 결과는 가장 깔끔하지만 새 세그먼트를 지을 공간과 인덱스 크기만큼의 redo가 든다. 지금 필요한 것은 왼쪽의 빈 리프를 걷어 내는 일이므로 COALESCE면 충분하다. 고친 뒤 MIN 칸은 700 → 3블록이다. CF10_PK도 같은 모양이라 함께 한다. 매주 REBUILD는 받아들이지 않는다. 빈 리프가 생긴 원인은 매주 일어나는 일이 아니라 2022년을 한 번에 지운 일이다. 그래서 정리 프로시저의 DELETE 뒤에 COALESCE를 붙이는 것으로 충분하다. 그냥 두면 빈 블록은 다른 곳에서 블록이 쪼개질 때 다시 쓰이는데, 새 판매는 하루 약 700건(리프 약 2블록)만 오른쪽 끝에 붙는다. 697블록이 다 다시 쓰이려면 1년 가까이 걸리고, 그동안 이 칸은 하루 290번 × 약 700블록을 읽는다.

해설

REBUILD도 답이다: 결과(3블록)는 같다. 다만 새 세그먼트를 다 지을 때까지 옛 인덱스와 새 인덱스가 함께 공간을 차지하고, LOGGING 인덱스라 인덱스 크기만큼 redo가 나온다. 지운 쪽만 손보면 되는 지금은 COALESCE가 싸다.
VALIDATE STRUCTURE 주의: ANALYZE INDEX … VALIDATE STRUCTURE는 도는 동안 그 테이블의 DML을 막는다. POS가 INSERT하는 운영 테이블이라면 복사본에서 재거나 영업이 끝난 뒤에 한다. ONLINE을 붙이면 DML은 막지 않지만 INDEX_STATS를 채우지 않는다.
테이블도 그대로다: 테이블의 고수위선은 4,583블록 그대로라서, CF10을 FULL로 읽는 SQL은 행이 절반인데도 4,583블록을 읽는다(교재 05화). 테이블은 ENABLE ROW MOVEMENT 뒤 SHRINK SPACE로 따로 정한다.
다음 정리부터는: 해마다 1년 치를 지우는 테이블이라면 판매일로 나눈 파티션을 두고 DROP PARTITION으로 지우는 편이 빈 블록도 redo도 남기지 않는다(교재 14화 §11).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모는 교재 03화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 03화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황