オプティマイザーの基本的な動き (DB2/400)

インデックスなどのまったくないシンプルなテーブルを題材に、オプティマイザーが SQL 文に対してどういう反応をするか、を見てみました。

索引のアドバイス

特定の SQL 文に対するアドバイス

WHERE でカラム選択をするだけの簡単な SQL を実行してみた結果を Visual Explain で見てみました。

WHERE 句で指定したカラムについて、索引アドバイザー機能からインデックス作成の"アドバイス"がありました。こういうインデックスがあればもっとパフォーマンスはよくなるのにな、ということです。
Visual Explain の「アクション」-「アドバイザー」から見ることができます。

今回は、統計については特段"アドバイス"はありませんでした。 (あることもあります)

テーブル毎のアドバイス

iSeries ナビゲーターの左側のペインの「データベース」からデータベースとスキーマ(ライブラリー) を選択し、表示される右ペインで「テーブル」をダブルクリックするとだいたい↓のような画面が表示されると思います。

そのペインの中でテーブルを選択して右クリックすると以下のようなメニューが表示されるので、その中から以下のように「索引アドバイザー」を選択してみましょう。

選択した結果、↓のような表示がされます。
一段目のものが最初の "SELECT * FROM QIWS.QCUSTCDT WHERE STATE = 'TX'" での「アドバイザー」で表示されていたものになります。

↑の二段目のものは今回の一連の流れとは関係ありません。
たとえば、"SELECT * FROM QIWS.QCUSTCDT ORDER BY STATE" や "SELECT COUNT(*) FROM QIWS.QCUSTCDT GROUP BY STATE"などを実行した場合にこのような勧告がされます。実験して確認してみて下さい。「勧告の理由」欄に"順序付け/グループ化"と出ていると思います。

アドバイスの追加

では、先ほどの WHERE 句のある同じ SQL 文の最後に ORDER BY LSTNAM を追加してみましょう。

すると、こんどは↓のような"アドバイス"が索引アドバイザーからありました。

"SELECT * FROM QIWS.QCUSTCDT WHERE STATE = 'TX' ORDER BY LSTNAM" という SQL を実行すると、上記の"アドバイス"がされると同時に以下のように「索引アドバイザー」の画面にも表示されるようになります。Visual Explain の「アドバイザー」からも同じものが見られます。

基本的に同じ情報が SYSIXADV というテーブルを検索することによって得られますが、↑の結果と必ずしも結果がまったく一致するわけではありません……少なくとも V5R4 時点の仕様では最新の状況が得られるわけではないようです。

一時索引

V5R4 からはSQE でも「一時索引」が作成/使用されるようになりました。

一時索引の作成

「一時索引」は基本的に、アドバイスされている索引の中から、何回も同じ SQL が使用されるので、その索引の作成時間とそれによって短縮される時間を勘案すると、一時的に索引の作成というコストを払ってもそれ以降のトクになることが確認できたものを作成したもので、SQE プラン・キャッシュに格納されます。
プラン・キャッシュに存在する、ということは IPL するとクリアされてしまう、ということを意味します。だから「一時」なわけです。

オプティマイザーの判断によって、自動的に作成/維持/廃棄されます。
たとえば、恒常的なインデックスができたような時には廃棄されることがあります。

一時索引の表示

テーブルについている索引は、「索引アドバイザー」のところでも見た「テーブル」表示のペインで、そのテーブルを選択しての右クリックメニューで「索引表示」を実行することによってすべて表示させることができます。
「一時索引」もその例外にはなりません。

SQE で使用される「一時索引」は、CQE で使用される "Temporary Index" とは異なるもので、"Maintainde Temporary Index (MTI)" と呼ばれます。

CQE の一時索引はジョブ構造の一部である、言ってみればその場限りのものです。ジョブをまたがっては使われません。
SQE の「一時索引」はプラン・キャッシュといういわば公共の場に置かれるので、同時に稼動しているジョブからも、プラン・キャッシュ内に存在している限りは後続のジョブにも使ってもらうことができます。

もちろんデータの変化によってインデックスも変化します。それが "Maintained" の意味ですね。"Temporary" というのは、(繰り返しになりますが) 永続的なものではなく、プラン・キャッシュの内容が IPL などでクリアされてしまえば、それに伴ってなくなるものである、ということを意味しています。

ソート済みリスト

ORDER BY の場合はよく「ソート済みリスト」が使用されます。

WHERE 句などのない、ORDER BY のみのシンプルな SQL を実行して見てみましょう。
「一時ソート済みリスト」が使用されているのが確認できますね。

特に"アドバイス"はありませんでした。

ハッシュ・テーブル

GROUP BY の場合はよく「ハッシュ・テーブル」が使用されます。

↓は、グループ毎のカウントを集計する SQL についての Visual Explain 表示結果です。

GROUP BY に使用されているカラムについて、索引アドバイザーから"アドバイス"がありました。
グルーピングにもやはりインデックスは有効なようです。

用語集

選択性 (Selectivity): 選択した行が全体の何% か、たとえば 100万件のデータで選択性が 1% だったら 1万件ということ。
選択性が低い = ほとんどの行が選択結果となってしまう場合はテーブル・スキャンが有効であるし、高い = ごく少ない件数しか選択されない場合はインデックスの利用が有効であると考えられる。

カーディナリティ: 低い = カラムの値の種類が全体に比較して少ない/高い = 種類が多いことを指す。グルーピングなどを行う時にハッシュ・テーブルが使え、メモリーに収まるくらい小さくできると具合がいい。高すぎるカーディナリティはハッシュ値の衝突が起きる可能性がある。そういう場合はリンク・リストが使用されるのだが、リンクに沿ってのアクセスでない場合はパフォーマンスに問題が生じる可能性がある。

[Top Pageに戻る]

Ads by TOK2