테이블 파티셔닝

오라클에서 파티션 테이블을 만드는 방법 6가지

범위 파티셔닝 (range partitioning)
해시 파티셔닝 (hash partitioning)
리스트 파티셔닝 (list partitioning)
인터벌 파티셔닝 (interval partitioning)
참조 파티셔닝 (reference partitioning)
복합 파티셔닝 (composit partitioning)

범위 파티셔닝



SQL> CREATE TABLE range_example
( range_key_column  date NOT NULL,
  data              varchar2(20)
)
PARTITION BY RANGE ( range_key_column )
(
   PARTITION part_1 VALUES LESS THAN ( to_date('01/01/2010','dd/mm/yyyy')),
   PARTITION part_2 VALUES LESS THAN ( to_date('01/01/2011','dd/mm/yyyy'))
)
/

Table created.

Data insert




-- 2009년 데이터 insert 

SQL> insert into range_example ( range_key_column, data )
values (to_date('15-dec-2009 00:00:00','dd-mon-yyyy hh24:mi:ss'), 'application data...') ;



SQL> insert into range_example ( range_key_column, data )
values (to_date('31-dec-2009 23:59:59','dd-mon-yyyy hh24:mi:ss'), 'application data...') ;


-- 2010년 데이터 insert 


SQL> insert into range_example ( range_key_column, data )
values (to_date('01-jan-2010 00:00:00','dd-mon-yyyy hh24:mi:ss'), 'application data...') ;



SQL>  insert into range_example ( range_key_column, data )
values (to_date('31-dec-2010 00:00:00','dd-mon-yyyy hh24:mi:ss'), 'application data...') ;


SQL> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss') from range_example partition ( part_1 );

