1. 테이블 파티셔닝
   1. 파티션 테이블 종류
   2. 범위 파티셔닝
   3. 해시 파티셔닝
   4. 리스트 파티셔닝
   5. 인터벌 파티셔닝
   6. 참조 파티셔닝
   7. 복합 파티셔닝
   8. 로우 이동

테이블 파티셔닝

파티션 테이블 종류

1. 범위 파티셔닝(range partitioning)

• 함께 저장되어야 할 데이터의 범위를 지정하여 테이블을 파티션 한다.

2. 해시 파티셔닝 (hash partitioning)

• 해시 파티션 키 컬럼에 해시함수를 적용하고, 해시 함수의 값에 따라 파티션이 결정된다.

3. 리스트 파티셔닝 (list partitioning)

• 이산 값들을 같은 집합으로 묶어 지정하여 함께 저장될 데이터를 결정한다.

4. 인터벌 파티셔닝 (interval partitioning)

• 범위 파티션과 유사하지만, 데이터 발생시 데이터베이스 스스로 새로운 파티션을 생성한다.

5. 참조 파티셔닝 (reference partitioning)

• 참조키에 의해 강제된 부모/자식 관계에서 자식 테이블이 부모 테이블의 파티셔닝을 상속하여 테이블을 파티션 한다.

6. 복합 파티셔닝 (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

-- 데이터 입력(part_1)

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...') ;

-- 데이터 입력(part_2)
* 입력된 로우는 파티션 범위가 작거나 같은것이 아니라 작다는것에 유의

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...

해시 파티셔닝

• 여러 다른 디스크에 데이터를 잘 분산시키거나 더 관리하기 쉬운 청크로 데이터를 분리하도록 설계

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

-- 다이나믹 SQL을 사용하여 T 테이블을 삭제

    begin
        execute immediate 'drop table t';
    exception when table_or_view_does_not_exist
        then null;
    end;

-- 입력 변수(p_nhash)를 사용하고, 다이나믹 SQL을 사용하여 해시 테이블 T을 생성

    execute immediate '
        create table t (id)
        partition by hash(id)
        partitions ' || p_nhash || '
        as
        select rownum
        from all_objects ';

-- 입력 변수(p_nhash)에 의해 생성된 모든 파티션 정보를 사용하여 동작 FROM 절을 l_text 변수에 저장

    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;

-- 파티션별 cnt, 막대 그래프 생성

    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...');
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 파티션 테이블에 정의되지 않는 나머지 데이터 입력을 위해 세 번째 파티션(DEFAULT)을 생성한다

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

Table altered.

• 주의할 점 : 리스트 파티션 테이블에 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 될 경우 에러 발생되며 실패하지만, 인터벌 파티셔닝의 경우 새로운 파티션을 생성하여 데이터를 저장한다.

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,            -- 반정규화 컬럼
     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   <---  부모테이블에 대한 수정이 자식테이블에 동일하게 반영됨.

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

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...

• 한 로우(20091231235959)에 대해 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 활성화 = 파티션 키를 수정 가능하도록 허용하는 것.
• 내부적으로 로우를 삭제하고 재입력하는 것처럼 수행된다.

요약

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