SQLer 生島勘富 のブログ

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

コメントを頂いたので

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

数値を文字型で持つべきではない

　コメントをいただいたので。

　前の記事はこちら。
　　　　http://d.hatena.ne.jp/Sikushima/20110809/1312871002

　元ネタはこちら。
　　　　http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917
　　　　http://d.hatena.ne.jp/iad_otomamay/20100906/1283786846

　システムはそれぞれ要件が全く違う。だから、何事も一概には言えない。
　しかし、はっきり言えるのは数値を文字で置き換えて保存するのは、普遍的な超バッドノウハウでしょう。これはどう考えてもあり得ん。

　インサートされた時点の数値エリアの入力率が50％でそれが最終的に90％になる伸長率が高いデータだったとしても、

　　　　PCTFREE ＝ 100％ × 数値エリア割合 × 50％

　としておいたのと、数値エリアを文字で持った場合と比べると、PCTFREEを大きく取った方がデータ容量は小さくなる。なぜなら、最大桁数まで入力したとしても、数値はCHAR型の約半分のサイズになるから。
　最終的には行移行はほぼ起きず、データブロック数を少なくできるため、数値を文字型で持つことの意味は全くありません。

　また、仮に PCTFREE ＝ 0％にしていて（これは最後の１レコードが行移行してしまうので止めた方がいいが）CHAR型のエリアにNULLを入れられて、将来、UPDATEされたら行移行が起きてしまいます。

　ですから、
　　　１．固定長に拘らず可変長で設計し PCTFREE を設定する。
　　　２．適宜、Analyzeを実行して行連鎖を確認する。
　　　３．行連鎖しているテーブルがあれば、PCTFREEを数％ずつ大きくする。

　運用前に完璧な PCTFREE を設定するなんてのは不可能ですが、PCTFREE の変更はいつでも可能で、既存のデータは変更できないけれど次のブロックからは変更されます。

　行移行を修正した後に、PCTFREE の変更を行えば問題は徐々に収束していきます。

　これぐらいは単純な管理用アプリを書いて、その使い方を運用マニュアルに書けばすむ話で、トータルすれば文字を数値に変換する工数よりもはるかに小さいはずですし、システム全体のパフォーマンスもはるかに良くなります。

　シビアな設計だから数値を固定長の文字列で持つというのは間違いです。

スケールアウトについて

　スケールアウトについてはいろんなケースが考えられる。まず一つは Oracle RACのようなツールを使うこと。個人的にはこれはお勧めですが、ツールについてはそれぞれのマニュアルを読んで貰えたら。

　ツールを使わない場合は、お手製の負荷分散になりますが、それにはいろんな考え方、パターンがあります。

　そこで問題になるのが JOIN ですが……。JOINって問題なの？

　繰り返すけれど、負荷分散についてはいろんなケースがあるのですが、スケールアウトするまでは動いているわけよね。もちろん、マスターも際限なくに大きくなるということはあるけれど、マスターのレコード数が影響する規模なら、そもそもスケールアウトするまで動いてない。

　スケールアウトしなければならない状態になったとしても、分けねばならないのは基本的にトランザクションでしょ。

　分けねばならないトランザクションテーブルを特定のセグメント毎、あるいは、期間毎に分けることになると思うが、現状のハードウェア環境であればハードディスクが足りないということはまずないので、分けるテーブル以外のマスターなどのテーブルはコピーして両方に置いておき、更新は常に複数のサーバを同時更新させる。

　スケールアウトが必要になるほど大きくなるテーブル以外は更新は少ないのだから、コピーしてマルチフェーズコミットしてもシステム全体にはほとんど影響しません。
　これはフレームワークで吸収してもいい（SQLは同じだから簡単にできる）。

　「スケールアウトが〜」って気にする人達は、データを複数の場所に持つ「非正規化」は簡単に容認するが、非正規化は後から解析するのはもの凄く大変で不整合も起こりやすい。
　しかし、全く同じ構造のテーブルがあって、同じデータで同時更新することだけを保証すればよいのであれば簡単なわけ。なんでコピーはイヤなのか？

　全く影響しないとは言えないけど、マスターなどのデータ量がパフォーマンスに影響すると思うなら、もう一度、インデックス周りを見直し修正したり、勉強し直した方がいい。

　スケールアウトするときに、マスターサーバ、トランザクションサーバって分けるとしたらセンスないな〜と思ってしまう。まあ、ケースバイケースですけど、マスターサーバ、トランザクションサーバって分ける必要があるシステムであれば、RDBMS は使わないで NoSQL を使うべきですね。

　つまり、お手製でスケールアウトしても、JOIN を書いているプログラムは影響しないようにできるし、基本、そういう風にスケールアウトするべし。

　もちろん、分けたテーブルを串刺しして集計するのは、マスターをコピーしてようがいまいが、分割されたら何らかの変更が必要になります。
　それがイヤなら、Oracle RAC などを使えばいいんじゃないかな。