[Oracle] 테이블 Move 및 인덱스 Rebuild 방법
데이터베이스를 오래 운영하다 보면 데이터의 입력, 수정, 삭제가 반복되면서 물리적인 저장 공간에 효율성이 떨어지는 현상이 발생하는데, 이를 ‘조각화(Fragmentation)‘라고 합니다. 테이블 Move와 인덱스 Rebuild는 이러한 ‘조각화를 제거‘하기 위함입니다.
- 테이블 Move 방법
- 인덱스 Rebuild 방법
1. 테이블 Move 방법
- 테이블 Move가 필요한 이유: 테이블에 DELETE가 많이 발생해도 오라클은 할당된 공간을 자동으로 반환하지 않습니다.
- HWM(High Water Mark) 하향 조정: 데이터를 다 지워도 ‘여기까지 데이터를 썼었다’는 표시인 HWM은 내려가지 않습니다. Full Table Scan 시 데이터가 없는 빈 블록들까지 다 읽어야 하므로 성능이 저하됩니다. Move를 하면 이 표시를 실제 데이터 수준으로 낮춰줍니다.
- 공간 낭비 제거: 블록 내부에 듬성듬성 생긴 빈 구멍들을 메워 저장 공간을 절약합니다.
- Row Chaining / Migration 해소: 한 행의 데이터가 너무 길어져서 여러 블록에 흩어져 저장된 경우, 이를 한 블록으로 깔끔하게 모아 I/O 횟수를 줄여줍니다.
- WASTED_PCT가 30% 이상이라면 MOVE 작업을 통해 공간을 회수하고 HWM을 낮추는 것이 성능 상 유리합니다.
SELECT T.OWNER, T.TABLE_NAME, T.NUM_ROWS, ROUND((T.BLOCKS * 8192) / 1024 / 1024, 2) AS TOTAL_MB, -- 할당된 총 크기 ROUND((T.AVG_ROW_LEN * T.NUM_ROWS) / 1024 / 1024, 2) AS ACTUAL_MB, -- 실제 데이터 크기 ROUND(((T.BLOCKS * 8192) - (T.AVG_ROW_LEN * T.NUM_ROWS)) / 1024 / 1024, 2) AS WASTED_MB, -- 낭비되는 크기 CASE WHEN T.BLOCKS > 0 THEN ROUND((((T.BLOCKS * 8192) - (T.AVG_ROW_LEN * T.NUM_ROWS)) / (T.BLOCKS * 8192)) * 100, 2) ELSE 0 END AS WASTED_PCT -- 낭비율(%) FROM DBA_TABLES T WHERE T.OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW') AND T.BLOCKS > 100 -- 소규모 테이블 제외 AND CASE WHEN T.BLOCKS > 0 THEN ROUND((((T.BLOCKS * 8192) - (T.AVG_ROW_LEN * T.NUM_ROWS)) / (T.BLOCKS * 8192)) * 100, 2) ELSE 0 END > 30 -- WASTED_PCT 30% 이상 ORDER BY WASTED_MB DESC; - 오라클 11g 버전에서는 안타깝게도 ONLINE 옵션이 지원되지 않기 때문에, MOVE 작업 중에 테이블에 Exclusive Lock(배타적 잠금)이 걸려 다른 사용자의 SELECT는 가능하지만 INSERT/UPDATE/DELETE가 모두 차단됩니다. 서비스 중단 없이 테이블을 재구성해야 한다면, 오라클에서 제공하는 DBMS_REDEFINITION 패키지를 사용할 수 있는데, 과정이 복잡하고 실수할 위험이 있으므로, 새벽 시간대에 10~30분 정도의 짧은 중단이 가능하다면 아래처럼 수동으로 하는 것을 권장합니다. 테이블을 MOVE하기 전에 테이블스페이스에 충분한 공간이 있는지 체크하고, 테이블 한 개씩의 MOVE 작업을 수행합니다.
-- 1. 일반 테이블 (No Partition, No LOB) SELECT T.OWNER, T.TABLE_NAME, T.TABLESPACE_NAME, 'PARTITIONED_NO' AS TABLE_TYPE, 'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' MOVE;' AS MOVE_DDL FROM DBA_TABLES T WHERE T.OWNER = '스키마' AND T.TABLE_NAME = '테이블명' AND T.PARTITIONED = 'NO' AND NOT EXISTS (SELECT 1 FROM DBA_LOBS L WHERE L.OWNER = T.OWNER AND L.TABLE_NAME = T.TABLE_NAME) UNION ALL -- 2. 파티션 테이블 (Yes Partition, No LOB) SELECT T.OWNER, T.TABLE_NAME, T.TABLESPACE_NAME, 'PARTITIONED_YES' AS TABLE_TYPE, 'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' MOVE PARTITION ' || P.PARTITION_NAME || ';' AS MOVE_DDL FROM DBA_TABLES T JOIN DBA_TAB_PARTITIONS P ON T.OWNER = P.TABLE_OWNER AND T.TABLE_NAME = P.TABLE_NAME WHERE T.OWNER = '스키마' AND T.TABLE_NAME = '테이블명' AND T.PARTITIONED = 'YES' AND NOT EXISTS (SELECT 1 FROM DBA_LOBS L WHERE L.OWNER = T.OWNER AND L.TABLE_NAME = T.TABLE_NAME) UNION ALL -- 3. LOB 포함 일반 테이블 (No Partition, Yes LOB) SELECT T.OWNER, T.TABLE_NAME, T.TABLESPACE_NAME, 'PARTITIONED_NO_WITH_LOB' AS TABLE_TYPE, 'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' MOVE LOB (' || L.COLUMN_NAME || ') STORE AS (TABLESPACE ' || L.TABLESPACE_NAME || ');' AS MOVE_DDL FROM DBA_TABLES T JOIN DBA_LOBS L ON T.OWNER = L.OWNER AND T.TABLE_NAME = L.TABLE_NAME WHERE T.OWNER = '스키마' AND T.TABLE_NAME = '테이블명' AND T.PARTITIONED = 'NO' UNION ALL -- 4. LOB 포함 파티션 테이블 (Yes Partition, Yes LOB) -- 파티션된 LOB은 DBA_LOB_PARTITIONS에서 정보를 가져와야 정확합니다. SELECT T.OWNER, T.TABLE_NAME, T.TABLESPACE_NAME, 'PARTITIONED_YES_WITH_LOB' AS TABLE_TYPE, 'ALTER TABLE ' || T.OWNER || '.' || T.TABLE_NAME || ' MOVE PARTITION ' || LP.PARTITION_NAME || ' LOB (' || LP.COLUMN_NAME || ') STORE AS (TABLESPACE ' || LP.TABLESPACE_NAME || ');' AS MOVE_DDL FROM DBA_TABLES T JOIN DBA_LOB_PARTITIONS LP ON T.OWNER = LP.TABLE_OWNER AND T.TABLE_NAME = LP.TABLE_NAME WHERE T.OWNER = '스키마' AND T.TABLE_NAME = '테이블명' AND T.PARTITIONED = 'YES'; - 다음으로 작업한 테이블과 연관된 인덱스를 Rebuild해야 합니다.(LOB 타입은 테이블 MOVE 작업 시 “LOB( ) STORE AS ( )” 옵션을 사용하면 LOB 인덱스가 재구성됩니다.)
-- 1. 일반 및 비트맵 인덱스 (Non-Partitioned) SELECT I.OWNER, I.INDEX_NAME, I.INDEX_TYPE, I.TABLESPACE_NAME, I.PARTITIONED, 'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' REBUILD' || CASE WHEN I.INDEX_TYPE LIKE 'BITMAP%' THEN '' ELSE ' ONLINE' END || ';' AS REBUILD_DDL FROM DBA_INDEXES I WHERE I.TABLE_OWNER = '스키마' AND I.TABLE_NAME = '테이블명' AND I.PARTITIONED = 'NO' AND I.INDEX_TYPE NOT IN ('LOB', 'DOMAIN') UNION ALL -- 2. 파티션 인덱스 (Partitioned) SELECT I.OWNER, I.INDEX_NAME, I.INDEX_TYPE, I.TABLESPACE_NAME I.PARTITIONED, 'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' REBUILD PARTITION ' || P.PARTITION_NAME || CASE WHEN I.INDEX_TYPE LIKE 'BITMAP%' THEN '' ELSE ' ONLINE' END || ';' AS REBUILD_DDL FROM DBA_INDEXES I JOIN DBA_IND_PARTITIONS P ON I.OWNER = P.INDEX_OWNER AND I.INDEX_NAME = P.INDEX_NAME WHERE I.TABLE_OWNER = '스키마' AND I.TABLE_NAME = '테이블명' AND I.PARTITIONED = 'YES' AND I.INDEX_TYPE NOT IN ('LOB', 'DOMAIN') - 마지막으로 작업한 테이블의 통계 정보를 갱신합니다.(“통계 정보를 갱신하는 절차 : 3-1. 테이블 통계 갱신” 참고)
2. 인덱스 Rebuild 방법
인덱스 리빌드(Index Rebuild)는 물리적으로 깨진 인덱스를 복구하거나, 조각화(Fragmentation)가 심해져 저하된 성능을 회복하기 위해 수행합니다.
- DBA_INDEXES에서 LEAF_BLOCKS가 크면서 DENSITY_RATIO가 낮은 후보군을 추출합니다.
-- 1. 일반 인덱스 (Non-Partitioned) SELECT I.OWNER, I.INDEX_NAME, 'N/A' AS PARTITION_NAME, I.INDEX_TYPE, I.LEAF_BLOCKS, -- 1. 블록당 데이터 건수 (낮을수록 위험), 10 미만이면 심각한 조각화 의심. ROUND(I.NUM_ROWS / DECODE(I.LEAF_BLOCKS, 0, 1, I.LEAF_BLOCKS), 2) AS ROWS_PER_BLOCK, -- 2. 유니크 지수 (높을수록 리빌드 가치 높음), 1에 가까울수록 유니크하며, 리빌드 시 성능 개선 효과가 큼 ROUND(I.DISTINCT_KEYS / DECODE(I.NUM_ROWS, 0, 1, I.NUM_ROWS), 3) AS SELECTIVITY, -- 3. 종합 위험도 점수 (낮을수록 조각화 심함), 리프 블록 하나에 여러 개의 키 값이 들어갈 수 있으므로 1보다 큰 것이 일반적임 -- 이 수치가 0.1~0.2 이하로 떨어질 때를 심각한 조각화로 간주 ROUND(I.DISTINCT_KEYS / DECODE(I.LEAF_BLOCKS, 0, 1, I.LEAF_BLOCKS), 2) AS DENSITY_SCORE, 'ANALYZE INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' VALIDATE STRUCTURE;' AS ANALYZE_DDL FROM DBA_INDEXES I WHERE I.OWNER = 'TGMADM' AND I.LEAF_BLOCKS > 1000 AND I.PARTITIONED = 'NO' AND I.INDEX_TYPE NOT IN ('LOB', 'DOMAIN') -- 분석 불가 타입 제외 UNION ALL -- 2. 파티션 인덱스 (Partitioned) -- 파티션 인덱스는 파티션 단위로 VALIDATE를 수행해야 INDEX_STATS에 기록됩니다. SELECT I.OWNER, I.INDEX_NAME, P.PARTITION_NAME, I.INDEX_TYPE, P.LEAF_BLOCKS, ROUND(I.NUM_ROWS / DECODE(I.LEAF_BLOCKS, 0, 1, I.LEAF_BLOCKS), 2) AS ROWS_PER_BLOCK, ROUND(I.DISTINCT_KEYS / DECODE(I.NUM_ROWS, 0, 1, I.NUM_ROWS), 3) AS SELECTIVITY, ROUND(I.DISTINCT_KEYS / DECODE(I.LEAF_BLOCKS, 0, 1, I.LEAF_BLOCKS), 2) AS DENSITY_SCORE, 'ANALYZE INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' PARTITION ' || P.PARTITION_NAME || ' VALIDATE STRUCTURE;' AS ANALYZE_DDL FROM DBA_INDEXES I JOIN DBA_IND_PARTITIONS P ON I.OWNER = P.INDEX_OWNER AND I.INDEX_NAME = P.INDEX_NAME WHERE I.OWNER = 'TGMADM' AND P.LEAF_BLOCKS > 1000 AND I.PARTITIONED = 'YES' AND I.INDEX_TYPE NOT IN ('LOB', 'DOMAIN') ORDER BY DENSITY_SCORE ASC; ANALYZE INDEX ... VALIDATE STRUCTURE 명령을 실행하여 INDEX_STATS 뷰에 상세 정보를 쌓습니다.ANALYZE INDEX 스키마.인덱스명 [PARTITION 파티션명] VALIDATE STRUCTURE;
- INDEX_STATS 뷰는 현재 세션에서
ANALYZE ... VALIDATE STRUCTURE 명령를 실행했을 때만 데이터가 보입니다. WASTE_RATIO가 20%를 넘는 인덱스를 REBUILD 후보로 선정합니다.SELECT NAME, HEIGHT, LF_ROWS, -- 리프 블록(Leaf Block)에 현재 물리적으로 저장되어 있는 전체 엔트리(행)의 개수 DEL_LF_ROWS, -- 삭제된 행 수 (이 값이 많으면 공간 낭비 심함) ROUND((DEL_LF_ROWS_LEN / DECODE(LF_ROWS_LEN + DEL_LF_ROWS_LEN, 0, 1, LF_ROWS_LEN + DEL_LF_ROWS_LEN)) * 100, 2) AS WASTE_SPACE_PCT FROM INDEX_STATS WHERE ROUND((DEL_LF_ROWS_LEN / DECODE(LF_ROWS_LEN + DEL_LF_ROWS_LEN, 0, 1, LF_ROWS_LEN + DEL_LF_ROWS_LEN)) * 100, 2) > 20; ALTER INDEX ... REBUILD ... 명령을 실행하여 REBUILD를 진행합니다. PARALLEL 및 NOLOGGING 옵션 사용법은 “인덱스 저장용 테이블스페이스 정비 절차” 글을 참고해 주세요.ALTER INDEX 스키마.인덱스명 REBUILD [PARTITION 파티션명] ONLINE;
- 마지막으로 작업한 인덱스의 통계 정보를 갱신합니다.(“통계 정보를 갱신하는 절차 : 3-2. 인덱스 통계 갱신” 참고)
