ストアドプロシージャにするとワークテーブルが増える？
大阪でセミナーが終わりました。わたしもイロイロと勉強になりました。
前回は1日セミナーだったので、ストアドプロシージャの話ができなかったのですが、今回はできました。ストアドプロシージャはバッチ処理でしか使わないとか、参照系のストアドプロシージャを作る時には、ストアドプロシージャの数だけワークテーブルを作って、セッションIDや端末名などをキーに入れてストアドプロシージャ内でデータを作成、クライアントプログラム（APサーバ、クライアント）でワークテーブルから抽出する、というような処理をするのが一般的という話が出ました。
もちろん、パーマネントテーブルなどを使っているのでしょうけれど、もし使ってなかったら、REDOログもたくさん出力されて大変だなと。アーカイブログモードや、ホットスタンバイなどの構成だったりすると、SELECTしたデータがすべてログに出力されるんだから、システム全体に大変な負荷が掛かりますよね（冗談抜きでこういう構成を見ることが多い）。
セミナーでもお話しましたし、ここのコメントでも何度か書いていますが、わたしはワークテーブルはほとんど使いません。その具体的な方法は、CodeZineの方に瀬戸さんに書いていただいたので、ぜひごらんください。
また、わたしにとってはストアドプロシージャの方が管理も簡単です。
というのも、逆に考えると、ストアドプロシージャにしないなら言語側でSQL文を生成し実行することになる。DAOを作って（わたしはAccessの方と勘違いしてしまったりしますが……）、ある程度、SQLを記述する場所を限定しているとしても、別の言語の中にString変数としてSQL文が入り込んでいるわけです（これを弊社では言語のスパゲッティと呼んでいる）。
よほど小さいシステムでもない限り、テーブル単位にDAOを作ってそれだけで終わるということはない。そうならばJOINしたSQLを多少は使わないとパフォーマンスも工数も足りないでしょう。ということは、テーブルやビューがString変数の中に分散してしまっています。
CRUD図などあてになったためしがないし（苦笑）、String変数の中、情報を解析するのは本当に大変な作業だと思うのですが、Grep取ったら大丈夫とか言うのかな……。
一方でストアドプロシージャはストアーするときにコンパイルされ、参照しているオブジェクトがすべてDB内の管理領域（ライブラリ）に保存されます。ですから、基本的にテーブルやビューなどの変更による影響は一瞬で確実に把握できます。これは、ライブラリに対するSQLを書いても見れますし、RDBMSに付属のツールでもできますが、SI Object Browserを利用するのがお勧めです。
SI Object Browserをインストールして、オブジェクトを複数選択して右クリック「依存関係」「マトリックス表」などを表示してみれば意味が理解できるでしょう。（エクセルに出力もできますよ）
もっとも、ストアドプロシージャの中で動的SQLを書いていたらダメなんですけどね。基本、弊社では動的SQLは、どうしてもパフォーマンスが欲しい複雑な処理にしか使いません。動的SQLを使っている機能への影響度はすぐわかりますし……。
バージョンの管理方法は普通、ストアドプロシージャごとにテキストファイルに保存して、ヴァージョン管理ツール（VSSとか、CVSとか、Subversionなど）を使えば問題になることはないでしょう。
というわけで、ストアドプロシージャの方が管理工数も下がります。
もちろんストアドプロシージャも万能ではありません。パフォーマンスが悪くなる具体例なども、セミナーではお話しましたけど、時間が掛かるのでここでは書かない。
とにかく、ストアドプロシージャにするとワークテーブルが必要と考えている人は、だまされたと思ってCodeZineの記事をご覧いただいて、ぜひ試して欲しいです。
セミナーは、東京は人数が集まらず開催できませんでした。残念。大阪は弊社内で2～4名で随時（土日）開催可能です。（7月25日、26日は開催予定。まだ空きがあります）。
わたしはプレゼンの資料どおりにしゃべれたことがない。そのうえ猛烈な飽き性なので、毎回同じ内容で話するのは無理です。ですから、質問が多い方がありがたい。例えば今回は準備していたわけではないのですが、SQLでn-Gramを実現する、つまり、LIKE '%検索文字%' とするとインデックスが使えないけれど、インデックスを使ってあいまい検索を高速に行う方法などの話もさせてもらいました。メンバーのスキルを見て、その場で話す内容や練習問題を考えてますので、設計から実装まで、たっぷり質問を持ってきてもらえれば。
ご興味のある方はこちらまで info@g1sys.co.jp
コメント
saki1208
生島さん、こんばんわ。
saki1208です。
ワークテーブルですか、ウチでも殆どそうですねぇ。
Oracle7あたりからの流れのまま、ずっとワークテーブルを使ってます。
しかも、パーマネントテーブル。テンポラリテーブルにすればいいのにといつも
提案するのですが、複数のトランザクションをまたいだバッチ等で異常終了時の
データを残すことが難しいと考えているらしく、常に却下されます。
# 結局、必要なときにワークのデータをコミットするように変更しないといけな
# いのに…
# 例外処理時にテーブル指定して、CSVとかで吐き出せばいいだけなんですけど。
# LOB使ってると面倒ですが。
# 締処理なんかのバッチでは本来重要ではない部分のREDOが大量に出力されま
# すしね。w
それにしても、SI Object Browser使ってる人多いですね。ウチでもほぼすべて
コレです。
でも、開発者目線で見るとエディタは貧弱(メモ帳レベル)だし、バージョン管理
システムとの連携も出来ないし、デバッグは使い難いし、あんまりいいとこない。
あくまでDBA用ツールだと思ってます。個人的に…
私自身はPL/SQL Developerというツールを使用しています。機能的にSI OBに
負けてるのは、IMP/EXPを使用したインポート/エクスポートくらいじゃないか
と…
標準では出来ませんが、アドインでバージョン管理システムと連携したり、プ
ロジェクトとして固まりで管理したり、その他、かなり開発者よりの機能満載
ですよ。
# 注:別に社員ではありません。w
saki1208
連投すみませぬ。
今は別トランザクションで管理できるし、プログラム変更しなくてもワークの
データは残せますね。
宝春
はじめまして、宝春と申します。
本題とはそれますが、私もPL/SQL Developerを使用しております。
もともとはSIOBを使ってましたが、PL/SQL Developerを使い始めてからは、
すっかりSIOBを使うことがなくなりました。
#別に社員では(略
まぁ、うちでは「なぜか」ストアド(PL/SQL言語)を使ってはいけないという
理解しがたいシバリがあるので機能を存分に活かすことができないのが
玉に瑕です。
バージョン管理連携は、Subversionなんかもできるんですかね。
ClearCaseとの連携は見たことがあったような・・・。
あとで、調べてみようっと。
さて、うちでもワークテーブルばりばりです。
もっとも、私どものDBA様はコールドバックアップからの復元しかできない
ようで、ノーアーカイブログでの運用をしておりますので あまり気にして
おりません。
orz
#生島様
#コメント欄汚してごめんなさい。
saki1208さん、どうも。
ワークを使うかどうかも割と上流の判断ですよね。
最上流とは言わないけど。
結局、自分の担当範囲ぐらいはそれなりに勉強しておかないとプロジェクトを明後日の方向へ連れて行っちゃいますね。
SI Object Browserは最新バージョンではインテリセンスも効いたような気がします。
バージョン管理はツールとは連動してないので、自分でやるしかないけれど……。
SQLServer + VS + VSS なら連動できますけどね。
宝春さん、どうも。
やっぱりワークテーブルを使うんですね。
ワークテーブルが増えるという批判が多いので、イラっとすることが多いです。
コールドバックアップで済むのであればそれでよいのですけれど、Oracle使うほどのシステムじゃないかな、PostgreSQLの方がメリット大きいかもね（笑）
saki1208
おはようございます。
saki1208です。
PL/SQL Developerはマジでおすすめです。
お試し版もあるので、試してみていただくとすぐに良さがわかるかも...
# 別に社員では(略
DBに限らずですが、ツールを利用する場合には便利な反面、ツールがないと作業
出来ない技術者が育ってしまうのが怖いですよね。
私自身は周りの人にいつも言ってます。
「ツールが裏でやってくれてることの意味、内容ぐらいはちゃんと理解しろ」と。
すべてを覚えておく必要はないですけど、何が起きるか位は理解していないと恐
ろしくて使えたもんじゃないし、いつか事故が起きます。
ワークテーブルが増えるのはストアドのせいではないですよねぇ。DBによる差異
があるのでアレですが、REFCURSOR使えばワークいらないし...
動的SQLについては激しく同意します。.Net以降のプログラムでは殆どSQLを書か
なくなってきましたが(個別にSQLを書かなければパフォーマンスの要件がクリア
できないほどクリティカルなところがない?)、作ったそのときはいいんですけど
仕様変更などでどこで使われてるか調査しようとすると極端に難しくなりますよね。
とてもじゃないけど、GREPなんかじゃ追いつけない。
saki1208さん、どうも。
実は、Oracleは9i以来ほとんど弊社では使ってない。
下請けで入るときは「バカだな～」と思いながらもあまり自分色を出せないので（ウソつけと言われそうですが）私としては相当抑えて3割ぐらいしか出してない。それでも激しすぎると言われるのですから、本気でぶつかったら死人が出るな～。
そんな訳でツールなどは他所から提供されたものを使って来たので、PL/SQL Developerは試してなかった。
次の案件で使わせていただきます。
デカいシステムでCRUDなんて信じたらえらい目に合うし、Grepでは追いつかないし、本当に怖いのですけど、それでもO/Rマッパーとかで隠蔽しちゃいたいのでしょうね。
もうRDBMSを使わなかったらいいのにね。
kim
生島さん。
いつも楽しく拝見しています。
私は「ＶＢ６＋Ｏｒａｃｌｅ」の開発経験ばかりです。
（ＳＱＬＳｅｒｖｅｒは経験なしです）
更新系はストアドで統一しています。
しかし参照系は下記の２つに実装方式が分かれます。
（１）ＶＢ側でのSQL文字列の生成方式
（２）ストアド側でのREFCURSOR使用方式
例えばコールセンター用顧客検索画面（顧客数：数十万件）では、
・住所と名前で検索
・電話番号と名前で検索
・携帯電話番号のみで検索
などSQL文のWHERE句の組み合わせが複数あり得るとき、
上記（１）のＶＢ側でのSQL文字列生成方式で実装します。
これを上記（２）のストアドREFCURSOR方式で実装しようとすると、
WHERE句の組み合わせの数だけIF文とSQL文が必要になるわけです。
保守性を考慮した結果、検索条件の組み合わせが複数ある場合、
ＶＢ側でゴリゴリSQL文字列を生成する方式で実装しています。
本音は参照系も更新系も全てストアドでやりたいんですけどね。
kimさん、どうも。
> ・住所と名前で検索
> ・電話番号と名前で検索
> ・携帯電話番号のみで検索
名前で検索もありますよね。
100万件とすると、1M件ですよね。
住所が100Byte、名前20Byte、電話番号12Byte × 3とすると。
1レコードで200Byteないわけです。
ということは200MByteぐらいのデータ量ですよね。
これなら、セミナーでお話した簡易のN-Gramなどで楽勝です。
たぶん、2人日もあればあいまい検索（LIKE %%)と同じ結果を、ストアドプロシージャを使った固定のSQLでインデックスを使った高速検索できます。
まぁ、宣伝ですけどセミナーではそんなお話もさせてもらいます。
更に宣伝ですけれど、N-Gram + で形態素解析を加え活用形などの変換を加味したものをSQLで実現しているのがこちらです。
http://www.g1sys.co.jp/sakutto/
グリッドもよくできてますけど、こちらも割りとイケてるのですけどね……。
kim
生島さん。
ご返信ありがとうございます。kimです。
>これなら、セミナーでお話した簡易のN-Gramなどで楽勝です。
>たぶん、2人日もあればあいまい検索（LIKE %%)と同じ結果を、ストアドプロシ
>ージャを使った固定のSQLでインデックスを使った高速検索できます。
ぜひ、実装方式を知りたいです！
大阪までセミナーには、諸事情がありまして
行けなくて残念です。。。
現在、私の方でも探しているのですが、
方式が説明されている書籍やサイトがございましたら、
教えて頂けませんか？
kimさん、どうも。
それほど難しくはないので、他にもやっている人はいると思うけれど、一応、オリジナルなのでサイトとかはないですね。
単純にN-GramをSQLでやっているだけですから、N-Gramについて調べてSQLでやる方法を考えてみてください。
つづきはメールしました。
kim
生島さん。
メールありがとうございました！（しかも早朝に！）
かなりいいヒントを頂いた感があります。
（１）まずは自分で考える。
（２）私のギブアップ時点で稟議書ＵＰ。
の手順で行こうと思います。
コールセンターにかかってくる電話は、とにかくひどいです。
「手元に請求書がございましたら、お客様番号を教えてください」
というマニュアルに記載されている１番目の質問には、
まずお客様は答えてくれません。
お客様の電話でよくあるのが、
・大田区の山下だけど、今月請求額おかしいじゃねえか！バカヤロー！
・佐藤と申します。携帯番号は個人情報なので教えたくないです。
・ジーワンマンション２０１号室の山田太郎です。
郵便番号わかりません。固定電話ありません。
携帯電話の番号は。。。えー忘れました。
こんな感じで、コールセンター受付業務も大変です。
お客様も大体クレームありきで電話かけてきてますから、イライラモードです。
①とにかくわかる情報であいまい検索して、件数を絞り込む。
②上記①で絞り込んだ情報から「おそらくこの人」を探し出し、確認する。
③お客様と合意できたら、そのお客様の全情報表示画面へＧＯ！
そうなると、顧客検索フォームで検索条件を入力するとき
グーグル式検索の方がやりやすいのかなと思った次第です。
まずは自分で試行錯誤してみます。
ありがとうございました。
kimさん、どうも。
もちろん、文字を切るところではループは必要です。
Oracleの場合はテーブルファンクションが結構作りにくく面倒ですけれど、慣れたら問題ないでしょう。
> ・大田区の山下だけど、今月請求額おかしいじゃねえか！バカヤロー！
> ・佐藤と申します。携帯番号は個人情報なので教えたくないです。
> ・ジーワンマンション２０１号室の山田太郎です。
> 郵便番号わかりません。固定電話ありません。
> 携帯電話の番号は。。。えー忘れました。
そんな感じでしょうね。私はコールセンターの仕組みはやったことがないけれど、Google式がいいんじゃないかと思いました。更に、Google式に作ってあげるとパラメータが1つで済みますから、他のプログラムで再利用するときにもメリットがあったりします。
VBでゴリゴリ書いているとVBでないと再利用しにくいですからね。
1秒ルールとか言われそうですから、インデックスが効くかどうかも非常に重要だと思います。
私がコールセンターのオペだったら1秒でも「遅い！」って文句言うタイプなので、そこはしっかりとチューニングします。
がんばってみてください。