テーブルのパーティッション分割

本日のネタもデータベースである。

最近のデータベースでは、テーブルを「パーティッションに区切る」ということが可能。
最近といっても、もうかなり前からかも。えーと、Oracleだと、10gくらいからか？
しかし、Enterprise Editionじゃないと使えないっぽい。ケチだよね、Oracleって。
DB2はV9から。なんだ、やっぱり割と最近じゃない？
SQL Serverは、2005。ということは、約10年前。10年ひと昔、というから割と古いのかも。
MySQLでは、5.1からか。PostgreSQLは一応は対応しているのか。

でもってパーティッションに分割すると、どんないいことがあるかというと。

　・パフォーマンスアップ
　・データの管理がしやすくなる

といったところか。

テーブルをパーティッションに区切ると効率的なのは、ログデータのように、履歴を保存しておくような場合。

パーティッションに分割するといっても、けっこういろいろなパターンがあるようなので、ここで一回整理しよう。

・レンジパーティッション
テーブルを時系列で分割するような分割方法。
　2000年のデータ
　2001年のデータ
　2002年のデータ
のように、時間や数値の範囲で分割していく手法。

・リストパーティッション
テーブルのある列をキーにして分割するような分割方法。
　男性のデータ
　女性のデータ
のように、データの取る値によって、保存先のパーティッションを決定する方法。

・ハッシュパーティッション
テーブルのある列から、ハッシュ値を計算して、その値から分割する方法。
　分割したいのだけど、どの値で分割してよいかわからない場合に、ハッシュパーティッションが有効。

・コンポジットパーティッション
なんかよくわからないが、リストとハッシュの組み合わせ、とかでやるものか。

まぁ、メジャーなのは、以下の３つか。

　・レンジパーティッション
　・リストパーティッション
　・ハッシュパーティッション

ひとつひとつみていこうか。

レンジパーティッション

これは、紹介した通り、値の範囲で分割しましょう、という考えやすい分割方法である。
パーティッションの定義は、CREATE TABLEでテーブルを作成する際にする。後で変更する場合は、ALTER TABLEを使う。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)

上記のようなテーブルがあったとする。普通に実行すれば、テーブルが作成されるが、パーティッション分割は行われない。

これをパーティッションに分割してみよう。

パーティッションに分割するためには、PARTITION句が必要になる。PARTITION句では、どの列で分割を行うのか、それに加え、どのような条件で分割を行うのかを指定する。
partition_testでは、日付のd列を使って、レンジパーティッションで分割するようにしたいと思う。レンジパーティッションでは、PARTITION BY RANGEを使用する。ここまでをSQLにしてみると、以下のようになる。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)
PARTITION BY RANGE (d)

まだ十分でないため、実行してもエラーになる。
PARTITION BYには、分割のための条件式が必要になる。分割したい数分だけパーティッションの名前と条件を列挙する。
partition_testでは、年を単位にしてパーティッションを分割しようと思う。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)
PARTITION BY RANGE (d) (
 PARTITION year1 VALUES LESS THAN(TO_DATE('2016-01-01', 'YYYY-MM-DD')),
 PARTITION year2 VALUES LESS THAN(TO_DATE('2017-01-01', 'YYYY-MM-DD'))
)

これで、ふたつのパーティッションに分割されることになる。必要なら、TABLESPACEを指定して、保存先のストレージを分けるということもできる。

日付で分割する場合、パーティッションのメンテナンスが必要になる。つまり2017年になったら、パーティッションを増やしてやる必要がある。保存しておく必要がなくなった古いデータをパーティッションごと削除する、ということも可能である。既存テーブルへのパーティッションの追加、削除は、ALTER TABLE命令で行う。

パーティッションの追加

ALTER TABLE partition_test
 ADD PARTITION year3 VALUES LESS THAN(TO_DATE('2018-01-01', 'YYYY-MM-DD'))

パーティッションの削除

ALTER TABLE partition_test
 DROP PARTITION year1

パーティッション内のレコードは全部なくなってしまうので注意。
この他、パーティッションのメンテナンスとしては、既存のパーティッションをさらに分割(SPLIT)したり、ふたつのパーティッションを統合(MERGE)したりすることができる。

パーティッションを作成したからといって、INSERTやUPDATEを行う際に、パーティッションを意識する必要はない。普通にINSERTすればよい。

INSERT INTO partition_test VALUES(1, '2015-01-01', 10);
INSERT INTO partition_test VALUES(2, '2015-12-31', 20);
INSERT INTO partition_test VALUES(3, '2016-01-01', 30);
INSERT INTO partition_test VALUES(4, '2016-12-31', 40);

2016-01-01より前のレコードは、パーティッション year1 に、2017-01-01より前のレコードは、パーティッション year2 に自動的に記録される。
しかしである。記録すべきパーティッションが見つからない場合は、エラーになるので注意したい。こういった場合、最大値までのパーティッションを作成しておくと、間違いがない。最大値は、MAXVALUEで指定可能。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)
PARTITION BY RANGE (d) (
 PARTITION year1 VALUES LESS THAN(TO_DATE('2016-01-01', 'YYYY-MM-DD')),
 PARTITION year2 VALUES LESS THAN(TO_DATE('2017-01-01', 'YYYY-MM-DD')),
 PARTITION yearx VALUES LESS THAN(MAXVALUE)
)

SELECTで問い合わせを行う際には、パーティションを意識した方が、パフォーマンス向上が期待できる(かも知れない)。
データの検索を2015年のデータに限って行いたいのであれば、パーティッションyear1のみからデータを検索すれば、高速。パーティッションを指定するには、以下のようにする。

