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_DATE
(TO_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一発でパーティションを移動してくれてもいいような気がするけど…。
特定のパーティションのデータだけを対象にした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