[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-1. TABLE, TABLE PARTITION, TABLE SUBPARTITION 타입의 테이블을 MOVE합니다. -- 컬럼 중에 LONG 타입이 있다면 에러가 나므로 CLOB으로 변경 후 실행해야 합니다. -- MOVE 실행 시 NOLOGGING옵션은 "SELECT FORCE_LOGGING FROM V$DATABASE;" 결과가 "NO"일 때만 적용됩니다. -- 관련 인덱스가 없다면 cascade=>FALSE 옵션을 사용하여 테이블만 통계정보를 갱신합니다. -- 관련 인덱스가 있다면 해당 인덱스는 UNUSABLE 상태가 되므로, 인덱스를 REBUILD한 후 인덱스 통계 정보와 함께 갱신합니다.(아래 2단계 확인) SELECT S.OWNER, S.SEGMENT_NAME, S.PARTITION_NAME, S.SEGMENT_TYPE, S.TABLESPACE_NAME, ROUND(S.BYTES/1024/1024, 2) AS SEGMENT_MB, T.STATUS AS TABLE_STATUS, 'ALTER TABLE ' || S.OWNER || '."' || S.SEGMENT_NAME || '" MOVE ' || CASE WHEN S.SEGMENT_TYPE = 'TABLE' THEN '' WHEN S.SEGMENT_TYPE = 'TABLE PARTITION' THEN 'PARTITION "' || S.PARTITION_NAME || '" ' ELSE 'SUBPARTITION "' || S.PARTITION_NAME || '" ' END || 'TABLESPACE [TARGET_TS] PARALLEL 4 NOLOGGING;' AS MOVE_DDL, 'ALTER TABLE ' || S.OWNER || '."' || S.SEGMENT_NAME || '" NOPARALLEL LOGGING;' AS RESET_DDL, 'EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>''' || S.OWNER || ''', tabname=>''' || S.SEGMENT_NAME || ''', ' || 'cascade=>FALSE, degree=>4, no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);' AS TABLE_STATS_DDL FROM DBA_SEGMENTS S, DBA_TABLES T WHERE S.OWNER = T.OWNER AND S.SEGMENT_NAME = T.TABLE_NAME AND S.OWNER = '스키마' AND S.SEGMENT_NAME = '테이블명' AND S.SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') ORDER BY S.OWNER, S.SEGMENT_NAME; -- 1-2. LOB컬럼을 포함하고 있는 테이블을 MOVE합니다. -- LOB타입 테이블을 MOVE하면 LOB컬럼과 연관된 LOB INDEX도 자동으로 REBUILD되므로 2단계에서 제외합니다. -- 한 테이블에 LOB컬럼이 여러 개 있는 경우, RESET_DDL과 STATS_DDL이 중복되므로 확인 후 실행하시기 바랍니다. -- cascade=>TRUE 옵션을 사용해서 관련 인덱스 통계 정보도 함께 갱신합니다. SELECT L.OWNER, L.TABLE_NAME, L.COLUMN_NAME, LP.PARTITION_NAME, L.TABLESPACE_NAME, L.INDEX_NAME, L.PARTITIONED, LP.COMPOSITE, 'ALTER TABLE ' || L.OWNER || '."' || L.TABLE_NAME || '" ' || CASE WHEN LP.COMPOSITE <> 'NO' THEN '-- CHECK DBA_LOB_PARTITIONS VIEW' WHEN L.PARTITIONED = 'NO' THEN 'MOVE LOB ("' || L.COLUMN_NAME || '") ' ELSE 'MOVE PARTITION "' || LP.PARTITION_NAME || '" LOB ("' || L.COLUMN_NAME || '") ' END || 'STORE AS (TABLESPACE [TARGET_TS]) PARALLEL 4 NOLOGGING;' AS MOVE_DDL, 'ALTER TABLE ' || L.OWNER || '."' || L.TABLE_NAME || '" NOPARALLEL LOGGING;' AS RESET_DDL, 'EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>''' || L.OWNER || ''', tabname=>''' || L.TABLE_NAME || ''', ' || 'cascade=>TRUE, degree=>4, no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);' AS LOB_STATS_DDL FROM DBA_LOBS L, DBA_LOB_PARTITIONS LP WHERE L.OWNER = LP.TABLE_OWNER(+) AND L.TABLE_NAME = LP.TABLE_NAME(+) AND L.COLUMN_NAME = LP.COLUMN_NAME(+) AND L.OWNER = '스키마' AND L.TABLE_NAME = '테이블명'; - 테이블을 MOVE하는 순간, 해당 테이블에 걸린 모든 인덱스는 UNUSABLE 상태가 되므로 작업한 테이블과 연관된 모든 인덱스를 Rebuild해야 합니다.(LOB 타입은 테이블 MOVE 작업 시 “LOB( ) STORE AS ( )” 옵션을 사용하면 LOB 인덱스가 재구성됩니다.)
-- 2. 1-1단계에서 MOVE한 테이블과 연관된 INDEX를 [REBUILD] -> [RESET] -> [통계수집] 절차를 거칩니다. -- LOB컴럼을 가지고 있는 테이블의 NORMAL INDEX는 자동으로 REBUILD되지 않으므로 2단계 작업에 포함되었습니다. -- TABLE MOVE를 실행하면 관련 INDEX가 DBA_SEGMENTS뷰에서는 사라지기 때문에 DBA_INDEXES뷰를 사용했습니다. -- SUBPARTITION이 있는 경우는 DBA_IND_SUBPARTITIONS뷰를 추가로 확인해야 합니다. -- cascade=>TRUE 옵션을 사용해서 테이블 통계 정보와 함께 관련 인덱스 통계 정보도 갱신합니다. SELECT I.OWNER, I.INDEX_NAME, I.INDEX_TYPE, I.STATUS AS INDEX_STATUS, ROUND(I.LEAF_BLOCKS*8192/1024/1024,2) AS ESTIMATED_MB, IP.PARTITION_NAME, IP.SUBPARTITION_COUNT, IP.STATUS AS IND_PART_STATUS, I.TABLE_OWNER, I.TABLE_NAME, CASE WHEN NVL(IP.SUBPARTITION_COUNT, 0) > 0 THEN '-- CHECK DBA_IND_SUBPARTITIONS VIEW' ELSE 'ALTER INDEX ' || I.OWNER || '."' || I.INDEX_NAME || '" REBUILD ' || CASE WHEN IP.PARTITION_NAME IS NULL THEN '' ELSE 'PARTITION "' || IP.PARTITION_NAME || '" ' END END || 'TABLESPACE [TARGET_TS] PARALLEL 4 NOLOGGING;' AS MOVE_DDL, 'ALTER INDEX ' || I.OWNER || '."' || I.INDEX_NAME || '" NOPARALLEL LOGGING;' AS RESET_DDL, 'EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>''' || I.TABLE_OWNER || ''', tabname=>''' || I.TABLE_NAME || ''', ' || 'cascade=>TRUE, degree=>4, no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);' AS ALL_STATS_DDL -- 'EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>''' || I.OWNER || ''', indname=>''' || I.INDEX_NAME || ''', ' || -- 'degree=>4, no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);' AS INDEX_STATS_DDL FROM DBA_INDEXES I, DBA_IND_PARTITIONS IP WHERE I.OWNER = IP.INDEX_OWNER(+) AND I.INDEX_NAME = IP.INDEX_NAME(+) AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP') AND I.TABLE_OWNER = '스키마' AND I.TABLE_NAME = '테이블명';
2. 인덱스 Rebuild 방법
인덱스 리빌드(Index Rebuild)는 물리적으로 깨진 인덱스를 복구하거나, 조각화(Fragmentation)가 심해져 저하된 성능을 회복하기 위해 수행합니다.
- DBA_INDEXES 뷰에서 LEAF_BLOCKS가 크면서 DENSITY_RATIO가 낮은 인덱스를 대상으로
ANALYZE INDEX 명령어를 실행하여 인덱스 내부 구조의 ‘조각화(Fragmentation)’를 진단합니다.-- 1-1. 일반 인덱스 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 = '스키마' AND I.LEAF_BLOCKS > 1000 AND I.PARTITIONED = 'NO' AND I.INDEX_TYPE NOT IN ('LOB', 'DOMAIN') -- 분석 불가 타입 제외 UNION ALL -- 1-2. 파티션 인덱스 (Partitioned) -- 파티션 인덱스는 파티션 단위로 VALIDATE를 수행해야 INDEX_STATS에 기록됩니다. SELECT I.OWNER, I.INDEX_NAME, P.PARTITION_NAME, I.INDEX_TYPE, P.LEAF_BLOCKS, ROUND(P.NUM_ROWS / DECODE(P.LEAF_BLOCKS, 0, 1, P.LEAF_BLOCKS), 2) AS ROWS_PER_BLOCK, ROUND(P.DISTINCT_KEYS / DECODE(P.NUM_ROWS, 0, 1, P.NUM_ROWS), 3) AS SELECTIVITY, ROUND(P.DISTINCT_KEYS / DECODE(P.LEAF_BLOCKS, 0, 1, P.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 = '스키마' 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 후보로 선정합니다.-- 2. 인덱스 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; - 선정된 REBUILD 대상 인덱스를 [인덱스 REBUILD] -> [REBUILD 옵션 원복] -> [통계정보 갱신] 절차를 진행합니다.
-- 3. INDEX 단독으로 REBUILD를 할 경우 -- COMPOSITE = 'YES'는 SUPPARTITION이 있는 것이므로 DBA_IND_SUBPARTITIONS뷰까지 확인하셔야 합니다. SELECT S.OWNER, S.SEGMENT_NAME, S.PARTITION_NAME, S.SEGMENT_TYPE, S.TABLESPACE_NAME, ROUND(S.BYTES/1024/1024, 2) AS SEGMENT_MB, I.STATUS AS INDEX_STATUS, 'ALTER INDEX ' || I.OWNER || '."' || I.INDEX_NAME || '" REBUILD ' || CASE WHEN S.SEGMENT_TYPE = 'INDEX' THEN '' WHEN S.SEGMENT_TYPE = 'INDEX PARTITION' THEN 'PARTITION "' || S.PARTITION_NAME || '" ' ELSE 'SUBPARTITION "' || S.PARTITION_NAME || '" ' END || 'TABLESPACE [TARGET_TS] PARALLEL 4 NOLOGGING;' AS REBUILD_DDL, 'ALTER INDEX ' || I.OWNER || '."' || I.INDEX_NAME || '" NOPARALLEL LOGGING;' AS RESET_DDL, 'EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>''' || I.OWNER || ''', indname=>''' || I.INDEX_NAME || ''', ' || 'degree=>4, no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);' AS STATS_DDL FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = I.OWNER AND S.SEGMENT_NAME = I.INDEX_NAME AND I.OWNER = '스키마' AND I.INDEX_NAME = '인덱스명' AND S.SEGMENT_TYPE IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') ORDER BY S.OWNER, S.SEGMENT_NAME;