SELECT * FROM partition_test PARTITION(year1)

だいたい、こんな感じだろうか。
では、次に、リストパーティッションについてみていこう。

リストパーティッション

レンジパーティッションは、値の範囲でパーティッション分割を行っている。リストパーティッションでは、キーとなる列の値をリストで定義する。
ユーザ管理が行われているテーブルを例にしてみる。以下のように、レコードデータにユーザIDが付けられている。多くのWebアプリケーションシステムでユーザ管理が行われていると思う。今回は、そういったシステムでユーザIDをキーにして、パーティッション分割を行ってみる想定である。
まずは、テーブルの定義を見てみよう。

CREATE TABLE partition_test2 (
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY(userId, no)
)

userId列が、ユーザIDを示す列である。通常、ユーザ間のデータ共有ってまず行われない。ユーザ間のコミュニケーションが目的のシステムであれば話は別であるが、ブログとか、ショッピングサイトなら、他ユーザの情報は見えないし、見せてはいけないものとなる。
ユーザごとに、パーティッション分割してしまえば、何かと便利なんじゃないか、という発想である。

こういった用途で、リストパーティッションを使うことができる。リストパーティッションは、PARTITION BY LISTで定義する。partition_test2では、userIdをキーに分割したいので、PARTITION BY LIST (userId)とする。レンジパーティッション同様に、分割するパーティッションを列挙していくが、分割の条件がLESS THANではなく、単にVALUESに値を列挙するだけとなる。

CREATE TABLE partition_test2 (
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY (userId, no)
)
PARTITION BY LIST (userId) (
 PARTITION list1 VALUES ('user1', 'user2'),
 PARTITION list2 VALUES ('user3'),
 PARTITION listx VALUES (DEFAULT)
)

じゃーん。こんな感じでパーティッション分割してみた。
ユーザuser1とuser2は同じパーティッションlist1に含まれることになる。user3は単独でパーティッションlist2に記録される。その他のユーザは、listxに入ることになる。

データの追加は通常のINSERTでOK。

INSERT INTO partition_test2 VALUES('user1', 1, '2016-04-01', 100);

パーティッションを指定することも可能ではあるが、あまりメリットはない。

INSERT INTO partition_test2 PARTITION(list1) VALUES('user1', 2, '2016-04-02', 101);

間違えたパーティッションにINSERTしようとするとエラーになる。

INSERT INTO partition_test2 PARTITION(list2) VALUES('user1', 2, '2016-04-02', 101);

レコードを検索する際に、パーティッション指定をすると、便利かも知れない。

SELECT * FROM partition_test2 PARTITION(list1);

パーティッション分割していないテーブルだとWHERE条件でユーザIDを指定しないといけない。

SELECT * FROM partition_test2 WHERE userId = 'user1';

上記の例では、user1とuser2が同じパーティッションに入るので、やはりWHERE条件は必要になるかも知れないが、ユーザひとりについてひとつのパーティッションを割り当てます、というルールにすれば、そのテーブルをそのユーザ専用にすることができる。うまいことキーを設定してやれば、めんどうな結合条件を省略できるのでは、という話である。

これ開発中のシステムでも使いたいな、でもEnterprise Editionじゃないとだめなのか。


ハッシュパーティッション

レンジ、リストとふたつのパーティッションをみてきた。どちらもパーティッションに分割するためのキーが必要であったが、キーに対してひとつの列しか与えることができない。PARTITION BY RANGE (a,b,c) ということはできないのである。PARTITION BY LISTでも同様。

複数の列からパーティッション分割の条件を設定したい場合は、ハッシュパーティッションを使用すればよい。

前の例では、ユーザIDでリストパーティッション分割を行った。大規模なシステムでは、ユーザーIDだけではなく、ドメインとユーザIDでユーザ管理できることが往々にしてある。その場合、以下のようなテーブルになるであろう。

CREATE TABLE partition_test3 (
 domain VARCHAR2(10) NOT NULL,
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY (domain, userId, no)
)

domainとuserIdでユニークになるようなデータ構造となる。パーティッション分割する際に、domainだけでは不十分。userIdも付けたい。しかし、複数列を許してくれない。ハッシュパーティッションにすれば、この問題を解決できる。

CREATE TABLE partition_test3 (
 domain VARCHAR2(10) NOT NULL,
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY (domain, userId, no)
)
PARTITION BY HASH (domain, userId) (
 PARTITION hash1,
 PARTITION hash2,
 PARTITION hash3
)

ハッシュパーティッションでは、複数の値からひとつの「ハッシュ値」を計算し、それを元にパーティッション分割する。この値のときは、このパーティッションに記録する、という条件を指定することはできない。データベースシステムの方で勝手に記録先が決定してしまう。
ハッシュ値の特性から、同じ値を与えれば、同じ記録先になる。例えば、キー'domain1','user1'のレコードがパーティッションhash1に記録されたとする。以降、パーティッション分割数を変更しない限り、キー'domain1','user1'の記録先は、常にパーティッションhash1に記録される。
どのパーティッションに記録されたかを調べるには、USER_OBJECTSテーブルの、SUBOBJECT_NAMEを参照するとわかる。
USER_OBJECTSテーブルのOBJECT_IDを調べるには、DBMS_ROWID.ROWID_OBJECT(rowid)を使用する。rowidは、レコードの疑似列。

よくわからないでしょうから、SELECT命令にします。

SELECT *
  FROM (
    SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) object_id,
      domain, userId
      FROM partition_test3
  )
  NATURAL JOIN USER_OBJECTS
  ;

今回はここまでとする。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

タグ：SQL