<<戻る

Oracleのパーティション表とパーティション索引(PARTITIONING)

概要

大規模な表や索引をパーティションという単位で分割して管理しやすいようにしいます。これは、ファイルシステムのボリューム機能と同じようなものです。

導入利点

パーティションレベルのデータロード、索引作成、バックアップ、リカバリが可能
表全体ではなく、パーティションのサブセットに問い合わせが可能なのでパフォーマンスアップ
パーティション化によりメンテナンス操作による停止時間を削減
パーティション化はアプリケーションと独立しているので、既存のプログラム修正の必要なし

パーティション表

LONGデータ型、LONG RAW型データ以外の表はパーティションに分割できます。また最大64,000の分割が可能です。

パーティション索引構成表

索引構成表はレンジ・パーティション、ハッシュ・パーティション化がサポートされています。

パーティション化の種類

レンジ・パーティション化:1年のデータを1月から3月を1パーティションとするような範囲で分ける方法
リスト・パーティション化:哺乳類、爬虫類などのグループ分けする方法
ハッシュ・パーティション化:ハッシュ値を使った方法
コンポジット・パーティション化:上記を複合したもの

どのような場合にパーティション化を検討するか

2GB以上の表

サンプルソースコード

-- range_partition.sql
-- auhtor satoshi okita
-- since 2004/04/08 12:10:00 
-- -------------------
-- 
-- RANGE PARTITIONING 
--
drop table sales_range cascade constraints;
create table sales_range 
( salesman_id        number(5),
  salesman_name        varchar2(30),
  sales_amount        number(10),
  sales_date        date)
PARTITION BY RANGE(sales_date)
(
  PARTITION sales_Q1_2004 values less than (to_date('2004/04/01',
'YYYY/MM/DD')),
  PARTITION sales_Q2_2004 values less than (to_date('2004/07/01',
'YYYY/MM/DD')),
  PARTITION sales_Q3_2004 values less than (to_date('2004/10/01',
'YYYY/MM/DD')),
  PARTITION sales_Q4_2004 values less than (to_date('2005/01/01',
'YYYY/MM/DD'))
);

--
-- LIST PARTITIONING
--
drop table sales_list cascade constraints;
create table sales_list (
  salesman_id        number(5),
  salesman_name        varchar2(30),
  sales_state        varchar2(20),
  sales_amount        number(10),
  sales_date        date
)
PARTITION BY LIST(sales_state)
(
  PARTITION sales_asia values ('asia'),
  PARTITION sales_us values ('usa','kanada'),
  PARTITION sales_euro values ('eu'),
  PARTITION sales_other values (DEFAULT)
);

--
-- HASH PARTITIONING
--
drop table sales_hash2 cascade constraints;
drop tablespace hash_p1 including contents and datafiles cascade
constraints;
drop tablespace hash_p2 including contents and datafiles cascade
constraints;
drop tablespace hash_p3 including contents and datafiles cascade
constraints;
drop tablespace hash_p4 including contents and datafiles cascade
constraints;
--
create tablespace hash_p1 datafile
'/opt/app/oracle/oradata/test/partition_01.dbf' size 20m autoextend on;
create tablespace hash_p2 datafile
'/opt/app/oracle/oradata/test/user/partition_02.dbf' size 20m
autoextend on;
create tablespace hash_p3 datafile
'/opt/app/oracle/oradata/test/user/partition_03.dbf' size 20m
autoextend on;
create tablespace hash_p4 datafile
'/opt/app/oracle/oradata/test/user/partition_04.dbf' size 20m
autoextend on;
create table sales_hash 
( salesman_id        number(5),
  salesman_name        varchar2(30),
  sales_amount        number(10),
  week_no        number(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (hash_p1, hash_p2, hash_p3, hash_p4);
/

パーティション索引

パーティション索引には、大きく分けてローカルとグローバルがあります。ローカルの場合は、パーティション表と対となる索引を作成します。これは明示的に登録や削除を行うものではなく、パーティション表を作成した際に自動的に作られるものです。グローバルは、ある項目ごとにパーティション索引を作成することです。普通のテーブルでも、何らかの索引が必要になる場合がありますが、これも同じようなものです。

サンプルソースコード

-- partition_idx.sql
--
--
drop table employee cascade constraints;
create table employee (
  employee_id        number(4) not
null,
  last_name        varchar2(10),
  department_id        number(2)
) PARTITION BY RANGE (department_id) ( 
  PARTITION employee_part1 values less than (11) tablespace users,
  PARTITION employee_part2 values less than (21) tablespace users,
  PARTITION employee_part3 values less than (31) tablespace users
);
-- local index
--create index employee_local_idx on employee(employee_id) local;
-- global index
--create index employee_global_idx on employee(employee_id);

--
-- GROBAL PARTIION INDEX
--
create index employee_global_part_idx on employee(employee_id)
GLOBAL PARTITION BY RANGE(employee_id) (
  PARTITION p1 values less than (5000),
  PARTITION p2 values less than (MAXVALUE)
);

用語

パーティション表やパーティション索引には、いくつか用語が出てきますので今後、インターネットの検索や調べていくためのキーワードとして記述しておきます。

パーティションキー: パーティションを決定する１つ以上の列のセット
パーティション・プルーニング: Oracleサーバが、パーティションとサブパーティションを認識しているため不要なパーティションやサブパーティションはSQL文から除外（プルーニング）します。これにより検索するデータ量が少なくなるためパフォーマンスが向上します。 Where句の書き方などでプルーニングが出来なくなる場合は、パーティションを使う意味が無くなります。
パーティション・ワイズ結合: 結合列に沿ってパーティション化された２つの表を結合するするときにできる最適化の事です。
パラレルDML: parallelDMLとは並行してDMLを実行する事、パフォーマンス向上が期待できます。

参考資料

Oracle9i データベース概要リリース2（9.2）.pdf　１１章　パーティション表とパーティション索引