Partition Overview

테이블, 인덱스, IOT(index-organized table)를 여러 개의 세그먼트로 파티셔닝하고 각각 다른 테이블스페이스에 할당 할 수 있는 Enterprise Edition의 기능이다.
파티셔닝된 테이블은 하나의 논리적 엔티티로 간주되지만 각각의 파티션은 별도의 세그먼트로 저장 데이터의 처리 및 관리를 위한 다양하고 세분화된 솔루션을 선택할 수 있다.

Oracle Database 11g에는 레퍼런스(reference) 파티셔닝, 인터벌(interval) 파티셔닝, 가상 컬럼의 파티셔닝, 확장형 컴포지트(extended composite) 파티셔닝 등의 옵션이 새로 추가되면서 파티셔닝 설계의 가능성과 관리성이 대폭적으로 향상되어 비즈니스 요구에 따라 OLAP 뿐 아니라 OLTP에서도 파티셔닝을 적용할 수 있다.

버전에 따른 Oracle Database 파티셔닝 기능

확장형 컴포지트(Extended Composite) 파티셔닝
컴포지트 파티셔닝을 이용하면 파티션에 서브파티션을 생성하고 테이블의 구조를 더욱 세분화할 수 있다.
11g 이전에는 주로 액세스되는 2 개의 Demension 중 하나를 선택하여 Range파티션으로 선택하면 나머지 하나를 subparition으로 할 때 나중에 사용되는 파티션은 성능이 저하될 수 있었다

Oracle Database 11g에서는 이러한 문제를 매우 쉽게 해결할 수 있습니다. 새로운 버전에서는 rang-hash, range-list 컴포지트 파티셔닝 이외에도 다양한 대안이 제공되고 있기 때문입니다.

Range-range
Range-hash
Range-list
List-range
List-hash
List-list

Oracle Partitioning의 기본 기능 소개

데이터베이스 관리자의 관점에서 볼 때, 파티션 된 오브젝트는 일괄적으로 또는 개별적으로 관리가 가능한 여러 개의 조각으로 구성되며, 따라서 오브젝트 관리에 대한 유연성을 크게 개선할 수 있습니다.
애플리케이션의 관점에서 볼 때에는 파티션 된 테이블은 파티션 되지 않은 테이블과 아무런 차이가 없습니다. 그러므로 애플리케이션 변경 작업은 필요하지 않습니다.

테이블은 '파티셔닝 키(partitioning key)'을 통해 분할되며 파티셔닝 키란 특정 로우가 어떤 파티션에 위치하는지 정의하는 일련의 컬럼을 말합니다.

< Oracle 11g 에서의 파티셔닝 전략 >

.jpg
IOT(index-organized table)에는 Range Partitioning, List Partitioning, Hash Partitioning이 적용될 수
있습니다.

관리성 개선을 위한 Oracle Partitioning의 활용

데이터베이스 오브젝트가 아닌 파티션 단위로 작업을 실행함으로써 유지보수 작업의 관리 편의성을 한층 개선하는 것이 가능

성능 개선을 위한 Oracle Partitioning의 활용

Partitioning Pruning:
오라클은 인덱싱, 조인, 병렬 액세스 등의 테크닉에서 Partition Pruning을 활용할 수 있도록 지원할
예정입니다.

Partition-wise Join:

• Partition-wise Join은 서로 다른 두 개의 테이블을 조인 처리함으로써 적용되며, 두 테이블 간 조인 키(join key)를 기준으로 파티셔닝 그룹으로 조인하게 됨
• Partition-wise Join은 대규모 조인 작업을 보다 작은 크기로 분할함으로써 조인 실행 속도를 개선
• 순차적, 병렬적 실행 모델에서 모두 뛰어난 효과를 제공합니다.

Oracle Partitioning을 이용한 정보 생명주기 관리

• 개별 파티셔닝의 독립성은 "계층화된 아카이빙(tired archiving)" 전략을 위한 핵심 기반
• 파티션(또는 파티션 그룹)을 서로 다른 스토리지 계층에 저장함으로써 각 파티션에 적합한 물리적 특성과 비용효율성을 제공할 수 있다.
• Oracle Partitioning을 활용함으로써, END USER에게 제공되는 서비스에 영향을 미치지 않은 상태에서 스토리지 비용을 극적으로 절감하는 것이 가능하다.

Oracle 11g 에서의 새로운 Partitioning

1) Reference Partitioning sample1

