by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_테이블 파티셔닝




테이블 파티셔닝

*파티션 테이블 종류

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승이어야 한다.
  • 검증
    1. N개의 파티션을 가진 해시 파티션 테이블을 자동으로 생성하는 프로시저
    2. 파티션 로우 수 조회, 그래프로 표기하여 데이터의 분포 상태를 확인

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


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

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

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

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

요약

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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.