DB2 for i の改善されたEVIデータベース・インデックス機能を使用してSQL照会機能を強化する
IBM i 7.1 の機能強化された Encoded Vector Index 機能を使用して集計データのSQL照会を改善する
IBM i 7.1 が 2010 年の春に再登場したとき、多くの拡張機能が提供されましたが、その中に Encoded Vector Index (EVI) という、独特のリレーショナル・データベース・インデックス・テクノロジーへの機能強化がありました。30 年以上に渡り革新的な System i テクノロジーを提供してきた IBM の歴史に違わず、ロチェスターにある DB2 for i チームは、インデックス機能、照会最適化、データ・アクセスに新たな解釈を加えました。この記事では、機能強化された EVI の動作と、そのアプリケーションでの利用方法についてお話しします。
定義されたインデックス
では、インデックスとは何でしょうか。簡単に言えば、インデックスは、参照を容易にするためデータベース・エンジンで使用されているデータ構造です。特に、インデックスによって表の行を特定し、直接それにアクセスできます。ちょうど本の索引のように、データ構造には、通常はアルファベット順に並べられた「キー」のリストがあり、キーごとに、このキー値が特定のページに表示されることを示すロケーション番号があります。本の場合、すべてのページが正しく番号付けされていて、ページに対応する番号を使用して指定のページに直接移動できるメカニズムを備えていれば、インデックスにより、1) キーワードまたは値の検索 2) そのワードまたは値を含むページを見つけて移動、が効率的かつ効果的に行うことができます。翻って、それは時間と労力のムダを省きます。
リレーショナル・データベースのインデックスも同様の機能を果たします。インデックスには、基礎表からのキー値のリストが含まれています。キー値は、1 つまたは複数の列で構成できます。各キー値には一意の ID が関連付けられています。DB2 for i 内では、この ID は相対行番号 (RRN) と呼ばれています。つまり、表の各行には、特定かつ一意の RRN が割り当てられているということです。RRN はデータ・スペースになくてはならない部分であり、表で定義された列の一部とは見なされません。
データ・アクセス中にインデックスを利用するには、プローブおよび走査という手法でインデックスにアクセスします。プローブは、ちょうど本の索引で特定の単語を見つけて、そこに移動するような、選択基準を満たすキー値に直接配置することと考えることができます。インデックス走査は、インデックスの第 1 キーから始まり、順番にキー全体を読み取ることと考えることができます。もちろん、任意のキーから開始してそこから読み取ったり、逆の順番で読みとったりする、いわばキーの逆行も可能です。
インデックス・プローブの場合、データベース・エンジンは、最低の手順数で該当の行に行き着くことができます。つまり、キーと関連する RRN を見つけるようインデックスを調べ、インデックスからの RRN を使用して表を調べます。
インデックス走査の場合、該当の行に到達するには、データベースは多数の手順を踏まなければならない場合があります。おそらくデータベース・エンジンは、多数のキーを読み取り、テストしてから一致するキーを選択するのでしょう。いったんキーを選択したら、該当するキーの RRN を使用して表を調べます。もちろん、インデックス走査を利用するその他の理由として、暗黙配列を利用するためという点があります。索引キーを順番に読み取り、その順番で基礎表を調べると、行がキーと同じ順番になっているように見えます。
インデックス専用アクセス
インデックスには、行ごとにキーと RRN の両方があり、表の行にアクセスするには、少なくともインデックスを読み取り、表を読み取る必要があることがわかりましたので、照会で必要とされるデータ検索に伴う時間とエネルギーを最小限に抑える方法についてお話ししましょう。実際これは、 DB2 for i 照会オプティマイザの主な目標の 1 つでもあります。つまり、照会プロセスにおける入出力をできるだけなくす、または少なくすることです。
インデックス専用アクセス (IOA) は、SQL 照会で使用できる手法です。この手法を使用したデータベース・エンジンは、インデックスに組み込み保存されたデータを利用することで、表を読み取らなくても済みます。このデータは、キー自体で表され、Encoded Vector Index の場合は、EVI オブジェクトの各キーに関連付けられた追加データを意味します。
IOA は、キー欄で表されているデータを取得するロジックを使用します。キー中の列の相対位置は重要ではありません。重要なのは、照会で使用されているすべての列が、どこかでインデックスのキーとして表現されているということです。そうでない場合、クエリーは欠けているデータを取得するため表を読み取る必要があり、IOA のメリットがなくなってしまいます。
IOA を使用すると、照会オプティマイザは、インデックス・データ構造のみ読み取って要求を満たすことで、入出力の削減計画を作成することができます。キー別に多数の行にアクセスしている照会の場合、こうすることで効率性とパフォーマンスを著しく改善できます。次の例では、インデックス CUSTOMER_IX1 は、キー欄として REGION を指定しています。クエリーは、列 REGION、CUSTOMER_NO、および CUSTOMER_NAME を参照しています。表の行の特定にインデックスを使用できますが、IOA には使用できません。基礎顧客表にアクセスして CUSTOMER_NO 列と CUSTOMER_NAME 列を取得する必要があります。
CREATE INDEX CUSTOMER_IX1 ON CUSTOMER (REGION);
SELECT REGION, CUSTOMER_NO, CUSTOMER_NAME
FROM CUSTOMER
WHERE REGION = 'NORTH'
ORDER BY CUSTOMER_NO;
REGION、CUSTOMER_NO、CUSTOMER_NAME の各列をキーとして指定するインデックスの場合、データベース・エンジンは、インデックス・オブジェクトから必要なすべてのデータを取得しながら、基礎表を読み取るという余分な作業をせずに済みます。
CREATE INDEX CUSTOMER_IX2 ON CUSTOMER
(REGION, CUSTOMER_NO, CUSTOMER_NAME);
SELECT REGION, CUSTOMER_NO, CUSTOMER_NAME
FROM CUSTOMER
WHERE REGION = 'NORTH'
ORDER BY CUSTOMER_NO;
IOA 手法は、基数インデックスまたは EVI に対して使用することができます。EVI データ構造は、基礎表にさらに機能とメリットをもたらします。図 1 ：EVI の要素に示すように、EVI は基本的にベクトルとシンボル・テーブルという 2 つの基礎構造で構成されています。
ベクトルは要素の配列で、表の行ごとに 1 つの要素があり、各要素はそれが表す行と同じ順序位置にあります。ベクトルの各要素には、シンボル・テーブルからの適切な代理キーを表すコードが入っています。シンボル・テーブルには、基礎表で表された独自のキー値のリストが入っています。該当キーに一致する行数のカウントも入っています。この EVI シンボル・テーブル・コンテンツにより、非常に効果的な IOA 手法が可能になります。
2012 年度の 1 月に発注した数のカウントを要求する照会を考えてみてください。EVI がないと、データベース・エンジンは、表にある年月の基準に一致するすべての行にアクセスして、数えなければなりません。年月をキーとして定義している EVI があれば、カウントは即座に認識され、利用できます。
CREATE ENCODED VECTOR INDEX MY_EVI ON MY_TABLE
(YEAR, MONTH);
データベース・エンジンで必要なのは、EVI シンボル・テーブルのエントリーを 1 つ読み取り、解を得ることだけです。基本表から複数行を読み取る場合と、EVI を使用して解を見つける場合の時間と労力の違いは、通常はかなりのものです。
その他 EVI 専用アクセスについて、表で表された独自の年月のリストまたは独自の年月のカウントを要求する可能性があります。繰り返しますが、EVI シンボル・テーブルには解があり、利用できます。
当然ながら、挿入、アップデート、削除により、基礎データが表で変更されると、EVI は即座にアップデートされます。したがって、シンボル・テーブル情報は、常に最新の状態で正確です。
ビジネス上の問題
業務結果に対するダッシュボード、分析、報告は、ビジネス・アプリケーションではごくありふれたニーズです。ユーザーにタイムリーに情報を提供することは、成功には欠かせない要素です。企業の傾向として、より多くのユーザーが重要な情報にアクセスできるようになっています。逆にこれがシステムにストレスを与えています。速いだけでなく、効率的に処理するクエリーの機能は、タイムリーなデータをユーザーへ提供する際に成功するためのカギです。
図 2：集計データの要求例 に大規模なデータ・セットの集計を要求している簡単な要求の例を示します。
この例では、開発者は 2012 年度の四半期および月ごとの数量と収益の合計を見つけるよう求められています。データベースには 3 年分の受注データが入っており、各年で約 200 万行あり、合計 600 万行カウントになっています。環境によっては、これは大規模なデータ・セットですが、別の環境にとっては小規模です。にもかかわらず、これは現実的なビジネス上の問題を表す役目を果たし、我々に照会最適化を検討させるものです。ご覧のように、基礎データ・セットが大規模か小規模かにかかわらず、それに合わせてソリューションを拡大したり、縮小することができます。
図 3：集計データ結果を生み出す SQL 照会 は、この結果を生み出す照会の SQL ステートメントを表しています。
この照会が指定されている場合、オプティマイザは 2012 年のデータを含む行を特定して読み取り、年、四半期、月の組み合わせごとに、それらの行の数量と収益のデータを合計する計画を考え出す必要があります。 つまり、600 万行のうち約 200 万行のデータにアクセスして集計するということです。また、年間 200 万行を年 12 か月で割ると、グループごとに約 167,000 行存在します。結局、データベース・エンジンは比較的小規模な解集合を得るために、多くの手順を踏まなければならないのです。
この作業の例は、次の手順から成る照会計画で表されます。
- 表にある 600 万行を完全に走査します。
- 年が 2012 に等しい各行をテストし、選択します。
- 独自のハッシュ表を使用して、年、四半期、月ごとの数量と収益の数字を集計し、合計します。
- ハッシュ表の集計値をアンロードし、年、四半期、月ごとに結果をソートします。
- ソート結果を要求元に返します。
図 4：集計データ要求例の照会計画の図 は照会計画を図示しています。
この特定の照会のパフォーマンスは、ローカル選択述部 (例: WHERE YEAR = 2012) に一致する行のみ特定して、アクセスできるようにするため、図 5：EVI の追加による SQL 照会のサポート に示すようにインデックスを指定することで改善できる可能性があります。
この場合、年をキーとして EVI を作成すると、新しく効率性が高い照会計画が可能になるはずです。この場合、表の全走査はインデックス・プローブ、RRN リスト走査、表プローブで置き換えられます。
図 6：EVI を照会で使用した場合の結果 に示すように、EVI を使用すると入出力操作回数を 600 万行から 200 万行に減らすことができます。
しかし DB2 はそれでも 200 万行のデータを読み取り、処理しています。データを選択し、合計するのにこれより優れた方法はないのでしょうか。
EVI INCLUDE 節を導入する
IBM i 7.1 の場合、EVI サポートで新しい INCLUDE 節が提供されています。これにより、1 つまたは複数の集計結果を、独自のキーごとに EVI シンボルに保存できます。簡単に言えば、EVI キーごとに合計、カウント、平均を持ち歩くことができ、SQL Query Engine (SQE) は EVI 専用アクセスを使用して、表にアクセスしたり、詳細データの集計をしたりしなくてもデータを検索できます。この新しい DB2 for i 機能は、パフォーマンスとスケーラビリティーに対して非常に劇的でプラスの効果をもたらします。
EVI でサポートされる集計関数は次のとおりです。
- AVG
- COUNT
- COUNT_BIG
- SUM
- STDDEV
- STDDEV_SAMP
- VARIANCE
- VARIANCE_SAMP
図 7：EVI INCLUDE サポートを使用した照会パフォーマンスの改善 は、新しい EVI INCLUDE サポートをどのように活用して、前述の要約照会に対してより速いソリューションを実現できるかを示しています。
IOA 手法を利用できるかどうか確認するには、図 8：照会のすべての列がインデックス中で表現されていることの確認 に示すように、照会で使用されているすべての列がインデックス中で表現されていることを確認します。
キー欄が、照会のグループ化仕様と一致している、またはそれをカバーしていることも重要です。この例では、キー定義をするため、インデックスには最低でも年、四半期、月の列が必要です。さらに、年、四半期、月、日の複合キーも機能します。この組み合わせは、照会実行中に再グループ化できる詳細情報を表しているためです。
EVI の作成中、適切な列を使用してキーを構築し、キー値ごとに、集計値が計算され、キー別にシンボル・テーブルに保存されます。これは、集計せずにインデックスを作成するより、はるかに時間が掛かると思うでしょう。照会を実行している最新の SQE. Imagine を使用し、その結果をインデックス作成の入力として使用しているため、実際は、同程度またはそれ以上に速くなっています。実際は、こうしたことが DB2 エンジン内で起きています。
いったん作成された EVI は、基本表への挿入、アップデート、または削除操作のタイミングで即座にアップデートされます。インデックス中の影響を受けたキーそれぞれに関連付けられた集計値もアップデートされます。EVI をこのように利用することの真のメリットの 1 つとして、シンボル・テーブル・データは常に最新の状態で、正確であるという点があります。
EVI の使用例
新しい EVI を準備した状態で、照会オプティマイザには 3 種類のデータ・アクセスと、いくつかの集計オプションがあります。最初の選択肢は、表の全走査とハッシュ・グループ化です。2 つ目の選択肢は、EVI を使用して行を特定し、表プローブを使用してハッシュ・グループ化に沿って行にアクセスすることです。IBM i 7.1 で使用できる 3 番目の最もエキサイティングな選択肢は、図 9：EVI シンボル・テーブル集計値を使用する照会計画 に示すようにオプティマイザが EVI シンボル・テーブルを排他的に使用することです。
この技法により、コストが高い基本表への入出力操作を完全に回避し、詳細データの集計作業も回避します。
照会計画に示すように、新しい EVI INCLUDE サポートを使用することのメリットは、データの読み取り量が著しく減り、それに対応して集計作業が減るかまったくなくなるということです。その結果、照会の応答時間がはるかに速くなり、使用するシステム・リソースも抑えられ、この種の作業負荷を上手に増減できます。これを正しい角度で見直すには、6 行を読み取り、処理する場合と、 600 万行を読み取り処理する場合の差を想像してください。できれば、6 行と 6 億行の場合を考えてみてください。比較になりません。
EVI シンボル・テーブル専用アクセスを使用するメリットは明らかですが、他の過去のインデックス機能の機能拡張と組み合わせた場合、さらに有効性が高まる可能性があります。ビジネス上の問題に違った解釈を加えるため、オーダー・テーブルには年、四半期、月の列が入っていないと考えます。その代わり、日付欄 SHIPDATE が入っています。ただし、年、四半期、月ごとの情報の要求は同じです。言い換えれば、照会は、図 10：オリジナル SQL 照会の変更 に示すように、出荷日の派生物として年、四半期、月を指定しています。
特定の SQL 照会を実行すると、図 11：変更された SQL 照会を実行した場合の結果 に示すように表の全走査が行われます。YEAR(SHIPDATE) の導出値に基づくキーを持つインデックスがないためです。
表の走査中、データベース・エンジンは行を読み取り、関数 YEAR() を処理して、出荷日から年の値を導出し、この値をテストして、値が 2012 の場合に集計用の行を選択する必要があります。SHIPDATE 列に対するインデックスは、この照会には値を提供しません。この列は表に存在していないため、年に対するインデックスを作成することはできません。
この場合、新しい EVI INCLUDE 集計サポートは役立つでしょうか。簡単に言えば「イエス」です。
まず解決しなければならない問題は、キー欄を正しく記述することです。派生キーの使用に関するサポートは、図 12：派生キー・サポートを使用して、年、四半期、月をキー欄として表現する に示すように、該当する SQL 関数をインデックス定義の一部として使用することで、年、四半期、月の値をそれぞれ表現できる 6.1 以降から利用できます。
この特定のインデックスは、データベース・エンジンがローカル選択述部 YEAR(SHIPDATE) = 2012 に一致する行を特定するのに役立ちますが、年、四半期、月ごとに数量と収益を集計するメリットはありません。図 13：派生キー EVI を使用する照会計画 は、派生キー EVI を使用して、表の全走査をなくす照会計画を示しています。
一方、INCLUDE サポートを利用することで、EVI シンボル・テーブル専用アクセスは入出力だけでなく、データの集計に関連する処理もなくすことができます。図 14：派生キーと集計列の両方が組み込まれた新しい EVI は、派生キーと集計列の両方が組み込まれた EVI を示します。
2012 年度の 200 万行を読み取り、その 200 万行を四半期と月ごとに集計するのではなく、データベース・エンジンは EVI に依存して解を出しています。図 15：照会で EVI シンボル・テーブル専用アクセスを使用した場合の結果 に示すように、これにより派生インデックスが使用できない場合の表の全走査がなくなり、数百万行を集計するハッシュ・グループ化作業がなくなります。
ベスト・プラクティスと EVI の使用タイミング
EVI の作成は、照会オプティマイザとデータベース・エンジンを支援する 1 つの手法に過ぎません。EVI は比較的カーディナリティーが低い列に対して最適に作成されます。つまり、キー値ごとにいくつか独自の値か多数の重複行があるということです。データが一意または一意に近い列に対して EVI を作成するメリットはありませんし、お勧めしません。EVI の利用は高度な手法であるため、その代償とメリットを両方ともよく理解しておく必要があります。
列 ORDER_NUMBER と列 ORDER_DATE のあるオーダー・テーブルが指定されている場合、適切なインデックス機能戦略は ORDER_NUMBER に基数インデックス、ORDER_DATE に EVI を作成することです。オーダー番号は、カーディナリティーが高く一意で、オーダー日はカーディナリティーが低く一意でない (独自の日付値の数 = 365 x 表された年数) 場合があります。
集計関数が組み込まれた EVI は、EVI でサポートされている特定の集計関数を使用している照会に対してのみ作成されます。もう 1 つの要因として、集計照会が、グループごとの行数が比較的高い場合に、多数の行を参照しているかどうかということがあります。例えば、ある顧客の収益を合計するよう要求し、その顧客の持つ行が数十という照会では、ささいなデータでなければ、データを読み取り、処理する労力は比較的低いです。一方、数千万行を読み取り、そのデータを結果に集約するよう要求している照会では、労力ははるかに大きく、時間もさらに消費します。集計関数が組み込まれた EVI は、結果が計算され利用できる限り、多くのメリットをもたらすことができます。
インデックスの作成やインデックスのアップデートをしても、はるかに優れた照会パフォーマンスで、その苦労は何度でも帳消しにできるため、頻繁に実行される照会をサポートする EVI のみ作成することも理にかなっています。
インデックスの保守について言えば、正しく設計された EVI を維持するコストは非常に低いです。基数インデックスのコストより低くなっています。集計関数がシンボル・テーブルに組み込まれている場合にも、これが当てはまります。
EVI: SQL 照会ブースター
IBM i 7.1 にアップグレードすると多くのメリットがあります。その 1 つに DB2 for i の最新版を使用があります。7.1 の新しい EVI INCLUDE サポートは、SQL 経由でデータを保存し、アクセスする場合に、著しいメリットを提供できる独自機能の 1 つです。インデックス機能戦略や DB2 for i に関連した内容で困ったことがありましたら、お気軽に mcain@us.ibm.com まで連絡ください。詳細は information about DB2 for i indexing here および latest release of DB2 for i here をご覧ください。