TO_CHAR(RANGE_KEY_COLUM
-----------------------
15-dec-2009 00:00:00
31-dec-2009 23:59:59



SQL> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss') from range_example partition (part_2 );

TO_CHAR(RANGE_KEY_COLUM
-----------------------
01-jan-2010 00:00:00
31-dec-2010 00:00:00

입력된 데이터가 상위 경계값 보다 클 때는 오류를 발생시킨다.



SQL> insert into range_example ( range_key_column, data )
 values ( to_date('31-dec-2012 00:00:00','dd-mon-yyyy hh24:mi:ss'), 'application data...') ;
insert into range_example ( range_key_column, data )
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

인터벌 파티셔닝 사용
모든 데이터를 포함하는 파티션을 미리 생성해두기
범위 파티셔닝에서 MAXVALUE 구문을 사용하여 처리할 수 있다.



SQL> CREATE TABLE range_example
( range_key_column  date ,
  data              varchar2(20)
)
PARTITION BY RANGE ( range_key_column )
(
   PARTITION part_1 VALUES LESS THAN ( to_date('01/01/2010','dd/mm/yyyy')),
   PARTITION part_2 VALUES LESS THAN ( to_date('01/01/2011','dd/mm/yyyy')),
   PARTITION part_3 VALUES LESS THAN ( MAXVALUE )
)
/


-- RANGE_KEY_COLUMN 값이 NULL 이라도 새로운 파티션에 저장한다.


SQL> INSERT INTO range_example ( range_key_column, data )
SELECT NULL AS range_key_column,  'application data...' from dual ;

1 row created.


SQL> select * from  range_example partition ( part_3 ) ;

RANGE_KEY_COLU DATA
-------------- --------------------
               application data...

해시 파티셔닝

파티션 키에 해시(hash) 함수를 적용 한 값으로 파티셔닝
데이터가 최대한 고르게 분산되도록 하기 위해서 2의 n승 (2,4,8,16,...)의 숫자로 파티셔닝 개수를 지정하도록 권고함.
Hash 키는 유일한 컬럼 혹은 컬럼집합이어야 하고, 여러 파티션에 걸쳐 로우가 분산되도록 가능한 많은 식별 값을 가져야 함.

-



SQL> CREATE TABLE hash_example
(
 hash_key_column date,
 data varchar2(20)
)
 PARTITION BY HASH (hash_key_column)
 (  partition part_1 tablespace p1 ,
    partition part_2 tablespace p2
 )
 /

Table created.


SQL> insert into hash_example ( hash_key_column, data )
 values ( to_date('25-jun-2010','dd-mon-yyyy'),'applicationdata...') ;

1 row created.


SQL> insert into hash_example ( hash_key_column, data )
 values ( to_date('27-feb-2010','dd-mon-yyyy'),'applicationdata...') ;

1 row created.


SQL> select * from hash_example partition ( part_1 ) ;

HASH_KEY_COLUM DATA
-------------- --------------------
20100227000000 applicationdata...

SQL> select * from hash_example partition ( part_2 ) ;

HASH_KEY_COLUM DATA
-------------- --------------------
20100625000000 applicationdata...

해시 파티션 수를 변경하면, 모든 파티션 전체에 걸쳐 재분배 발생
큰 테이블을 가지고 분할정복 시 가장 유용하다.

#해시파티션의 장점#

파티션 수는 2의 n승이어야 한다.
N개의 파티션을 가진 해시 파티션 테이블을 자동으로 생성하는 프로시저
파티션 로우 수 조회, 그래프로 표기



SQL> create or replace procedure hash_proc ( p_nhash in number,
                                        p_cursor out sys_refcursor )
authid current_user
as
    l_text long;
    l_template long := 'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' ||
                        ' from t partition ( $PNAME$ ) union all ';
    table_or_view_does_not_exist exception;
    pragma exception_init ( table_or_view_does_not_exist, -942 );
begin
    begin
        execute immediate 'drop table t';
    exception when table_or_view_does_not_exist
        then null;
    end;
    
    execute immediate '
        create table t (id)
        partition by hash(id)
        partitions ' || p_nhash || ' 
        as
        select rownum
        from all_objects ';
    
    for x in ( select partition_name pname,
                      partition_position pos
               from user_tab_partitions
               where table_name = 'T'
               order by partition_position )
    loop
        l_text := l_text ||
            replace(
            replace(l_template, 
                '$POS$', x.pos ),
                '$PNAME$', x.pname );
    end loop;
    
    open p_cursor for
        'select pname, cnt, 
            substr( rpad(''*'', 30*round( cnt/max(cnt) over(), 2), ''*''),1,30) hg
            from (' || substr( l_text, 1, length(l_text)-11 ) || ' )
            order by oc';
end;
/

4개의 파티션 생성을 위해 변수를 4로 하여 실행 시 분포도



SQL> variable x refcursor
SQL> set autoprint on
SQL> exec hash_proc(4, :x);

PL/SQL procedure successfully completed.


PN        CNT HG
-- ---------- ----------------------------------------------
p1       9873 *****************************
p2       9880 *****************************
p3      10203 ******************************
p4       9899 *****************************

변수를 5로 했을 경우 분포도


SQL> exec hash_proc(5, :x);

PL/SQL procedure successfully completed.

PN        CNT HG
-- ---------- ------------------------------------------------------------
p1       4962 **************
p2       9880 *****************************
p3      10203 ******************************
p4       9900 *****************************
p5       4911 **************

6, 7 로 하였을 경우 분포도



SQL> exec hash_proc(6, :x);

PL/SQL procedure successfully completed.


PN        CNT HG
-- ---------- ------------------------------------------------------------
p1       4962 **************
p2       5004 **************
p3      10203 ******************************
p4       9900 *****************************
p5       4911 **************
p6       4877 **************


SQL> exec hash_proc(7, :x);
PL/SQL procedure successfully completed.


PN        CNT HG
-- ---------- ------------------------------------------------------------
p1       4963 ***************
p2       5004 ***************
p3       5076 ***************
p4       9900 ******************************
p5       4911 ***************
p6       4877 **************
p7       5127 ***************

2의 n승을 가진 해시 파티션 수로 하면 균등하게 배분된 결과 확인



SQL> exec hash_proc(8, :x);

PL/SQL procedure successfully completed.


PN        CNT HG
-- ---------- ------------------------------------------------------------
p1       4964 *****************************
p2       5004 *****************************
p3       5076 *****************************
p4       4911 ****************************
p5       4911 ****************************
p6       4877 ****************************
p7       5127 ******************************
p8       4989 *****************************

리스트 파티셔닝

Oracle 9i 부터 지원되는 기능
특정 코드에 의해 파티션할 경우 유용하다.



create table list_example
(  state_cd varchar2(2) ,
      data varchar2(20)
)
partition by list(state_cd)
(
 partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
 partition part_2 values ( 'CT', 'RI' , 'NY' )
) ;


SQL> insert into list_example values ('CT','application ddata...');

1 row created.

SQL> insert into list_example values ('MA','application ddata...');
SQL> insert into list_example values ('ME','application ddata...');
SQL> insert into list_example values ('NH','application ddata...');
SQL> insert into list_example values ('NY','application ddata...');
SQL> insert into list_example values ('RI','application ddata...');
SQL> insert into list_example values ('VT','application ddata...');

-- 위 처럼 PARTITION KEY 에 해당되는 값은 정상적으로 INSERT 되나, 
-- KEY에 맞지 않는 값은 check constraint 처럼 에러를 발생시키며 INSERT 가 실패된다.

SQL> insert into list_example values ('VA','data') ;
 insert into list_example values ('VA','data')
             *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

list_example 파티션 테이블에 세 번째 파티션을 생성한다



SQL> alter table list_example add partition part_3 values ( DEFAULT ) ;

Table altered.

==> 사용자가 정의한 목록에 해당하지 않는 모든 값은 part_3 에 저장된다.
==> 주의할 점 : 리스트 파티션 테이블에 DEFAULT 파티션을 생성하면 더이상 파티션을 추가할 수 없다.
==> 파티션을 추가하려면 DEFAULT 파티션을 제거 후, 신규 파티션 추가 이후 DEFAULT 파티션을 재생성 해야 한다.



SQL> alter table list_example add partition part_4 values ( 'CA','NM');
alter table list_example add partition part_4 values ( 'CA','NM')
            *
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

인터벌 파티셔닝

Oracle 11gR1 이상에서 지원된다.
범위 파티셔닝과 유사하며, 데이터베이스가 파티션을 추가하는 규칙(interval)을 추가하였다.
기존 파티션에 데이터가 있고, 새로운 데이터가 데이터베이스에 입력될 때 새로운 파티션을 자체적으로 생성한다.
연산이 가능한 단일컬럼으로 범위 파티션된 테이블이어야 한다.



create table audit_trail
(
  ts timestamp,
  data varchar2(30)
)
partition by range(ts)
interval (numtoyminterval(1 , 'month'))
  store in (USERS, EXAMPLE )   -- Tablespace_name 
(partition P0 values less than
(to_date('01-01-1900','dd-mm-yyyy'))
 )
;

범위 파티셔닝의 경우, 특정 범위에 맞지 않는 데이터가 insert 될 경우 에러 발생되며 실패하지만,
인터벌 파티셔닝의 경우 새로운 파티션을 생성하여 데이터를 저장한다.



set lines 200
col partition_name format a20
col interval format a40
col high_value format a40

select a.partition_name, a.tablespace_name, a.high_value,
       decode( a.interval, 'YES' , b.interval ) interval
  from user_tab_partitions a, user_part_tables b
 where a.table_name = 'AUDIT_TRAIL'
   and a.table_name = b.table_name
  order by a.partition_position;

PARTITION_NAME       TABLESPACE_NAME                HIGH_VALUE                               INTERVAL
-------------------- ------------------------------ ---------------------------------------- ----------
P0                   USERS                          TIMESTAMP' 1900-01-01 00:00:00'

==> 파티션이 1개가 생성되어 있으며, INTERVAL 파티션이 없음 ( INTERVAL IS NULL )
==> 현재 까지는 일반적인 범위 파티션으로 존재함.


interval (numtoyminterval(1 , 'month'))    -- Interval 지정 
  store in (USERS, EXAMPLE )               -- 신규 파티션이 저장될 Tablespace 목록 지정

데이터 insert 후 partition list 를 확인한다



SQL> insert into audit_trail (ts ,data) values
(to_timestamp( '27-feb -2010' , 'dd -mon-yyyy' ),'xx' );

1 row created.

SQL> commit ;

Commit complete.


PARTITION_NAME       TABLESPACE_NAME                HIGH_VALUE                               INTERVAL
-------------------- ------------------------------ ---------------------------------------- ----------------------------------------
P0                   USERS                          TIMESTAMP' 1900-01-01 00:00:00'
SYS_P91              USERS                          TIMESTAMP' 2010-03-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')

==> 범위 파티션의 경우, 2010-03-01 보다 작고, 1900-01-01 보다 크거나 같은 데이터 존재
==> 인터벌 파티션의 경우, 인터벌이 설정될 때 ( HIGH_VALUE - INTERVAL ) 보다 크거나 같고, HIGH_VALUE 보다 작음.



select  TIMESTAMP '2010-03-01 00:00:00'-NUMTOYMINTERVAL(1,'MONTH') greater_than_eq_to
      , TIMESTAMP '2010-03-01 00:00:00' strictly_less_than
from dual ;

GREATER_THAN_EQ_TO                       STRICTLY_LESS_THAN
---------------------------------------- ---------------------------------------------------------------------------
01-FEB-10 12.00.00.000000000 AM          01-MAR-10 12.00.00.000000000 AM


-- 다른 달의 데이터 입력 시 아래와 같이 나눠진다

SQL> insert into audit_trail (ts,data) values ( to_date('25-jun-2010','dd-mon-yyyy'),'xx') ;

1 row created.


PARTITION_NAME       TABLESPACE_NAME                HIGH_VALUE                               INTERVAL
-------------------- ------------------------------ ---------------------------------------- ----------------------------------------
P0                   USERS                          TIMESTAMP' 1900-01-01 00:00:00'
SYS_P91              USERS                          TIMESTAMP' 2010-03-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
SYS_P92              USERS                          TIMESTAMP' 2010-07-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')