• 부모 테이블에서 partition key(분할 키)를 상속함으로써 논리적으로 equi-partitioned 되도록 테이블 참조를 허용.
• 기본 키-외래 키 관계를 통해 자식 테이블 파티셔닝을 부모 테이블로부터 상속받지만 파티셔닝 키는 자식 테이블에 저장하지 않음..
• 레퍼런스 파티션은 자식 테이블을 부모 테이블과 동일한 방법으로 파티셔닝 하려 하지만 두 테이블이 동일한 컬럼을 포함하고 있지 않은 경우 매우 유용하게 활용 될 수 있다.
• 각각의 자식 테이블에 장황한 파티셔닝 정의 구문을 명시적으로 선언할 필요가 없다

-- parent table
create table customers
(
cust_id number primary key,
cust_name varchar2(200),
rating varchar2(1) not null
)
partition by list (rating)
(
partition pA values ('A'),
partition pB values ('B')
);

-- child table Reference Partitioning 적용
create table sales
(
sales_id number primary key,
cust_id number not null,        -- Reference Partitioning을 위해 not null이 보장되어야함
sales_amt number,
constraint fk_sales_01 foreign key (cust_id)
           references customers
)
partition by reference (fk_sales_01);

2) Interval Partitioning sample2

• range partitions의 creation을 자동화한다.
• 동일한 크기의 간격을 기준으로 파티셔닝을 정의하여 첫 번째 생성되는 파티션을 제외한 모든 파티션은 조건에 맞는 데이터가 입력되는 시점에 자동으로 생성됨
• Range Partitioning 기능을 확장시킨 것.
• 파티션을 특정 테이블스페이스에 저장하고 싶다면 store in 구문을 이용하면 됩니다:
  interval (numtoyminterval(1,'MONTH'))
  store in (TS1,TS2,TS3)
  이제 파티션은 TS1, TS2, TS3 테이블스페이스에 라운드 로빈 방식으로 저장됩니다.
  애플리케이션 개발자가 에는 어떻게 해야 할까요? 물론

-- Interval Partitioning

-- 일반적인 Range partition
create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')),
partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd'))
);

-- Interval 을 적용한 Range partition
create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
-- store in       -- 특정 테이블스페이스를 지정할 경우 store 문장추가
-- (TS1,TS2,TS3)  -- 여러개 테이블스펭스가 지정될 경우 라운드로빈방식으로 저장됨
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);

3) Extended Composite Partitioning sample

• 두 dimensions에 따라 논리적인 분할이 가능
  : Range-Range
  : Range-List
  : Range-Hash
  : List-List
  : List-Range
  : List-Hash
  : Interval-Range *
  : Interval-List *
  : Interval-Hash *

4) Virtual Column Partitioning sample3

• 파티셔닝 키는 가상 컬럼(virtual column)을 기반으로 설정.
• 가상 컬럼은 디스크에 저장되지 않으며 메타데이터의 형태로만 존재

5) System Partitioning

-- 애플리케이션에서 어떤 레코드가 어떤 파티션에 저장되어야 하는지 컨트롤
-- 애플리케이션이 데이터를 입력하면서 파티션을 직접 지정해 주어야 한다.

-- System Partitioning 

create table sales3
(
sales_id number,
product_code number,
state_code number
)
partition by system
(
partition p1 tablespace users,
partition p2 tablespace users
);

기타 유용한 Oracle Database 11g 파티셔닝 기능

단일 파티션을 위한 테이블스페이스 전송

-- 데이터베이스에서 데이터를 전송하기 위한 가장 빠른 방법으로 활용가능
– 11g 이전에는 기존에는 테이블스페이스단위로 처리가 가능했었음
– 11g 에서 단일 파티션만을 전송하고 플러그인 처리가 가능함

하나의 파티션만 exp/imp해서 무엇이 가능할까?

--sql Command
--connect system
create or replace directory data_pump_dir as 'C:\oradata';
grant read, write on directory data_pump_dir to sh;

--connect sh
drop tablespace ts1 including contents and datafiles;
create tablespace ts1 datafile 'c:\oradata\ts1.f' size 100m reuse;
drop tablespace ts2 including contents and datafiles;
create tablespace ts2 datafile 'c:\oradata\ts2.f' size 100m reuse;

