행위

서브파티션 추가

DB CAFE

thumb_up 추천메뉴 바로가기

1 SUB PARTITION[편집]

1.1 서브 파티션 테이블생성[편집]

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')
   ) ;
  • 파티션 테이블 PK 생성
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;

1.2 일반테이블을 서브파티션 테이블로 변경[편집]

-- DROP TABLE TED.EMP_1;
CREATE TABLE EMP_1
    AS SELECT * 
         FROM EMP 
        WHERE 1=0;

ALTER TABLE EMP_1 
  ADD CONSTRAINT PK_EMP_1 
      PRIMARY KEY(DEPTNO,HIRE_DATE
                 ,HIRE_TIME,NATIONAL_CD
                 ,HIRE_HIST_TYPE)
USING INDEX
      NOLOGGING;

2. SUBPARTITION 추가 

ALTER TABLE EMP 
  ADD SUBPARTITION PT_20110831_WEST VALUES LESS THAN ('WEST1','WEST2');
ORA-01735: 부적합한 ALTER TABLE 옵션입니다.
ALTER TABLE EMP 
  ADD SUBPARTITION TEMPLATE WEST VALUES('WEST31','WEST32');
    ORA-01735: 부적합한 ALTER TABLE 옵션입니다.
  • 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';
  • 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 ;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=서브파티션_추가&oldid=9002"