S-JIS[2006-01-09/2010-07-20] 変更履歴

パーティション分割

Oracleでは、テーブルにパーティションを設けて分割することが出来る。
件数が大量になるテーブルは、うまく実体を分割しておくことで検索効率を上げられる。
(ここではOracle10g Enterprise Editionで実験)

パーティションの種類
種類 概要 備考 使用目的 更新日
レンジパーティション 項目値の範囲に応じて別々のパーティションに格納する方式 範囲と、それに応じてどのパーティションに格納するかはこちらで決められる。 日付毎に分ける場合とか? 2007-12-11
ハッシュパーティション 複数の項目の値に応じて別々のパーティションに格納する方式 パーティションの個数は決められるがハッシュ関数は決められないので、実際にどのパーティションに格納されるか不明。 キーとなるID毎に分散させる場合とか? 2010-07-20
リストパーティション 単独の項目の値に応じて別々のパーティションに格納する方式 値と、それに応じてどのパーティションに格納するかはこちらで決められる。 値の種類が少なめで決まっている場合向けかなぁ?  

パーティションを使うには、Enterprise Editionである必要がある。[2009-12-24]

SQL> create table part_test
  2  (key number)
  3  partition by hash(key);
create table part_test
*
行1でエラーが発生しました。:
ORA-00439: 機能は使用可能ではありせん: Partitioning

SQL> select * from v$option where parameter ='Partitioning';
PARAMETER     VALUE
------------- ------
Partitioning  FALSE

レンジパーティション

決まった年月で範囲を分ける例

CREATE TABLE テーブル名
(項目 型,
 項目 型,
 …
)
PARTITION BY RANGE (項目,項目…)
(PARTITION パーティション名称1 VALUES LESS THAN(TO_DATE('2005/01/01','YYYY/MM/DD'))
 TABLESPACE スペース名 STORAGE(INITIAL 20K NEXT 10K),
 PARTITION パーティション名称2 VALUES LESS THAN(TO_DATE('2005/02/01','YYYY/MM/DD'))
 TABLESPACE スペース名,
 PARTITION パーティション名称3 VALUES LESS THAN(TO_DATE('2005/03/01','YYYY/MM/DD'))
 TABLESPACE スペース名,
 PARTITION パーティション名称4 VALUES LESS THAN(MAXVALUE)
 TABLESPACE スペース名
)
ENABLE ROW MOVEMENT;

それぞれのパーティションの上限を指定していくことによって範囲を分けていく。
テーブルスペースのSTORAGE部分は省略可。

上限値には文字列や定数・数値と、最大値を表す「MAXVALUE」が指定可能。[2007-12-11]
NULLは指定できない。(指定するとORA-14019のエラー)

Date型を扱う場合は書式を指定したTO_DATETO_DATE(文字列,書式))でないとエラーになる。

SQL> CREATE TABLE part_test
  2  (key number,
  3   ymd date,
  4   num number,
  5   data varchar2(10)
  6  )
  7  PARTITION BY RANGE (ymd)
  8  (PARTITION part001 VALUES LESS THAN(to_date('2005/05/01'))
  9  );
CREATE TABLE part_test
       *
行1でエラーが発生しました。:
ORA-01861: リテラルがフォーマット文字列と一致しません

いちいち「VALUES LESS THAN(値)」の方に演算を書くのは面倒だが、「RANGE(項目)」の方では演算を指定できない。

PARTITION BY RANGE (to_char(ymd,'YYMM'))
                           *
行7でエラーが発生しました。:
ORA-00907: 右カッコがありません。

レンジは具体的に決まった値でなければならない為、年月のようにどんどん増えていくものを自動で拡張していくことは出来なさそう。

レンジパーティションの追加

ALTER TABLE パーティション化されたテーブル
ADD PARTITION part200507 VALUES LESS THAN(TO_DATE('2005/08/01','YYYY/MM/DD'));

レンジパーティションの分割

