ORACLE 파티션테이블

 -- 1.  파티션 테이블 생성

create table PT_TEST(
  NO NUMBER NOT NULL,
  NAME VARCHAR2(10) NULL
)
PARTITION BY RANGE(no)
(
     PARTITION PT_0 VALUES LESS THAN (0)
);

 -- 2. 파티션 테이블 추가

alter table PT_TEST ADD PARTITION PT_1 VALUES LESS THAN (5);
alter table PT_TEST ADD PARTITION PT_2 VALUES LESS THAN (10);

alter table PT_TEST ADD PARTITION PT_3 VALUES LESS THAN (15);

 -- 3. 테스트 데이터 INSERT
INSERT INTO PT_TEST VALUES (1 , 'A');
INSERT INTO PT_TEST VALUES (2 , 'B');
INSERT INTO PT_TEST VALUES (3 , 'C');
INSERT INTO PT_TEST VALUES (4 , 'D');
INSERT INTO PT_TEST VALUES (5 , 'E');
INSERT INTO PT_TEST VALUES (6 , 'F');
INSERT INTO PT_TEST VALUES (7 , 'G');
INSERT INTO PT_TEST VALUES (8 , 'H');
INSERT INTO PT_TEST VALUES (9 , 'I');
INSERT INTO PT_TEST VALUES (10 , 'J');
INSERT INTO PT_TEST VALUES (11 , 'K');

 

 -- 확인

SELECT * FROM PT_TEST
SELECT * FROM PT_TEST PARTITION (PT_1);
SELECT * FROM PT_TEST PARTITION (PT_2);

 

 -- 3. 파티션 삭제

ALTER TABLE  PT_TEST DROP PARTITION pt_1;

 

 -- 4. 파티션 이름 변경

ALTER TABLE PT_TEST RENAME PARTITION PT_1 TO PT_111;

 

-- 5.  파티션 TRUNCATE

ALTER TABLE pt_test TRUNCATE PARTITION PT_2;

-- 6. 파티션 exchange 

ALTER TABLE PT_TEST EXCHANGE PARTITION PT_2  WITH TABLE TEST  WITHOUT VALIDATION;

-- 7. 파티션 테이블 통계정보 

exec dbms_stats.gather_table_stats(cascade=>true,ownname=>'A',tabname=>'PT_TEST',partname=>'PT_2',

estimate_percent=>10,degree=>5,granularity=>'PARTITION');