14S_테이블 파티셔닝 (by feelie) [2015.01.04]
테이블 파티셔닝
파티션 테이블 종류
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 활성화 = 파티션 키를 수정 가능하도록 허용하는 것.
- 내부적으로 로우를 삭제하고 재입력하는 것처럼 수행된다.
update 에 의해 ROWID 가 변경되는 경우
- IOT의 기본 키를 수정할 때, 해당 로우의 유니버셜 ROWID는 변경된다.
- 오라클 10g 이상에서 alter table shrink / flashback table 시 에도 로우의 ROWID 가 변경된다.
요약
- 범위 파티셔닝은 데이터를 범위 조건으로 조회 시 가장 유용하다.
- 해시 파티셔닝은 자연적인 범위에 의해 파티션할 수 없는 데이터에 적합. 데이터 분산의 효과. 그러나 범위조건으로는 파티션 제외가 불가하다.
- 리스트 파티셔닝은 컬럼 값이 이산집합인 데이터에 적합. 쿼리에서 파티션 제외가 편리함.
- 복합 파티셔닝은 큰 파티션의 많은 I/O요청을 많은 장치로 분산할 수 있다.
- 범위 파티션 내에서 해시나 리스트 파티션 사용은 결과 데이터가 많거나, 단일 범위 파티션에 대한 PDML, 병렬인덱스 처리에 유용하다.