Table Reorg 방법 정리

운영시에 Reorg를 해야하는 경우는 자주 있을 수 있습니다.

용량이슈는 늘 DB운영시에 따라오는 부분이니깐요. 특히 DML이 잦은 테이블의 크기가 실제 데이터블록에 비해 크기가 커질 수 있습니다.

그리고 이전에 글에도 reorg 관련 글은 몇개 올렸던 기억이 있는데

이번에는 Oracle SR을 토대로 정리해보겠습니다.

 

출처 : Oracle SR 

Document 151588.1 how to reorg a table.pdf

0.16MB

 

 

 

1.CTAS

사용방법

1) 테이블 구조(METADATA)를 백업(EXP)

아래의 명령어(EXP or EXPDP)로 백업 혹은 스크립트 형태로 백업 받아놓습니다.

 exp username/password file=test.dmp tables=test rows=n
 (Or) 
 expdp username/password directory=<dirname> dumpfile=test.dmp tables=test

 

2) 원본테이블로 부터 Target Table로 Copy

 create table new_test as select * from test;

3)원본 테이블 및 index 와 constraint 삭제

(주의 : 이 명령어는 관련 index와 constraint 를 모두 삭제합니다)

drop table test cascade constraints;

4)Target Table Rename

 alter table new_test rename to test;

5)Index 및 constraint 재생성

(주의 : imp 시 table이 이미 있어서 error가 발생할 수 있습니다. 무시해도 됩니다)

 imp username/password file=test.dmp ignore=y

 

장점

1) 사용이 쉽습니다.

2) 사용 중에도 원본 테이블에 대한 DML이 가능합니다.(CTAS가 시작된 이후의 DML은 copy 되지 않음)

 

단점

1) 느린 작업 속도

2) 추가 Tablespace 내의 작업 공간 필요 

3) CTAS가 시작되고 나서 DML은 Target Table의 데이터가 기록되지 않습니다.

4) Target Table에 Index과 Constraint를 추가로 생성해야 합니다.

 

2.EXPDP/IMPDP (EXP/IMP)

사용방법

1)EXP or EXPDP 하기

exp username/password file=test.dmp tables=test 
(Or)
expdp username/password directory=<dirname> dumpfile=test.dmp tables=test

2)원본 테이블과 Index 및 constraint Drop

(주의 : 이 명령어는 관련 index와 constraint 를 모두 삭제합니다)

drop table test cascade constraints;

 

3)테이블에 IMP 하기

imp username/password file=test.dmp

 

장점

1)다른 reorg 방법과 비교했을 때 빠릅니다

2)IMP 전에 추가 Tablespace 내의 공간이 필요하지 않습니다. 

3)전체 테이블스페이스를 reorg할때 추천합니다.

4)재구성할 Object가 많을때 사용을 추천합니다.

단점

1)테이블 가용성에 영향을 받습니다. 계획 후에 downtime이 필요합니다.

2) EXP 후에 추가된 DML 데이터는 IMP되지 않습니다. (IMP 직전에 다시 EXP하면 유실 데이터를 최소화 할 수 있습니다)

 

3.ALTER TABLE MOVE TABLESPACE

사용방법

alter table TEST.TEST move tablespace TS_TEST;

alter index TEST.PK_TEST  rebuild tablespace TS_TEST;

장점

1)Shrink와 비교했을때 특히 빠른 작업속도를 보여줍니다

2) 명령어가 비교적 간단합니다.

 

단점

1)해당 작업 중에는 DML작업이 가능하지 않습니다.

2) 추가 Tablespace 내의 작업 공간 필요 

3) INDEX rebuild를 manually 하게 수행해야 합니다.

 

이전에 쓴 글 

https://bae9086.tistory.com/120

Reorg 방법 - Shrink 와 Move

+) ONLINE 옵션

아래의 테이블에 사용가능합니다.

-> Table에 대해서 Reorg 시 ONLINE 옵션을 사용하여 테이블의 primary key를 재구성하는 도중 DML 작업을 할 수 있습니다.

-> Partition 및 Sub-Partition Table의 Reorg시에 ONLINE 옵션 사용가능합니다.

제약사항)

1.작업 중 Parallel DML이나 Direct path DML은 지원되지 않습니다.

2.도메인 인덱스가 있는 테이블은 지원되지 않습니다.

3. IOT 테이블은 지원되지 않습니다.

 

4.SHRINK

사용방법

ASSM 세그먼트에서만 사용할 수 있습니다. 하지만 online으로 DML 동작 중에도 Shrink 사용할 수 있습니다.

 Conventional DML는 사용가능 합니다. parallel DML은 사용불가합니다.

