Oracle의 대표적인 DW를 위한 기능, Partition table의 종류

Table partitioning은 Oracle의 대표적인 DW를 위한 기능이다.

이 기능은 테이블을 특정 partition set으로 나누어 관리 및 성능 상의 이점을 얻는다.
다음의 내용은 이러한 partition table의 종류와 간단한 정의이다.

Range Partitioning Method

column 값의 범위에 따라 partition을 나누는 가장 일반적인 방법이다. 이 방식은 대개 날짜와 같은 범위로 많이 나뉘게 되는데, AP의 성격에 따라 데이터가 특정 partition에 몰리는 경우 hash partition 방식이나, list partition 방식 등 다른 partition 방식을 통해 sub-partition을 나누기도 한다.

Create table test164874 (
ord_day         NUMBER(2),
ord_month       NUMBER(2),
ord_year        NUMBER(4),
ord_id  NUMBER(10)
)
storage (initial 12k next 12k pctincrease 0 minextents 1)
PARTITION BY RANGE (ord_year,ord_month,ord_day)
(
PARTITION P1 VALUES LESS THAN (2001,3,31) TABLESPACE PART1,
PARTITION P2 VALUES LESS THAN (2001,6,30) TABLESPACE part2,
PARTITION P3 VALUES LESS THAN (2001,9,30) TABLESPACE part3,
PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE part4
)
/

Hash Partitioning Method

partition key의 hash 값에 의해 partition으로 나눠진다. 일반적으로 균등한 분포를 가지며 성능상의 이유로 hash partition을 사용하기도 한다.

CREATE TABLE tabpart1(
ord_id   NUMBER(5),
ord_date DATE
)
PARTITION BY HASH(ord_id) PARTITIONS 16
STORE IN (tbs1,tbs2,tbs3,tbs4)
/

Composite Partitioning Method

Oracle 10g 이전엔 Range-List, Range-Hash composite partition 만 지원했으나, Oracle 11에서는 List-List, List-Hash, List-Range and Range-Range composite partition이 지원된다. 또 interval partition 방식에 대해서 Interval-Range, Interval-List and Interval-Hash가 가능하다.

   

CREATE TABLE TAB1(
     ord_id  NUMBER(10),
     ord_day    NUMBER(2),
     ord_month  NUMBER(2),
     ord_year   NUMBER(4)
     )
PARTITION BY RANGE (ord_year,ord_month,ord_day)
  SUBPARTITION BY HASH(ord_id) SUBPARTITIONS 8
  STORE IN (TBS1,TBS2,TBS3,TBS4,TBS5,TBS6,TBS7,TBS8)
     (  PARTITION P1 VALUES LESS THAN (2001,3,31),
        PARTITION P2 VALUES LESS THAN (2001,6,30),
        PARTITION P3 VALUES LESS THAN (2001,9,30),
        PARTITION P4 VALUES LESS THAN (2001,12,31)
 )
/

List Partitioning Method

Oracle 9i에 소개된 partition 방식으로 partition key의 값 자체에 의해 분리된 partition으로 데이터가 저장된다. 값에 의해 데이터가 partition 되므로 전혀 관계없는 데이터 값을 하나의 partition에 저장할 수 있다.

CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);

Interval Partitioning Method

Interval partition은 range partition 기능의 확장으로 개별 범위를 명시적으로 정의하지 않아도 해당 interval에 속하는 데이터가 입력 될 때 partition이 자동으로 생성된다.

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2) )
PARTITION BY RANGE (time_id)
INTERVAL(numtodsinterval(7,'day'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );

Reference Partitioning Method

Oracle 11g에서 소개된 partition 방식으로 “모-자” 관계의 table 간에 모 table의 partition key column의 복제 없이 모 table의 partition 구조를 따라가게 됩니다.

SQL> CREATE TABLE ref_part_parent
2 (pcol1 NUMBER PRIMARY KEY,
3 pcol2 VARCHAR2(10))
4 PARTITION BY RANGE (pcol1)
5 (PARTITION p1 VALUES LESS THAN (100),
6 PARTITION p2 VALUES LESS THAN (200),
7 PARTITION p3 VALUES LESS THAN (300),
8* PARTITION p4 VALUES LESS THAN (MAXVALUE))

Table created.

SQL> CREATE TABLE ref_part_child
2 (ccol1 NUMBER NOT NULL,
3 CONSTRAINT ccol1_fk FOREIGN KEY(ccol1) REFERENCES ref_part_parent(pcol1))
4 PARTITION BY REFERENCE(ccol1_fk);

Table created.

System Partitioning Method

oracle 11g에서 소개된 partition 방식으로 다른 partition 방식과의 가장 큰 차이는 partition key를 정의하지 않는데 있다. 즉, system이 알아서 partition을 해준다.

CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4
); 

Virtual column-based Partitioning Method

virtual column partition 방식은 partition key의 값이 물리적으로 존재하지 않은 경우에도 virtual column 값에 의해 partition을 할 수 있게 한다.

 CREATE TABLE employees
  (employee_id  number(6) not null, first_name varchar2(30), 
   last_name varchar2(40) not null, email varchar2(25),
   phone_number varchar2(20), hire_date  date not null,
   job_id  varchar2(10) not null, salary number(8,2),
   commission_pct number(2,2), manager_id  number(6),
   department_id number(4),
   total_compensation as (salary *( 1+commission_pct))
   )
    PARTITION BY RANGE (total_compensation)
     (
       PARTITION p1 VALUES LESS THAN (50000),
       PARTITION p2 VALUES LESS THAN (100000),
       PARTITION p3 VALUES LESS THAN (150000),
       PARTITION p4 VALUES LESS THAN (MAXVALUE)
     );

참고 자료 : 

ORACLE PARTITIONING
Partitioning in Oracle Database 11g
Partition Table과 Index의 관리 방법
Partitioning Your Oracle DWH
Partitioning in Oracle Database 10g Release 2
Partitioning in Oracle Database 10g
Note 452447.1 11g Partitioning Enhancements