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

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

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

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

파티션 테이블 생성

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 인 경우 더 빠른 실행계획을 세울 수 있게 됩니다.

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

감사합니다.