SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

お手製パーティションテーブルと分散データベース

システム設計 SQL中級・上級

　お手製パーティションテーブルも、分散データベースも、このようなやり方では実はやったことはない。

　理論上はお手製で可能ですが、パーティションテーブルと分散データベースが必要な規模では Enterprise Edition を導入した方が良いという判断になり、政治的に勝てることはない。

　しかし、ストアドプロシージャを使えば比較的簡単（？）にできる。大きなサーバに Enterprise Edition を入れるよりも、Standard Edition で台数を増やす方が安価にスケーラビリティは確保できると私は考えている。一部に Enterprise Edition を導入すれば、パラレルクエリーを利用できパフォーマンス面でもメリットがあるのではないか。

　実践するチャンスはないので、未検証だから間違いがあったら是非コメントください。揚げ足取りはご勘弁を。

　Oracleで考えたけれど、SQLServerでも似たように応用できるでしょう。

概要

　数GByteを超えるテーブルは単一では検索性能が落ちるので、パーティションテーブルを利用すると良いが、パーティションテーブルは Enterprise Edition の機能になりライセンス費が出ないプロジェクトでは導入できないこともある。

　そこで、Oracleなどの機能を使わずにパーティショニング機能を実装し、同時にサーバの分散機能も出来るようにする。

　売上テーブルを分割して保存しサーバも分散する。主な手順は以下の通り。

　　　　・分散するテーブルを管理するテーブルを作成する。
　　　　・売上テーブルに対する抽出用ファンクションを作成する。
　　　　・売上テーブルに対する更新プロシージャを作成する。（割愛）
　　　　・抽出用ファンクションを利用して抽出する。

テーブル

　売上ヘッダ
　　　　ID
　　　　売上NO
　　　　売上日
　　　　顧客ID
　　　　……

　売上明細
　　　　ID
　　　　売上ID
　　　　商品ID
　　　　単価
　　　　数量
　　　　……

　パーティショニング方針は「顧客ID」の範囲で行う。

　管理テーブル
　　　　ID
　　　　顧客ID_FROM
　　　　顧客ID_TO
　　　　分割テーブル名（リンクサーバ名付き、またはシノニムを保存）

売上テーブルに対する抽出用ファンクション

　Row_Mainなどの定義は予めしておくこと。

　　FUNCTION　fnc_売上
　　　　(
　　　　顧客ID_F　IN　NUMBER　　--　必須
　　　　,　顧客ID_T　IN　NUMBER　　--　必須
　　　　,　売上日_F IN DATE --　範囲も
　　　　,　売上日_T IN DATE --　範囲も
　　　　,　……
　　)
　　RETURN　fnc_売上_RSET　PIPELINED
　　IS

　　　　Row_Main　Cur_Main%ROWTYPE;

　　　　ReturnRow　fnc_売上_ROW;

　　BEGIN

　　-- 管理テーブルから、保存されているテーブルを取得する
　　
　　-- 取得されたテーブル名をループしながら、
　　-- パラメータが NULL じゃないとき WHERE句を生成。

/* こんなSQLになるように
　　SELECT *
　　FROM 売上ヘッダ_01 a --テーブル名は管理テーブルから
　　　　INNER JOIN 売上明細_01 b
　　　　　　ON a.ID = b.売上ID
　　-- 以下を動的生成
　　WHERE
　　　　……
　　UNION ALL
　　SELECT *
　　FROM 売上ヘッダ_02@売上用02 a --テーブル名はサーバ名ごと管理テーブルから取得
　　　　INNER JOIN 売上明細_02 b
　　　　　　ON a.ID = b.売上ID
　　-- 以下を動的生成
　　WHERE
　　　　……
*/

　　　　OPEN　Cur_Main;
　　　　Loop
　　　　　　FETCH　Cur_Main　INTO　Row_Main;
　　　　　　EXIT　WHEN　Cur_Main%NOTFOUND;

　　　　　　ReturnRow.ID　:=　Row_Main.ID;
　　　　　　……
　　　　　　PIPE　ROW(ReturnRow);　　--　データを出力する。

　　　　END　Loop;
　　　　CLOSE　Cur_Main;

　　　　RETURN;

　　END　fnc_売上;

売上テーブルに対する更新プロシージャ（割愛）

　　-- 管理テーブルにアクセスし、保存されているテーブルを取得し
　　-- 対象データを更新する。

抽出用ファンクションを利用して抽出

　　WITH 　
　　　売上 AS
　　　　　 (SELECT * FROM 
　　　　　　　　TABLE(fnc_売上(
　　　　　　　　　　:顧客ID_F
　　　　　　　　　　, :顧客ID_T
　　　　　　　　　　, NULL
　　　　　　　　　　……
　　　　　　　　　　))
　　　　　 )
　　SELECT 
　　　　……
　　 FROM
　　　　売上 
　　　　INNER JOIN 顧客マスタ
　　　　　　……
　　 WHERE
　　　　　　……

まとめ

　もちろん、運用中に新たにテーブルの分割を行うときは、管理テーブルにデータを入れるタイミングと、データを移行するタイミングには十分注意する必要がある。

　WITH の部分はパラメータを入れるだけで売上ヘッダ・明細にアクセスする記述を統一できる。テーブルを自在に分割出来るので1台のサーバでも、複数のサーバでも同じ形で利用できる。

　理論上はパフォーマンス・一貫性を維持しながらスケーラビリティをあげることが出来ます。

　Standard Edition ではパラレルクエリーを利用できないので、UNIONで結合しても順に実行される。しかし、今回の例では、売上テーブルを格納するサーバにStandard Edition、それ以外のデータを格納するテーブルに Enterprise Edition を採用し【fnc_売上】の動的SQLの部分でパラレルクエリーを利用すれば UNION で結合している部分が並列処理される。
　つまり、分けたサーバが並列で処理してくれるので、横断検索でも相当パフォーマンスが上がることも期待できる。

　少なくとも「JOINを禁止」するよりも、工数・パフォーマンス・スケーラビリティのいずれに於いても効率的だと私は思うが、政治的にはまず勝てないだろう……。

　参照記事
　http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917
　http://d.hatena.ne.jp/hiro_naka/20110809

　しかし、10億レコードで（更に大きくなる可能性があって）「RACはやだ」って言われたら私ならこうする。
　キツイ書き方ばかりするけれど、私は対案なしに批判するようなことは絶対にしない。


　未検証ですけどね。説明を端折りすぎかな……。
　余談ですが、テーブルファンクションは「パラメータ付きのビュー」と考えれば良い。考え方次第で非常に活用範囲は広いです。