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

 

 

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

IT/Oracle 2017.07.25 17:31

 

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

 

 

 

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

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

 

 

 

 

파티션 테이블 생성

 

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

 

 

 

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

감사합니다.

 

 

https://hello-nanam.tistory.com/31

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