ALTER TABLE パーティション化されたテーブル
SPLIT PARTITION パーティション名
AT(TO_DATE('2005/01/01','YYYY/MM/DD'))
INTO(PARTITION パーティション名A TABLESPACE hoge,
     PARTITION パーティション名B TABLESPACE hage)
;

レンジパーティションの統合(結合)

ALTER TABLE パーティション化されたテーブル
MERGE PARTITIONS パーティション名1,パーティション名2
INTO PARTITION パーティション名1
;

レンジパーティションのレンジの変更

レンジパーティションのレンジの変更は、統合分割で行う。

レンジパーティションの定義の確認

SQL> select partition_name,high_value
  2  from user_tab_partitions
  3  where table_name='PART_TEST';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
PART200506                     TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200507                     TO_DATE(' 2005-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200508                     TO_DATE(' 2005-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200509                     TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

4行が選択されました。

ハッシュパーティション

CREATE TABLE テーブル名
(項目 型,
 項目 型,
 …
)
PARTITION BY HASH(項目,項目,…)
PARTITIONS 3
STORE IN(スペース名1,スペース名2,…)
;

HASH(項目)」には演算を指定することは出来ない。

ハッシュパーティションでパーティション名を指定するには、以下の様に記述する。[2010-07-20]

CREATE TABLE テーブル名
(項目 型,
 項目 型,
 …
)
PARTITION BY HASH(項目,項目,…)
(
 PARTITION パーティション名1,
 PARTITION パーティション名2,
 PARTITION パーティション名3
);

リストパーティション

CREATE TABLE テーブル名
(項目 型,
 項目 型,
 …
)
PARTITION BY LIST(項目)
(
 PARTITION パーティション名称1 VALUES(値,値,…) TABLESPACE スペース名,
 PARTITION パーティション名称2 VALUES(値,値,…) TABLESPACE スペース名,
 PARTITION パーティション名称3 VALUES(DEFAULT)  TABLESPACE スペース名
);

LIST(項目)」には演算を指定できない。

リストパーティションの分割

ALTER TABLE パーティション化されたテーブル
SPLIT PARTITION パーティション名
VALUES(値)
INTO(PARTITION パーティション名A TABLESPACE hoge,
     PARTITION パーティション名B TABLESPACE hage)
;

リストパーティションの統合(結合)

ALTER TABLE パーティション化されたテーブル
MERGE PARTITIONS パーティション名1,パーティション名2
INTO PARTITION パーティション名1
;

リストパーティションの値の変更

ALTER TABLE パーティション化されたテーブル
MODIFY PARTITION パーティション名
ADD VALUES(値)
;

または、「DROP VALUES(値)」。


パーティションに名前を付ける

個別のパーティションにテーブル名を付けることが出来る。

ALTER TABLE パーティション化されたテーブル
EXCHANGE PARTITION パーティション名称
WITH TABLE パーティションに付けるテーブル名
;

パーティションの変更

既存のパーティションを変更するには、「ALTER TABLE テーブル名 ADD/DROP PARTITION 〜」を使う。

テーブルスペースを追加する例
ALTER TABLE test ADD PARTITION TABLESPACE hoge;
パーティションを削除する例
ALTER TABLE test DROP PARTITION パーティション名;
ALTER TABLE test TRUNCATE PARTITION パーティション名;

インデックスの作成

パーティション化されたテーブル用のインデックスには、ローカルインデックスとグローバルインデックスがある。
ローカルは、パーティションに合わせてインデックスも分割される。
グローバルは1つのインデックスでパーティション全体を指す。
ビットマップインデックスはローカルでしか作れない)

CREATE INDEX 索引名 ON テーブル名(項目,…)
LOCAL又はGLOBAL
〔パーティション定義〕
;
CREATE INDEX ix_part_test
ON part_test(key)
LOCAL
;
CREATE INDEX ixg_part_test
ON part_test(num)
GLOBAL PARTITION BY RANGE(num)
(PARTITION p1 VALUES LESS THAN(50),
 PARTITION p2 VALUES LESS THAN(MAXVALUE)
)
;