-- 테이블스페이스 지정 시  2개로 지정하고
-- 파티션이 모두 짝수달 데이터로 파티션이 생성되어, 짝수는 모두 USERS Tablespace를 사용한다.

-- 홀수 파티션을 생성하는 데이터를 입력하면 두 번째 Tablespace 를 사용한다.

SQL> insert into audit_trail (ts,data) values ( to_date('15-mar-2010','dd-mon-yyyy'),'xx') ;

1 row created.


PARTITION_NAME       TABLESPACE_NAME                HIGH_VALUE                               INTERVAL
-------------------- ------------------------------ ---------------------------------------- ----------------------------------------
P0                   USERS                          TIMESTAMP' 1900-01-01 00:00:00'
SYS_P91              USERS                          TIMESTAMP' 2010-03-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
SYS_P93              EXAMPLE                        TIMESTAMP' 2010-04-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
SYS_P92              USERS                          TIMESTAMP' 2010-07-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')

데이터가 rollback 되거나 삭제 되어도 파티션은 그대로 남아있는다.



SQL> delete from audit_trail ;

3 rows deleted.

SQL> commit ;

Commit complete.


PARTITION_NAME       TABLESPACE_NAME                HIGH_VALUE                               INTERVAL
-------------------- ------------------------------ ---------------------------------------- ----------------------------------------
P0                   USERS                          TIMESTAMP' 1900-01-01 00:00:00'
SYS_P91              USERS                          TIMESTAMP' 2010-03-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
SYS_P93              EXAMPLE                        TIMESTAMP' 2010-04-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
SYS_P92              USERS                          TIMESTAMP' 2010-07-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')

