SUBPARTITION 추가

1. 테스트 테이블생성
 1) COMPOSIT 테이블 생성
   CREATE TABLE EMP  (
     NATIONAL_CD  VARCHAR2(12) NOT NULL,
    HIRE_DATE  VARCHAR2(12) NOT NULL,
    HIRE_TIME  VARCHAR2(6),
    HIRE_HIST_TYPE  VARCHAR2(1),
    DEPTNO  VARCHAR2(30),
    EMP_ID  VARCHAR2(3)
    )
   PARTITION BY RANGE(HIRE_DATE)
   SUBPARTITION BY LIST(NATIONAL_CD)
   SUBPARTITION TEMPLATE
       (
        SUBPARTITION SOUTH VALUES('SOUTH1','SOUTH2'),
        SUBPARTITION NORTH VALUES('NORTH1','NORTH2'),
        SUBPARTITION EAST VALUES('EAST1','EAST2')
       )
   (
    PARTITION PT_20110831 VALUES LESS THAN ('2011083200')
   )
   ;

   ALTER TABLE  EMP ADD  CONSTRAINT PK_PCRF_QOS_HIST PRIMARY KEY (DEPTNO,HIRE_DATE,HIRE_TIME,NATIONAL_CD,HIRE_HIST_TYPE)
   USING INDEX
   LOCAL
   NOLOGGING;

 2) 일반테이블 생성
   --DROP TABLE TED.EMP_1;

   CREATE TABLE EMP_1
     AS
   SELECT * FROM EMP WHERE 1=0;
   ALTER TABLE  EMP_1 ADD  CONSTRAINT PK_PCRF_QOS_HIST_1 PRIMARY KEY (DEPTNO,HIRE_DATE,HIRE_TIME,NATIONAL_CD,HIRE_HIST_TYPE)
   USING INDEX
   NOLOGGING;

2. SUBPARTITION 추가
 1) ALTER TABLE ~ ADD SUBPARTITION ~
    ALTER TABLE EMP ADD SUBPARTITION PT_20110831_WEST VALUES LESS THAN ('WEST1','WEST2');
    ORA-01735: 부적합한 ALTER TABLE 옵션입니다.

    ALTER TABLE EMP ADD SUBPARTITION PT_20110831_EAST VALUES LESS THAN ('WEST1','WEST2');
    ORA-01735: 부적합한 ALTER TABLE 옵션입니다.

   ALTER TABLE EMP ADD SUBPARTITION TEMPLATE WEST VALUES('WEST31','WEST32');
    ORA-01735: 부적합한 ALTER TABLE 옵션입니다.

   ALTER TABLE EMP ADD SUBPARTITION WEST VALUES('WEST31','WEST32');
    ORA-01735: 부적합한 ALTER TABLE 옵션입니다.

 2) SUBPARTITION VALUE 추가 : 이후 추가되는 partition에 subpartition value가 적용되려면, "ALTER TABLE ~ MODIFY SUBPARTITION ~"을 수행해야 함
    SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP';

    ALTER TABLE EMP MODIFY SUBPARTITION PT_20110831_EAST ADD VALUES ('WEST1','WEST2');

    ALTER TABLE EMP ADD PARTITION PT_20110931 VALUES LESS THAN ('2011093200');

    SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP';

    ALTER TABLE EMP MODIFY SUBPARTITION PT_20110931_EAST ADD VALUES ('WEST1','WEST2');

    SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP';

 3) SUBPARTITION TEMPLATE 추가
    -- SUBPARTITION TEMPLATE을 추가 하면 새로 PARTITION을 추가한 이후부터 NEW SUBPARTITION TEMPLATE이 적용됨

    -- OLD PARTITION에는 NEW SUBPARTITION TEMPLATE은 적용되지 않음

    -- OLD PARTITION에도 NEW SUBPARTITION TEMPLATE을 적용하려면, OLD PARTITION을 DROP하고 NEW PARTITION을 SPLIT하면 NEW SUBPARTITION TEMPLATE은 적용됨

    ① ADD SUBPARTITION TEMPLATE
       SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP';
       SELECT * FROM USER_SUBPARTITION_TEMPLATES WHERE TABLE_NAME='EMP';

       ALTER TABLE EMP
       SET SUBPARTITION TEMPLATE
         (SUBPARTITION SOUTH VALUES('SOUTH1','SOUTH2'),
          SUBPARTITION NORTH VALUES('NORTH1','NORTH2'),
          SUBPARTITION EAST VALUES('EAST1','EAST2'),
        SUBPARTITION WEST VALUES('WEST1','WEST2'),
        SUBPARTITION EAST_WEST VALUES('EAST_WEST1','EAST_WEST2'),
        SUBPARTITION SOUTH_NORTH VALUES('SOUTH_NORTH1','SOUTH_NORTH2')
         );

        SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP';
        SELECT * FROM USER_SUBPARTITION_TEMPLATES WHERE TABLE_NAME='EMP';

    ② PARTITION ADD
        SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP' AND PARTITION_NAME='PT_20110931';

        ALTER TABLE EMP ADD PARTITION PT_20110931 VALUES LESS THAN ('2011093200');

        SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP' AND PARTITION_NAME='PT_20110931';

    ③ OLD PARTITION DATA MIGRATION : PARTITION EXCHANGE
        SELECT * FROM EMP ;
        SELECT * FROM EMP_1 ;

        ALTER TABLE EMP EXCHANGE SUBPARTITION PT_20110831_SOUTH
           WITH TABLE EMP_1 INCLUDING INDEXES WITHOUT VALIDATION;

        ALTER TABLE EMP EXCHANGE SUBPARTITION PT_20110831_NORTH
           WITH TABLE EMP_1 INCLUDING INDEXES WITHOUT VALIDATION;

        ALTER TABLE EMP EXCHANGE SUBPARTITION PT_20110831_EAST
           WITH TABLE EMP_1 INCLUDING INDEXES WITHOUT VALIDATION;

        SELECT * FROM EMP ;
        SELECT * FROM EMP_1 ;

    ④ OLD PARTITION DROP
        SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP' AND PARTITION_NAME='PT_20110831';

        ALTER TABLE EMP DROP PARTITION PT_20110831;

        SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP' AND PARTITION_NAME='PT_20110831';

    ⑤ PARTITION SPLIT
        SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP' AND PARTITION_NAME='PT_20110831';

        ALTER TABLE EMP SPLIT PARTITION PT_20110931 AT ('2011083200') INTO(PARTITION PT_20110831, PARTITION PT_20110931);

        SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP' AND PARTITION_NAME='PT_20110831';

    ⑥ PARTITION DATA MIGRATION
        SELECT * FROM EMP ;
        SELECT * FROM EMP_1 ;

        ALTER TABLE EMP EXCHANGE SUBPARTITION PT_20110831_SOUTH
           WITH TABLE EMP_1 INCLUDING INDEXES WITHOUT VALIDATION;

        ALTER TABLE EMP EXCHANGE SUBPARTITION PT_20110831_NORTH
           WITH TABLE EMP_1 INCLUDING INDEXES WITHOUT VALIDATION;

        ALTER TABLE EMP EXCHANGE SUBPARTITION PT_20110831_EAST
           WITH TABLE EMP_1 INCLUDING INDEXES WITHOUT VALIDATION;

        SELECT * FROM EMP ;
        SELECT * FROM EMP_1 ;