oracle partition table 중간에 partition 추가하기 (split partition)

1. partition table & local index 생성.

CREATE TABLE p_emp (sal NUMBER(7,2))
 PARTITION BY RANGE(sal)
 (partition emp_p1 VALUES LESS THAN (2000),
  partition emp_p2 VALUES LESS THAN (4000),
  partition emp_max VALUES LESS THAN (MAXVALUE));

 create index p_emp_i on p_emp (sal) local;

insert into p_emp values (1000);
insert into p_emp values (2000);
insert into p_emp values (3000);
commit;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         4000

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         4000       USABLE

2. split partition table

전체 partition range의 중간에 partition을 삽입히기 위해서는 split partition 명령을 사용해야 한다. MAXVALUE가 없는 상태에서 제일 끝에 추가할 경우는 add partition 명령을 사용한다.

SQL> alter table p_emp split partition emp_p2 at (3000) into (partition emp_p2, partition emp_p3);

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         3000
P_EMP                          EMP_P3                         4000


SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       UNUSABLE
P_EMP_I              EMP_P3                         4000       UNUSABLE

partition table의 local partition index도 같이 split 되나 데이터가 있는 상태라면 "UNUSABLE" 상태로 되므로 이에 대한 rebuild 작업이 반드시 필요하다.

If the partition you are splitting contains data, the ALTER TABLE...SPLIT PARTITION statement marks UNUSABLE the new partitions (there are two) in each local index, all global index partitions, and any
global nonpartitioned index. You must rebuild such affected indexes or index partitions.

(Metalink Note 165599.1 Top Partitioned Tables Issues 중.)

SQL> alter index p_emp_i rebuild partition emp_p2;
SQL> alter index p_emp_i rebuild partition emp_p3;

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE

참고 : Oracle Technical iSeminar - Partition Table과 Index 관리