자동으로 생성된 INTERVAL 파티션은 추후에 RENAME 가능하다. ( 초기에 지정은 불가능 )

 

SQL> 
declare
  l_str varchar2(4000);
begin
  for x in ( select a.partition_name, a.tablespace_name, a.high_value
               from user_tab_partitions a
              where a.table_name = 'AUDIT_TRAIL'
                and a. interval = 'YES'
                and a.partition_name like 'SYS\_P%' escape '\' )
  loop
      execute immediate
      'select to_char( ' || x.high_value ||
         '-numtodsinterval(1 ,''second''), ''"PART_"yyyy_mm'') from dual'
        into l_str;
   execute immediate
   'alter table audit_trail rename partition "' || 
       x.partition_name || '" to "' || l_str || '"' ;
   end loop;
end;
/

PL/SQL procedure successfully completed.



select a.partition_name, a.tablespace_name, a.high_value,
       decode( a.interval, 'YES' , b.interval ) interval
  from user_tab_partitions a, user_part_tables b
 where a.table_name = 'AUDIT_TRAIL'
   and a.table_name = b.table_name
  order by a.partition_position;


PARTITION_NAME       TABLESPACE_NAME                HIGH_VALUE                               INTERVAL
-------------------- ------------------------------ ---------------------------------------- ----------------------------------------
P0                   USERS                          TIMESTAMP' 1900-01-01 00:00:00'
PART_2010_02         USERS                          TIMESTAMP' 2010-03-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
PART_2010_03         EXAMPLE                        TIMESTAMP' 2010-04-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')
PART_2010_06         USERS                          TIMESTAMP' 2010-07-01 00:00:00'          NUMTOYMINTERVAL(1,'MONTH')

참조 파티셔닝