Shrink 단계에서는 개체의 가용성에 큰 영향을 주지 않습니다. 그러나 세그먼트가 축소되는 특정 단계(HWM이 조정되는 경우) 중에는 세그먼트를 배타적 모드로 잠가야 합니다. 이 단계는 매우 짧으며 해당 기간 동안의 가용성이 미치는 영향은 낮습니다.

1.테이블의 row movement 활성화
SQL> ALTER TABLE <table name> ENABLE ROW MOVEMENT;

2. 테이블 Shrink 하지만 HWM은 Shrink 하지 않기
SQL> ALTER TABLE <table name> SHRINK SPACE COMPACT;


3. 테이블 Shrink 와 HWM Shrink
SQL> ALTER TABLE <table name> SHRINK SPACE;

4. 테이블 Shrink 와 모든 연관된 index
SQL> ALTER TABLE <table name> SHRINK SPACE CASCADE;

5.MView에 있는 테이블 Shrink
SQL> ALTER TABLE <table name> SHRINK SPACE;

6.Index만 Shrink
SQL> ALTER INDEX <index name> SHRINK SPACE;

 

장점

1) 해당 작업중에 DML이 가능합니다.

2) 사용이 비교적 간단합니다

3) insert/delete가 쌍으로 이용되어 압축되므로 추가 tablespace 공간이 필요하지 않습니다.

4)CASCADE 옵션을 사용하여 INDEX를 자동으로 rebuild 해줍니다.

 

단점

1)Redo가 많이 발생됩니다.

2)큰 테이블의 Shrink를 수행할 때 수행시간이 오래걸립니다.

3) Reorg시 같은 Tablespace안에서만 가능합니다. 다른 Tablespace로의 MOVE는 불가능합니다.

 

이전에 쓴 글

https://bae9086.tistory.com/120

Reorg 방법 - Shrink 와 Move

5.ONLINE REDEFINITION OF TABLE

사용방법

가용성에 영챵을 미치지 않고 REORG 작업을 하는 방법입니다.

추가적인 제약사항이 많으므로 확인 후 사용하셔야 합니다.

 

0.테스트용 테이블 생성 및 단편화 강제로 만들기

테이블 생성
CREATE TABLE REF_ORIGINAL(A VARCHAR2(20), B NUMBER, C NUMBER,D VARCHAR2(30));

인덱스 생성
CREATE INDEX IND_REF_ORIGINAL ON REF_ORIGINAL(A);
CREATE INDEX IND_REF_ORIGINAL2 ON REF_ORIGINAL(B);
CREATE INDEX IND_REF_ORIGINAL3 ON REF_ORIGINAL(C);
CREATE INDEX IND_REF_ORIGINAL4 ON REF_ORIGINAL(D);

데이터 삽입
DECLARE

TYPE tbl_ins IS TABLE OF REF_ORIGINAL%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 1..5000000 LOOP
   w_ins(i).A :=dbms_random.string('x',10);
   w_ins(i).B :=i;
   w_ins(i).C :=MOD(i,5);
   w_ins(i).D :='ABC'||dbms_random.string('x',10);

END LOOP;
   FORALL i in 1..5000000 INSERT INTO REF_ORIGINAL VALUES w_ins(i);
   COMMIT;
END;

/

데이터 삭제
DELETE FROM REF_ORIGINAL where C=3;

COMMIT;

다시 데이터 입력
begin
    for i in 1..1000000 LOOP
    INSERT INTO REF_ORIGINAL(A,B,C,D) VALUES('AFTER FRAGMENTATION',i + 5000000,mod(i,5),'a');
  
    END LOOP;
    commit;
END;
/

데이터 삭제
DELETE FROM REF_ORIGINAL where C=2;

COMMIT;

데이터 다시 입력
begin
    for i in 1..1000000 LOOP
    INSERT INTO REF_ORIGINAL(A,B,C,D) VALUES('FRAGMENTATION2 ',i + 6000000,mod(i,5),'a');
  
    END LOOP;
    commit;
END;
/

1)ONLINE REDEFINITION 가능여부 체크 (실제 명령어는 1번부터 수행하면 됩니다.)

첫번째 인자값은 스키마 두번째는 스키마는 테이블입니다.

BEGIN
 DBMS_REDEFINITION.CAN_REDEF_TABLE('YSBAE','REF_ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

 

2)NEW 테이블 생성

CREATE TABLE REF_NEW (A VARCHAR2(20), B NUMBER, C NUMBER,D VARCHAR2(30));

 

3)ONLINE REDEFINITION 시작

NEW 테이블은 미리 생성되어 있어야 하며, 같은 Tablespace 내에 존재해야 합니다. 

 

