[Oracle 11g~] 온라인 테이블 redefinition (reorg)

 

온라인 테이블은 OLTP* 특성상 CRUD*가 빈번하게 발생한다.

 

오라클의 인덱스는 B-Tree 형태를 가지는데, CRUD가 계속 발생할 경우 Tree가 점점 더 편향되게 바뀔 확률이 아주 높다.

(Root node 대비 한 쪽으로 데이터가 증가할 확률이 높으므로, 아래 그림 참조)

 

Internal Structure of a B-tree Index (Source : https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm)

 

 

따라서 생성된지 오래된 테이블을 신규 생성하여 기존 데이터를 부어주고, 인덱스를 새로 만들게 될 경우,

인덱스의 B-tree는 편향성을 보완하여 차지하는 용량을 줄이고 검색 속도를 빠르게 할 수 있다.

 

테이블 용량도 오라클 버전업을 거듭하면서 효율화가 많이 됐지만, 간혹 과거 윈도우의 HDD 디스크조각모음과 유사한 효과로 용량이 감소하는 경우가 있다.

 

이 때 해주는 것이 바로 TABLE REDEFINITION, 일명 REORG (REORGANIZATION) 인데, 규모가 큰 서비스를 운영하는 경우, 주기적으로 REORG를 통해 테이블스페이스를 효율적으로 관리하고, 검색 성능을 빠르게 해주고 있다.

 

대략적인 순서는 다음과 같다.

1. TABLE을 ONLINE DEFINITION 하겠다고 선언

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp', DBMS_REDEFINITION.CONS_USE_PK);
END;
/

 

 

2. INTERIM TABLE (새로 생성할 임시 테이블) 생성

CREATE TABLE hr.int_admin_emp
        (empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         sal        NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL,
         bonus      NUMBER (7,2) DEFAULT(1000))
     PARTITION BY RANGE(empno)
       (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
        PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);

예제 코드를 보면 PARTITION을 기존과 다르게 생성할 수 있다는 장점이 있다.

 

 

3. REDEFINITION 실행

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
       'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
        dbms_redefinition.cons_use_pk);
END;
/

여기서 START_REDEF_TABLE의 PARAMETER를 보면 COLMUN을 1대1로 대응 또는 값을 바꿔서 할 수 있다. 

(해당 PARAM 제외시 기존 COLUMN 그대로 들어감)

 

 

4. 의존 Object 복사

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;

 

 

 5. 오류확인

SQL> select object_name, base_table_name, ddl_txt from
         DBA_REDEFINITION_ERRORS;
 
OBJECT_NAME   BASE_TABLE_NAME  DDL_TXT
------------- ---------------- ------------------------------
SYS_C005836   ADMIN_EMP        CREATE UNIQUE INDEX "HR"."TMP$
                               $_SYS_C0058360" ON "HR"."INT_A
                               DMIN_EMP" ("EMPNO")
 
SYS_C005836   ADMIN_EMP        ALTER TABLE "HR"."INT_ADMIN_EM
                               P" ADD CONSTRAINT "TMP$$_SYS_C
                               0058360" PRIMARY KEY

 

 

6. (선택이라고 써있지만 필수적으로 추천) SYNC 맞추기

BEGIN 
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

 

 

7. 마무리 : 이 메소드를 실행시 interim(임시) 테이블과 original 테이블을 switch 하게 된다.

              (실시간으로 DML이 많이 발생하는 테이블의 경우 UNDO가 발생할 수 있으니 유의)

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

   

 

출처 : https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN11672

Redefining Tables Online

 

 

* OLTP : Online Transaction Processing. 온라인 트랜잭션 처리, 실시간으로 빈번한 트랜잭션이 발생하는 처리방식.

           (반대 : OLAP)

* CRUD : Create / Read / Update / Delete. 생성, 읽기, 갱신, 삭제