Oracle 11gR1 이상에서 지원함.
자식 테이블 파티션과 부모 테이블 파티션이 1:1 관계가 있는 상태에서의 파티셔닝

아래 ORDERS - ORDER_LINE_ITEMS 테이블 관계 예제로 확인



-- Parent table

SQL> create table orders
(
    order#      number primary key,
    order_date  date ,
    data        varchar2(30)
)
  enable row movement
  PARTITION BY RANGE (order_date)
  (
   PARTITION part_2009 VALUES LESS THAN (to_date( '01-01-2010', 'dd-mm-yyyy')) ,
   PARTITION part_2010 VALUES LESS THAN (to_date( '01-01-2011', 'dd -mm-yyyy'))
   )
/

Table created.


SQL> insert into orders values 
(1, to_date('01-jun-2009','dd-mon-yyyy'),'xxx') ;

1 row created.

SQL> insert into orders values
(2, to_date('01-jun-2010','dd-mon-yyyy'),'xxx') ;

1 row created.



-- Child table 

SQL> create table order_line_items
(
     order#     number,
     line#      number,
     order_date date, -- manually copied from ORDERS!
     data varchar2(30),
     constraint C1_PK PRIMARY KEY (order# ,line#),
     constraint C1_FK_P foreign key (order#) references ORDERS
  )
   enable row movement
   PARTITION BY RANGE (order_date)
   (
   PARTITION part_2009 VALUES LESS THAN (to_date( '01-01-2010' , 'dd-mm -yyyy' )) ,
   PARTITION part_2010 VALUES LESS THAN (to_date( '01-01-2011' , 'dd-mm-yyyy' ))
   )
/
Table created.


SQL> insert into order_line_items values 
(1, 1, to_date('01-jun-2009','dd-mon-yyyy'),'yyy') ;

1 row created.

SQL> insert into order_line_items values 
(2, 2, to_date('01-jun-2010','dd-mon-yyyy'),'yyy') ;

1 row created.

참조 무결성 제약조건으로, 자식테이블인 ORDER_LINE_ITEMS 파티션을 삭제하기 위해서는
ORDERS 파티션도 함께 삭제되어야 하나 데이터베이스는 이를 알지 못함.



SQL> alter table order_line_items drop partition part_2009 ;

Table altered.

SQL> alter table orders drop partition part_2009 ;
 alter table orders drop partition part_2009
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

참조 파티셔닝은 자식 테이블 파티션을 비우거나, 삭제할 때 부모 테이블의 파티션을 비우거나 삭제할 수 있다.



SQL> drop table order_line_items cascade constraints ;

Table dropped.



SQL> truncate table orders ;

Table truncated.


SQL> insert into orders values 
(1, to_date('01-jun-2009','dd-mon-yyyy') , 'xxx') ;

1 row created.


SQL> insert into orders values 
(2, to_date('01-jun-2010','dd-mon-yyyy') , 'xxx') ;

1 row created.

-- 이후에 자식 테이블을 생성한다. 


SQL> create table order_line_items
(
     order#     number,
     line#      number,
     data       varchar2(30),
     constraint C1_PK PRIMARY KEY (order# ,line#),
     constraint C1_FK_P foreign key (order#) references ORDERS
  )
   enable row movement
   PARTITION BY reference (C1_FK_P)       -- PARTITION BY REFERENCE 문장 사용
/

Table created.


SQL> insert into order_line_items values
(1, 1, 'yyyy') ;

1 row created.


SQL> insert into order_line_items values
(2, 1, 'yyyy') ;

1 row created.


SQL> select table_name ， partition_name
  from user_tab_partitions
  where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
 order by table_name, partition_name ;


TABLE_NAME                     PARTITION_NAME
------------------------------ --------------------
ORDERS                         PART_2009
ORDERS                         PART_2010
ORDER_LINE_ITEMS               PART_2009     <---
ORDER_LINE_ITEMS               PART_2010     <---

-- 자식 테이블 파티션도 함께 생성 된 것을 확인할 수 있다.


SQL> alter table orders drop partition part_2009 update global indexes ;

Table altered.


-- 2009년 파티션이 ORDERS, ORDER_LINE_ITEMS 에서 모두 DROP 된 것을 확인할 수 있다.


SQL>  select table_name ， partition_name
  from user_tab_partitions
  where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
 order by table_name, partition_name ;
 
 
 TABLE_NAME                     PARTITION_NAME
------------------------------ --------------------
ORDERS                         PART_2010
ORDER_LINE_ITEMS               PART_2010


-- 파티션 ADD 도 가능하다.

SQL> alter table orders add partition part_2011 values less than ( to_date('01-01-2012','dd-mm-yyyy')) ;

Table altered.


SQL>  select table_name ， partition_name
  from user_tab_partitions
  where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
 order by table_name, partition_name ;
 
 
TABLE_NAME                     PARTITION_NAME
------------------------------ --------------------
ORDERS                         PART_2010
ORDERS                         PART_2011      <--
ORDER_LINE_ITEMS               PART_2010
ORDER_LINE_ITEMS               PART_2011      <--



- *ENABLE ROW MOVEMENT*
  : UPDATE 시 파티션 값을 수정하고, 다른 파티션으로 로우 이동을 일으키는 데이터 수정을 허용함.
  - Oracle 8i 부터 지원
  - 파티션 key를 수정은 가능하였으나, row 이동은 허용되지 않았다.
  

SQL> select '2010', count(*) from order_line_items partition(part_2010)
     union all
     select '2011', count(*) from order_line_items partition (part_2011) ;
     
'201   COUNT(*)
---- ----------
2010          1   <---
2011          0


-- 현 상태에서, month 를 update 하여 다른 파티션으로 row를 이동시킨다.

SQL> update orders set order_date = add_months(order_date,12) ;

1 row updated.

SQL> select '2010', count(*) from order_line_items partition(part_2010)
     union all
     select '2011', count(*) from order_line_items partition (part_2011) ;

'201   COUNT(*)
---- ----------
2010          0
2011          1   <---  부모테이블에 대한 수정이 자식테이블에 동일하게 반영됨.

1. Oracle 11gR2 까지, 참조/인터벌 파티셔닝의 조합은 지원 되지 않음.
2. 부모 파티션을 인터벌 파티션으로 생성 시 오류 발생함.


create table order line items
*
ERROR at line 1:
ORA-14659: Partitioning method of the parent table is not supported

복합 파티셔닝

범위/해시/리스트 파티션의 조합
버전에 따라 조합 가능한 파티셔닝 타입이 다름.
복합 파티셔닝 시, 주 파티션에 대한 세그먼트가 없고 서브파티션만 세그먼트가 존재함.



SQL> CREATE TABLE composite_example
(  range_key_column date,
   hash_key_column  int,
   data             varchar2(20)
)
  PARTITION BY RANGE (range_key_column)
  subpartition by hash(hash_key_column) subpartitions 2
  (
     PARTITION part_1
     VALUES LESS THAN ( to_date( '01/01/2008','dd/mm/yyyy' ) )
     (
       subpartition part_1_sub_1 ,
       subpartition part_1_sub_2
     ),
     PARTITION part_2
     VALUES LESS THAN ( to_date( '01/01/2011','dd/mm/yyyy' ) )
     (
       subpartition part_2_sub_1,
       subpartition part_2_sub_2
     )
  )
/



-- range_key_column 으로 먼저 분리 후, hash(123) = sub_1 로 분리됨

SQL> insert into composite_example ( range_key_column, hash_key_column, data )
values (to_date('23-feb-2007','dd-mon-yyyy') , 123, 'application data') ;

1 row created.


SQL> insert into composite_example ( range_key_column, hash_key_column, data )
values (to_date('27-feb-2010','dd-mon-yyyy') , 456, 'application data') ;


1 row created.

서브파티션의 개수가, 반드시 같아야 하는 것은 아니다



SQL> CREATE TABLE composit_range_list_example 
(  range_key_column    date,
   code_key_column     int ,
   data                varchar2(20) 
)
PARTITION BY RANGE (range_key_column)
subpartition by list (code_key_column)
(
  PARTITION part_1 VALUES LESS THAN (to_date('01/01/2008','dd/mm/yyyy'))
  (
      subpartition part_1_sub_1 values (1,3,5,7),
      subpartition part_1_sub_2 values (2,4,6,8)
  ),
  PARTITION part_2 VALUES LESS THAN (to_date('01/01/2011','dd/mm/yyyy'))
  (
      subpartition part_2_sub_1 values (1,3),
      subpartition part_2_sub_2 values (5,7),
      subpartition part_2_sub_3 values (2,4,6,8)
  )
)
/

Table created.

로우 이동

파티션 키 컬럼을 수정할 경우,

수정 작업 후 다른 파티션을 사용하도록 하지 않는다. ( 파티션 키 값이 동일한 파티션 범위에 해당하는 값으로 변경되었을 경우)
수정 작업으로 다른파티션으로 로우 이동 발생. 테이블에 ROW MOVEMENT 가 설정되어야 이동 가능.



SQL> CREATE TABLE range_example
(  range_key_column date ,
   data varchar2(20)
)
  PARTITION BY RANGE (range_key_column)
  (PARTITION part_1 VALUES LESS THAN
  (to_date( '01/01/2010' , 'dd/mm/yyyy' )),
   PARTITION part_2 VALUES LESS THAN
  (to_date('01/01/2011' , 'dd/mm/yyyy' ))
   )
/

Table created.

SQL> insert into range_example (range_key_column, data)
  values (to_date('15-dec-2009 00:00:00','dd-mon-yyyy hh24:mi:ss') , 'application data...') ;

1 row created.

SQL> insert into range_example ( range_key_column, data )
values (to_date('01-jan-2010 00:00:00','dd-mon-yyyy hh24:mi:ss')-1/24/60/60,
 'application data...' );

1 row created.

SQL> select * from range_example partition (part_1 );

RANGE_KEY_COLU DATA
-------------- --------------------
20091215000000 application data...
20091231235959 application data...

한 로우에 대해 PART_1 에 남아있도록 RANGE_KEY_COLUMN 컬럼 값을 수정한다.



SQL> update range_example 
    set range_key_column = trunc(range_key_column)
  where range_key_column =
       to_date ( '31-dec-2009 23:59:59', 'dd-mon-yyyy hh24:mi:ss') ;

PART_2 파티션에 속하도록 RANGE_KEY_COLUMN 값을 수정한다



SQL> update range_example 
    set range_key_column = to_date('01-jan-2010','dd-mon-yyyy')
  where range_key_column = to_date('31-dec-2009','dd-mon-yyyy') ;

        update range_example
               *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

=> ROW MOVEMENT 가 활성화 되지 않았기 때문에 오류 발생함.
=> 파티션에서 다른 파티션으로 로우 이동 시 테이블에 ROW MOVEMENT 를 활성화 할 수 있다.
로우 이동 시, 해당 로우의 ROWID 가 변경된다.



SQL> select rowid from range_example 
   where range_key_column = to_date ('31-dec-2009','dd-mon-yyyy') ;

ROWID
------------------
AAAMhUAABAAAL0BAAB


SQL> alter table range_example enable row movement ;

Table altered.


SQL> update range_example 
    set range_key_column = to_date('01-jan-2010','dd-mon-yyyy')
  where range_key_column = to_date('31-dec-2009','dd-mon-yyyy') ;

1 row updated.

SQL> select rowid from range_example 
   where range_key_column = to_date ('01-jan-2010','dd-mon-yyyy') ;

ROWID
------------------
AAAMhVAABAAAMMBAAA

row movement 활성화 = 파티션 키를 수정 가능하도록 허용하는 것.
내부적으로 로우를 삭제하고 재입력하는 것처럼 수행된다.

update 에 의해 ROWID 가 변경되는 경우

IOT의 기본 키를 수정할 때, 해당 로우의 유니버셜 ROWID는 변경된다.
오라클 10g 이상에서 alter table shrink / flashback table 시 에도 로우의 ROWID 가 변경된다.

물리적인 DELETE, INSERT 작업을 수정으로 인식한다.
참조 키 제약조건으로 인해 자식테이블에서 DELETE 되지 않을 수 있다.
일반적인 수정보다 큰 비용이 들게 되므로, 잦은 파티션 키 수정과 파티션 이동은 좋지 않다.

요약

해시 파티셔닝은 자연적인 범위에 의해 파티션할 수 없는 데이터에 적합. 데이터 분산의 효과. 그러나 범위조건으로는 파티션 제외가 불가하다.
리스트 파티셔닝은 컬럼 값이 이산집합인 데이터에 적합. 쿼리에서 파티션 제외가 편리함.
복합 파티셔닝은 큰 파티션의 많은 I/O요청을 많은 장치로 분산할 수 있다.
범위 파티셔닝은 데이터를 범위 조건으로 조회 시 가장 유용하다.
범위 파티션 내에서 해시나 리스트 파티션 사용은 결과 데이터가 많거나, 단일 범위 파티션에 대한 PDML, 병렬인덱스 처리에 유용하다.