오라클 파티션 테이블 삭제 - olakeul patisyeon teibeul sagje

오라클은 파티션테이블(Partition Table)에 대해 다양한 관리작업을 지원합니다.

이 페이지에서는 파티션을 추가하거나, 삭제하거나, 자르거나, 이름을 변경하는 등의 작업을 해보겠습니다.

아래와 같은 Range 파티션테이블에서 ..

create table SALES (
  sales_no       number,
  sale_year      number,
  sale_month     number,
  sale_day       number,
  customer_name  varchar2(30),
  birth_date     date,
  price          number
)
partition by range (sales_no)
(
  partition SALES_P1 values less than (3),
  partition SALES_P2 values less than (5)
);

1) 파티션 Add

아래와 같이하여 SALES_P3 파티션을 추가(add)할 수 있습니다.

alter table SALES add partition SALES_P3 values less than (7);

2) 파티션 Drop

파티션 삭제는 아래와 같이 drop 커맨드를 사용합니다.

alter table SALES drop partition SALES_P3;

3) 파티션 Modify

아래와 같이 변경(modify) 하는 구문은 Range 파티션에서는 사용할 수 없습니다. ORA-14049 에러가 발생합니다.

하지만, List 파티션에서는 modify 구문을 사용할 수 있습니다.

위 샘플은 리스트 파티션에서 P_CENTRAL 파티션에 'KR' 항목을 add / drop 하는 샘플입니다.

에러없이 실행되는 것을 확인할 수 있습니다.

4) 파티션 Truncate

파티션도 테이블처럼 간주되기 때문에 아래와 같이 특정파티션에 대해서 Truncate 커맨드를 사용할 수 있습니다.

alter table SALES truncate partition P_EAST;

5) 파티션 Rename

파티션명의 Rename 은 테이블명의 Rename 과 구문이 거의 같습니다.

alter table SALES rename partition P_EAST to P_EAST_999;

▶▶▶ 파티션 Split/Merge/Coalesce 샘플 보기

▶▶▶ 오라클 파티션 종합 페이지 바로가기

오라클 파티션 테이블 샘플(생성 수정 삭제) 및 주의사항

대용량 데이터를 조회 시 조금이라도 더 빠른 데이터의 접근을 위해서는 

테이블을 파티션으로 분할하여 테이블을 생성할 수가 있습니다.

파티션 테이블 생성

CREATE TABLE TB_SALE  ( 
      SALE_DATE          VARCHAR2(8 BYTE)  NOT NULL ,
      SALE_TIME          VARCHAR2(6 BYTE)  NOT NULL ,
      CUST_NO            VARCHAR2(10 BYTE) NOT NULL ,
      SALE_AMT           NUMBER                     ,
      INPUT_DATE         DATE
)
  TABLESPACE TS_DATA
  PARTITION BY RANGE (SALE_DATE) 
 (
 PARTITION PR_TB_SALE_201801  VALUES LESS THAN ('20180201') , 
 PARTITION PR_TB_SALE_201802  VALUES LESS THAN ('20180301') , 
 PARTITION PR_TB_SALE_201803  VALUES LESS THAN ('20180401') , 
 PARTITION PR_TB_SALE_201804  VALUES LESS THAN ('20180501') , 
 PARTITION PR_TB_SALE_201805  VALUES LESS THAN ('20180601') , 
 PARTITION PR_TB_SALE_201806  VALUES LESS THAN ('20180701') , 
 PARTITION PR_TB_SALE_201807  VALUES LESS THAN ('20180801') , 
 PARTITION PR_TB_SALE_201808  VALUES LESS THAN ('20180901') , 
 PARTITION PR_TB_SALE_201809  VALUES LESS THAN ('20181001') , 
 PARTITION PR_TB_SALE_201810  VALUES LESS THAN ('20181101') , 
 PARTITION PR_TB_SALE_201811  VALUES LESS THAN ('20181201') , 
 PARTITION PR_TB_SALE_201812  VALUES LESS THAN ('20190101') , 
 PARTITION PR_TB_SALE_201901  VALUES LESS THAN ('20190201') , 
 PARTITION PR_TB_SALE_201902  VALUES LESS THAN ('20190301') , 
 PARTITION PR_TB_SALE_201903  VALUES LESS THAN ('20190401') , 
 PARTITION PR_TB_SALE_201904  VALUES LESS THAN ('20190501') , 
 PARTITION PR_TB_SALE_201905  VALUES LESS THAN ('20190601') , 
 PARTITION PR_TB_SALE_201906  VALUES LESS THAN ('20190701') , 
 PARTITION PR_TB_SALE_201907  VALUES LESS THAN ('20190801') , 
 PARTITION PR_TB_SALE_201908  VALUES LESS THAN ('20190901') , 
 PARTITION PR_TB_SALE_201909  VALUES LESS THAN ('20191001') , 
 PARTITION PR_TB_SALE_201910  VALUES LESS THAN ('20191101') , 
 PARTITION PR_TB_SALE_201911  VALUES LESS THAN ('20191201') , 
 PARTITION PR_TB_SALE_201912  VALUES LESS THAN ('20200101') 
-- PARTITION PR_TB_SALE_MAX  VALUES LESS THAN (MAXVALUE) 
 ) 
 ;


CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ;  


ALTER TABLE TB_SALE
  ADD CONSTRAINT PK_TB_SALE PRIMARY KEY (SALE_DATE, SALE_TIME, CUST_NO);


COMMENT ON COLUMN TB_SALE.SALE_DATE IS '매출일자';
COMMENT ON COLUMN TB_SALE.SALE_TIME IS '매출시간';
COMMENT ON COLUMN TB_SALE.CUST_NO   IS '고객번호';
COMMENT ON COLUMN TB_SALE.SALE_AMT  IS '매출 금액';
COMMENT ON TABLE  TB_SALE           IS '고객 매출내역';

파티션 구성 시 마지막에 MAXVALUE 를 주석으로 막은 이유는 

분할해 둔 파티션의 마지막이 임박해오는 경우 SPLIT 보다 파티션 추가가 간편하기 때문입니다!

물론 예기치 않은 데이터가 들어오는 경우 MAXVALUE 를 지정해두는것이 안정적이긴 합니다.

파티션 추가

 ALTER TABLE TB_SALE
 ADD PARTITION PR_TB_SALE_202001 VALUES LESS THAN ('20200201' )
 TABLESPACE TS_INDEX ;

파티션 삭제

ALTER TABLE TB_SALE DROP PARTITION PR_TB_SALE_202001 ;

파티션 분할 (MAXVALUE 파티션 분할)

ALTER TABLE TB_SALE 
SPLIT PARTITION PR_TB_SALE_MAX AT ( '20200201' )
INTO ( PARTITION PR_TB_SALE_202001
     , PARTITION PR_TB_SALE_MAX
     ) ;

MAXVALUE 로 잡혀있던 PR_TB_SALE_MAX 파티션을 2020년 02월 01일 이전 데이터가 입력될 파티션으로 분리하였다.

파티션 TRUNCATE

ALTER TABLE TB_SALE TRUNCATE PARTITION PR_TB_SALE_201801 ;

파티션 테이블 인덱스 생성

CREATE UNIQUE INDEX PK_TB_SALE ON TB_SALE (SALE_DATE, SALE_TIME, CUST_NO)
TABLESPACE TS_INDEX LOCAL ; 

파티션 테이블을 생성할 때 INDEX 설정 시 주의할 사항이 있습니다.

예를 들어 위와 같이 RANGE 파티션을 가진 테이블을 월별로 파티션을 분리한다고 했을 때 

테이블 전체에 INDEX 를 구성하는 것 보다 각각의 파티션 별로 INDEX 를 가지고 있어야 

대용량의 테이블을 조회할 때 더 효과적인 속도를 기대할 수 있습니다.

여기서 테이블스페이스의 INDEX 지정 시 LOCAL INDEX 가 아닌 default 로 설정하거나 GLOBAL INDEX 로 설정할 경우

파티션기준이 아닌 전체 테이블을 기준으로 INDEX 가 만들어지니 주의해야 합니다.

파티션 테이블이 GLOBAL INDEX 로 생성될 경우 테이블의 중간중간 데이터가 삭제되거나 변경된다면 

전체 테이블 기준으로 최적화된 INDEX 가 뒤죽박죽 될 수 있으나

LOCAL 로 생성되는 경우에는 다른 파티션의 INDEX 에는 영향이 가지 않으니 

INDEX 를 사용하는 SQL 인 경우 더 빠른 실행계획을 세울 수 있게 됩니다.

궁금한점이나 잘못된 부분이 있으면 댓글 남겨주세요^^

감사합니다.