drop table sales5;
create table sales5 
(
	PROD_ID         NUMBER NOT NULL,
	CUST_ID         NUMBER NOT NULL,
	TIME_ID         DATE NOT NULL,
	CHANNEL_ID      NUMBER NOT NULL,
	PROMO_ID        NUMBER NOT NULL,
	QUANTITY_SOLD   NUMBER (10,2) NOT NULL,
	AMOUNT_SOLD     NUMBER (10,2) NOT NULL
)
PARTITION BY RANGE (CHANNEL_ID)
(
  PARTITION CT VALUES LESS THAN (4) 	TABLESPACE ts1
, PARTITION NY VALUES LESS THAN (maxvalue) 	TABLESPACE ts2
);

alter table sales5 nologging;

insert /*+ parallel(a 4) */ into sales5 a 
select /*+ parallel(b 4) */ * from sales b;

commit;

alter table sales5 logging;

-- connect system
alter tablespace ts1 read only;

drop table sh.sales5;
drop tablespace ts1 including contents and datafiles;


-- Command Line
C:\oracle_home>expdp system/elpasil tables=sh.sales5:ct transportable=always directory=data_pump_dir dumpfile=p_ct.dmp

Export: Release 11.2.0.1.0 - Production on 금 10월 21 23:54:29 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

접속 대상: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
"SYSTEM"."SYS_EXPORT_TABLE_01" 시작 중: system/******** tables=sh.sales5:ct transportable=always directory=data_pump_dir dumpfile=p_ct.dmp
객체 유형 TABLE_EXPORT/TABLE/PLUGTS_BLK 처리 중
객체 유형 TABLE_EXPORT/TABLE/TABLE 처리 중
객체 유형 TABLE_EXPORT/TABLE/END_PLUGTS_BLK 처리 중
마스터 테이블 "SYSTEM"."SYS_EXPORT_TABLE_01"이(가) 성공적으로 로드됨/로드 취소됨
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01에 대해 설정된 덤프 파일:
  C:\ORADATA\P_CT.DMP
******************************************************************************
전송 가능한 테이블스페이스 TS1에 필요한 데이터 파일:
  C:\ORADATA\TS1.F
"SYSTEM"."SYS_EXPORT_TABLE_01" 작업이 23:54:38에서 성공적으로 완료됨


--sql Command
drop table sh.sales5;

drop tablespace ts1 including contents and datafile;

-- Command Line
C:\oracle_home>impdp partition_options=departition dumpfile=p_ct.dmp transport_datafiles='C:\ORADATA\T
S1.F'

Import: Release 11.2.0.1.0 - Production on 토 10월 22 00:09:01 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

사용자 이름: system
비밀번호:

접속 대상: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
마스터 테이블 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"이(가) 성공적으로 로드됨/로드 취소됨
"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 시작 중: system/******** partition_options=departition dumpfile=p_ct.dmp transport_datafiles='C:\ORADATA\TS1.F'
객체 유형 TABLE_EXPORT/TABLE/PLUGTS_BLK 처리 중
객체 유형 TABLE_EXPORT/TABLE/TABLE 처리 중
객체 유형 TABLE_EXPORT/TABLE/END_PLUGTS_BLK 처리 중
"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 작업이 00:09:08에서 성공적으로 완료됨


--sql Command
SQL> select segment_name
       from dba_segments
      where tablespace_name = 'TS1';

SEGMENT_NAME                                                                     
---------------------------------------------------------------------------------
SALES5_CT                                                                        

1 rows selected.
 

SQL> select * 
       from SALES5_CT
      where rownum <= 10;

PROD_ID     CUST_ID     TIME_ID             CHANNEL_ID  PROMO_ID    QUANTITY_SO AMOUNT_SOLD
----------- ----------- ------------------- ----------- ----------- ----------- -----------
        117        1553 1998/01/04 00:00:00           3         999           1       10.94
        117        2819 1998/01/04 00:00:00           3         999           1       10.94
        117        3214 1998/01/04 00:00:00           3         999           1       10.94
        117        4369 1998/01/04 00:00:00           3         999           1       10.94
        117        4463 1998/01/04 00:00:00           3         999           1       10.94
        117        6485 1998/01/04 00:00:00           3         999           1       10.94
        117        6777 1998/01/04 00:00:00           3         999           1       10.94
        117        8618 1998/01/04 00:00:00           3         999           1       10.94
        117        9316 1998/01/04 00:00:00           3         999           1       10.94
        117        9573 1998/01/04 00:00:00           3         999           1       10.94

10 rows selected.