오라클 파티션 Auto Interval Partitioning 기능 사용 방법

Oracle DB 에서 파티션 기능을 사용하다보면, 해가 바뀌는 시점에 다음해에 해당하는 파티션을 미리 만들어두지 않아서 에러를 만나게 되는 일이 종종 있습니다.

물론, 마지막 파티션을 maxvalue 라는 키워드를 사용해서 만들어주면 되긴 하지만,

관리가 안되어 마지막 파티션이 너무 커지는 일을 당할까봐 일부러 사용하지 않는 경우도 있습니다.

 

이런 경우에 생각할 수 있는 기능이 자동으로 다음 파티션을 만들어주는 기능입니다.

전문용어로는 Automatic Interval 파티셔닝, 인터벌 파니셔닝 이라고 합니다. 11g New Feature 로 9i, 10g DB 에서는 사용할 수 없는 기능입니다.

 

이 기능을 이용하는 방법은 매우 간단합니다.

아래와 같이 기존 파티션테이블 만드는 스크립트에 interval ( ) 구문만 추가해주면 됩니다.

 

 

1) Number Type 인터벌 파티셔닝

 

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)
interval (10)
(
  partition SALES_P1 values less than (10),
  partition SALES_P2 values less than (20),
  partition SALES_P3 values less than (30)
);

 

위 예제는 sales_no 컬럼을 파티션키로 해서 3개의 파티션으로 구성된 테이블을 생성합니다.

30보다 큰 값이 들어오면 데이터를 넣을 파티션이 없기 때문에 interval(10) 에의해 10 단위로 다음 파티션을 자동으로 만들어 줍니다.

 

 

아래와 같이 sales_no = 33 인 값이 Insert 되게 되면, 값이 들어갈 수 있는 파티션이 없기 때문에 원래는 에러가 나야 합니다.

 

 

하지만, interval(10) 이 지정되어 있기 때문에 아래와 같이 새로운 파티션을 Oracle DBMS 가 자동으로 생성합니다.

 

 

이때 자동으로 생성된 파티션은 이름이 Oracle 에 의해 자동으로 부여됩니다.

새로 생성된 파티션의 High Value 가 10 단위 다음 숫자인 40 으로 정해진 것을 확인할 수 있습니다.

 

그럼, 99 를 넣으면 어떻게 될까요?

 

저는 한 6개쯤 파티션이 생길 줄 알았는데, 그렇지는 않네요.^^

파티션은 1개만 더 생기고, 그 파티션의 High Value 는 99 보다 큰 값중 10 단위 수인 100 으로 잡히네요.

 

 

 

※ 인터벌 파티셔닝에서 파티션키는 한개의 컬럼만 가능하고, Number Type 과 Date Type 만 가능합니다.

 

2) Date Type 인터벌 파티셔닝

아래는 Date Type 컬럼을 파티션키로 하는 파티션테이블에서 Interval 기능을 사용하는 경우 입니다.

 

alter session set nls_date_format = 'yyyy/mm/dd';
    --> Date Type 을 사용할 때는 날짜 포맷을 정확히 세팅해줘야 함.

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 (birth_date)
interval (numtoyminterval(1, 'YEAR'))
(
  partition SALES_P1 values less than (to_date('2011/01/01','yyyy/mm/dd')),
  partition SALES_P2 values less than (to_date('2012/01/01','yyyy/mm/dd')),
  partition SALES_P3 values less than (to_date('2013/01/01','yyyy/mm/dd'))
);

 

Date Type 을 사용할 때는 nls_date_format 을 정확히 세팅해줘야 합니다.

'mon dd yy' 와 같은 형태로 되어 있으면 날짜를 제대로 인식하지 못합니다.

 

위 예제에서는 1년 단위로 Interval 하도록 설정되었습니다.

Date Type 에서는 numtoyminterval( ) 이라는 함수를 사용해야 합니다.

'YEAR' 대신 'MONTH', 'DAY' 를 사용할 수도 있습니다.

단, 'DAY' 를 사용하는 경우에는 numtoyminterval( ) 함수대신 numtodsinterval( ) 함수를 쓰는게 좀 다릅니다.

 

 

위와 같이 기존 파티션 범주에 속하지 않는 데이터를 넣게되면 새로운 파티션이 아래와 같이 생성됩니다.

 

 

Interval 이 1년 단위라 High Value 가 '2014/01/01' 인 파티션이 생성되었습니다.

 

 

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