BEGIN
 DBMS_REDEFINITION.START_REDEF_TABLE(
 uname => 'YSBAE',
 orig_table => 'REF_ORIGINAL',
 int_table => 'REF_NEW',
 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

확인

SQL> select count(*) from REF_ORIGINAL
UNION ALL
select count(*) from REF_NEW;

COUNT(*)         
---------------- 
         4800000
         4800000

2 rows selected.

4)DEPENDENT OBJECT COPY

 

DECLARE
 error_count pls_integer := 0;
BEGIN
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('YSBAE', 'REF_ORIGINAL', 'REF_NEW‘, dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
 DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

확인

기존 테이블에는 4개의 인덱스가 있었고, 그것이 옮겨졌는지 확인

원본 
SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where table_name='REF_ORIGINAL';

INDEX_NAME                                                                                                                       INDEX_TYPE                  
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- 
IND_REF_ORIGINAL                                                                                                                 NORMAL                     
IND_REF_ORIGINAL2                                                                                                                NORMAL                     
IND_REF_ORIGINAL3                                                                                                                NORMAL                     
IND_REF_ORIGINAL4                                                                                                                NORMAL                     

4 rows selected.

NEW 테이블
SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where table_name='REF_NEW';

INDEX_NAME                                                                                                                       INDEX_TYPE                  
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- 
I_SNAP$_REF_NEW                                                                                                                  NORMAL                     
TMP$$_IND_REF_ORIGINAL0                                                                                                          NORMAL                     
TMP$$_IND_REF_ORIGINAL20                                                                                                         NORMAL                     
TMP$$_IND_REF_ORIGINAL30                                                                                                         NORMAL                     
TMP$$_IND_REF_ORIGINAL40                                                                                                         NORMAL                     

5 rows selected.

 

 

5)SYNC

SYNC_INTERIM_TABLE 프로시저는 다르게 재정의 중에도 동기화를 수행할 수 있으며, 재정의를 종료하는 시간을 줄일 수 있다.

BEGIN
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('YSBAE', 'REF_ORIGINAL', 'REF_NEW');
END;
/

6) 종료

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('YSBAE', 'REF_ORIGINAL', 'REF_NEW');

7)RENAME

완료되었지만 INDEX이름은 변경되지 않았습니다.

SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where table_name='REF_NEW';

INDEX_NAME                                                                                                                       INDEX_TYPE                  
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- 
TMP$$_IND_REF_ORIGINAL0                                                                                                          NORMAL                     
TMP$$_IND_REF_ORIGINAL20                                                                                                         NORMAL                     
TMP$$_IND_REF_ORIGINAL30                                                                                                         NORMAL                     
TMP$$_IND_REF_ORIGINAL40                                                                                                         NORMAL                     

4 rows selected.

원본 삭제
DROP TABLE REF_ORIGINAL cascade constraints;

NEW 테이블 RENAME
ALTER TABLE  YSBAE.REF_NEW RENAME TO REF_ORIGINAL;

NEW 인덱스 RENAME
ALTER INDEX YSBAE.TMP$$_IND_REF_ORIGINAL0 RENAME TO IND_REF_ORIGINAL;
ALTER INDEX YSBAE.TMP$$_IND_REF_ORIGINAL20 RENAME TO IND_REF_ORIGINAL2;
ALTER INDEX YSBAE.TMP$$_IND_REF_ORIGINAL30 RENAME TO IND_REF_ORIGINAL3;
ALTER INDEX YSBAE.TMP$$_IND_REF_ORIGINAL40 RENAME TO IND_REF_ORIGINAL4;

끝

 

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN01510

Redefining Tables Online

 

 

장점

1) 테이블 가용성에 영향을 미치지 않습니다.

2) Index 및 constraint가 자동적으로 copy 됩니다.

3) LOBS와 큰 테이블작업에 추천합니다.

 

단점

1) 다른 reorg 옵션에 비해 작성해야 할 script가 많습니다.

2) dbms_redefinition 기본적으로 serial mode 로 인덱스를 생성합니다. 인덱스에 parallel mode 를 사용하려면 수동으로 생성 후 나중에 등록해주어야 합니다.

3) 가용성에 영향을 미치지 않아서 DML 이 가능하더라도 높은 트랜잭션 속도가 프로세스 동기화에 영향을 미칠 수 있으므로 사용량이 적은 시간에 수행하는 것이 좋습니다.   

 

이전에 쓴글

https://bae9086.tistory.com/81

TABLE_REDEFINITION

 

참고 

https://argolee.tistory.com/19

dbms_redefinition 패키지 (online 재생성 및 파티션 전환)

https://blog.goodusdata.com/167

[기술노트91회] Online Table Redefinition