ローカルは基となるテーブルと同じ形式のパーティションになる。[2007-12-13]
(例えばパーティション数が異なるインデックスを作ろうとするとORA-14024のエラーが発生する)

“グローバル・パーティション索引”は、テーブルとは無関係に別形式のパーティションにすることが出来る。
ただしグローバルはリストパーティションを作ることは出来ない。レンジとハッシュのみ可。[/2008-06-03]
(リストパーティションのインデックスを作ろうとするとORA-14151のエラーが発生する)
→OTNのパーティション表とパーティション索引

全く同じ項目を指定してグローバルインデックスとローカルインデックスの2種類を作ることは出来ない。[2008-06-28]
普通のインデックスでは同じ項目を指定するのは無意味だが…パーティションでもそうなのだろうか)


パーティション化テーブル作成時に(プライマリキーの)インデックスのパーティションも同時に定義するには、以下のように書く。[2007-12-13]

CREATE TABLE テーブル
(項目 属性,
 項目 属性,
 …,
 CONSTRAINT キー制約名 PRIMARY KEY (項目,…)
 USING INDEX GLOBAL PARTITION BY HASH(項目,…)
 (
  PARTITION 索引パーティション1,
  PARTITION 索引パーティション2
 )
)
PARTITION BY HASH(項目,…)
(
 PARTITION 表パーティション1,
 PARTITION 表パーティション2
)
;

パーティション(インデックス)定義の確認

パーティションテーブルの定義を確認する例。[2007-12-13]

パーティションの種類の確認

SQL> select TABLE_NAME,PARTITIONING_TYPE
  2  from dba_part_tables;

TABLE_NAME                     PARTITI
------------------------------ -------
TEST                           HASH

パーティション化項目の確認 [2009-01-06]

SQL> col column_name format a20
SQL> select name,column_name, column_position
  2  from dba_part_key_columns
  3  order by name,column_position;

NAME                           COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
TEST                           KEY1                               1
TEST                           KEY2                               2

分割されたパーティション名の確認

SQL> select TABLE_NAME,PARTITION_NAME
  2  from dba_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                           TEST_PART1
TEST                           TEST_PART2

パーティションインデックスの定義の確認 [2007-12-13]

SQL> select INDEX_NAME,TABLE_NAME,PARTITIONING_TYPE
  2  from dba_part_indexes;

INDEX_NAME                     TABLE_NAME                     PARTITI
------------------------------ ------------------------------ -------
IX_TEST2                       TEST2                          RANGE
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE
  2  from dba_ind_partitions;

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
IX_TEST2                       TEST2_IX_PART1
'5000'

IX_TEST2                       TEST2_IX_PART2
MAXVALUE

レンジパーティションだとHIGH_VALUEに「VALUES LESS THAN」の値が入っている。
ハッシュパーティションだとHIGH_VALUEはNULL。


データの操作

パーティション化されたテーブルに対しては、普通にSQL(DML)を実行することが出来る。[2009-10-06]
すなわち、INSERT文を書けば、自動的にどのパーティションに入れるかを計算して入れてくれる。
SELECT文やDELETE文を書けば、どのパーティションに入っているのかを探して出してくれる。

UPDATE文の場合、パーティションキーになっている項目を別の値に更新することは出来ない。
変更しようとすると以下のエラーが発生する。(パーティションが移動になる場合だけ?)

ORA-14402: updating partition key column would cause a partition change

DELETEしてINSERTするしか無さそう。
どうせOracle内部ではUPDATEと言ってもフラグを使ってDELETE-INSERTしているようなものなんだから、UPDATE一発でパーティションを移動してくれてもいいような気がするけど…。


パーティションを限定したDML

特定のパーティションのデータだけを対象にしたSQLを実行することが出来る。[2007-12-13]

SELECT *
FROM テーブル PARTITION(パーティション名);
SELECT *
FROM テーブル PARTITION(パーティション名)
WHERE 〜;

指定したパーティションだけしか選択されない。
これを使えば、テーブルの全データを対象にしたバッチを パーティション毎に並列化するのが容易に出来る。


Oracle11gでは、パーティションキーの値を使用した指定が出来るようになったらしい。[2010-02-04]

select *
from テーブル PARTITION FOR('値')
where 〜;

複数のテーブルを結合(join)する場合でも、それぞれのテーブル名の後ろにパーティション名を指定することが出来る。[2009-10-06]
また、パーティション指定の後ろに別名(エイリアス)を書くことも出来る。

select
	t1.*,
	t2.*
from
	テーブル1 partition(テーブル1パーティション1) t1,
	テーブル2 partition(テーブル2パーティション1) t2
where
	t1.keyA = 'aaa' and
	t1.keyA = t2.keyA
;

各テーブルでは、それぞれ指定されたパーティション内しか参照しない。
したがって、テーブル1とテーブル2のkeyAが同じハッシュを使っているとして、結合する為にはテーブル1もテーブル2も同じパーティションを指定しなければデータを取得できない。
別々のパーティションを指定したら、どちらかはSELECT結果が0件となるので、結果として何も取得されない。(エラーにはならない)


INSERT文でもパーティションを指定することが出来る。[2009-10-06]
ただし、インサートするデータがそのパーティションに適合しない場合(例えばハッシュパーティションでハッシュ値が別パーティションの値になる場合)はエラーとなる。

SQL> insert into TEST_HASH partition(HASH1) values('AAAA');
insert into TEST_HASH partition(HASH1) values('AAAA')
            *
行1でエラーが発生しました。:
ORA-14401: inserted partition key is outside specified partition

SELECT-INSERTでも、それぞれのテーブル指定の後ろにパーティションを指定できる。
その際のINSERT・SELECTの使用制限(使用方法)は、それぞれの制限どおり。


当然、UPDATE文でもパーティションを指定することが出来る。[2010-02-04]

update テーブル partition(パーティション名)
set 〜
where 〜
;

ただし、パーティションキーを変更するようなUPDATE文でパーティションを移動することになる場合はエラーになるらしい。
そういう場合は素直にDELETE〜INSERTかなぁ。


どのパーティションに格納されているかの確認方法

データがどのパーティションに格納されているかを調査する方法。[2009-01-09]
(特にハッシュパーティションでは実際にどのパーティションに格納されるのかは分からないので、調べる必要がある)

select
	o.object_name    table_name,
	o.subobject_name partition_name,
	t.*
from (
	select
		DBMS_ROWID.ROWID_OBJECT(rowid) object_id,
		t.*
	from テーブル t
	where 
    ) t,
	user_objects o
where o.object_id=t.object_id
;

ROWIDはOracleのテーブルの隠し項目で、レコードを特定するのに使えるもの(レコード毎に異なる値)。
ROWID_OBJECT関数は、ROWIDを元にそれがどのテーブル(パーティション)に格納されているかを返す(テーブル・パーティションのオブジェクトIDに変換する)関数のようだ。

上記のSQLを実行すると、レコード毎にパーティション名がpartition_name列に表示される。(パーティション化されていないテーブルの場合はNULL)


エクスポート

エクスポート(expコマンド)でパーティションテーブルが(同時にインデックスも)exportできる。[2007-12-13]

パーティション1つだけをエクスポートしたい時は、以下のように指定する。

> exp hishidama/hishidama@orcl10g tables=テーブル名:パーティション名 file=C:\expdat.dmp

ただしハッシュパーティション形式で作ったインデックスだと、何故かレンジパーティションとしてエクスポートされ(生成されたダンプファイル内のCREATE INDEX文がRANGEでVALUES LESS THAN(NULL)になっている)、インポート時点でORA-14019のエラーになる。
対処方法不明…バグとしか思えない。ちなみにバージョンは以下の通り。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQLメモへ戻る / Oracle目次へ戻る / 新機能へ戻る / 技術メモへ戻る
メールの送信先:ひしだま