SAP SQL Anywhere における自己管理インデックスクラスタリング統計情報 : 過去のブログより

[Sawa_Ito]

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。

https://blogs.sap.com/2013/09/25/from-the-archives-self-managing-index-clustering-statistics/

 

この記事のオリジナルは、Glenn Paulley が sybase.com に 2008 年 9 月に掲載したものです。

 

SQL Anywhere における自己管理統計情報メンテナンス機能に対して私たちのチームが行った強化の一つにインデックスクラスタリング統計情報の自動メンテナンスがあります。

これはつまり、インデックスのクラスタリングの特性を分析し、物理アクセスのコストをそれに適応させることで、サーバーのクエリーオプティマイザーがより良いインデックスを選択できるようになったということです。

ここで重要なことは、正確性が増したことで、クラスター化されたインデックスにプラスになっただけでなく、全種類のインデックスにプラスになったことです。

実際にあったお客様のクエリーを例に、これらのクラスタリング統計情報をオプティマイザーがどう使用するのか説明します。

 

SELECT *

FROM TJ_TransactionJournal LEFT OUTER JOIN TJ_TransactionJournalDetail

WHERE trn_endtime > '2008-06-01' and tli_lit_fk = 1

 

このクエリーでは、2つのポイントがあります。

まず、2つのテーブル間の outer join 条件は、2つのテーブル間の PK-FK 関係によって暗示されます（SQL Anywhere の機能）。

2つ目に、OUTER JOIN は、tli_lit_fk カラム上の「NULL を許容しない述部」により、オプティマイザーが inner join にリライトします。

TJ_TransactionJournal テーブルの行数は、67,700 万行です。TJ_TransactionJournalDetail テーブルの行数は、12,200 万行越えです。

一見すると、 trn_endtime  カラム上のクラスター化されたインデックスを利用したストラテジーの方が、述部の選択性が 2.36% と見積もられるため、便利に見えます（実際は、1.85%）。

以下は、これらの数字の詳細 pane を表示したこのクエリのグラフィカルなプランです。



しかしながら、オプティマイザーは、クラスター化されたインデックススキャンではなく、TJ_TransactionJournal テーブルに対するシーケンシャルスキャンを選択していることに注意してください。なぜでしょうか？

TJ_TransactionJournal quantifier のより詳細 pane を以下に示します。



Advanced Details pane は、この quantifier のためにクエリーオプティマイザーが評価したそれぞれの物理アクセスパスをリスト表示します。

この例では、リストされた最初の物理アクセスパスは、シーケンシャルスキャンです。

2 つ目は、trn_endtime カラム上のインデックスを表しています。これは、クラスター化されたインデックスとして示されています。

このセクションの値は以下のとおりです。

Selectivity 2.36310%

Index name trn_endtime

Clustered index yes

Depth 3

Estimated leaf pages 11773

Sequential Transitions 28597

Random Transitions 6329390

Key Values 368170

「Key values」 は、このインデックス内のユニークなキーの数です – trn_endtime インデックス内に 368,000 のユニーク値があります（このインデックスがコンポジット、つまり、複数のカラムにわたるかどうかはここからはわかりません）。

「Sequential transitions」は、インデックス内の隣接したキーが隣接テーブルページにあるローを指している場合の key-value ペアの数です。サーバーは隣接インデックスエントリーが同じページのローを指す場合の統計情報はインクリメントしません。

隣接インデックスエントリーのローの同じページのレジデンス、または隣接ページは、クラスタリングの主な特性です。そのため、本当にクラスター化されたインデックスのためには、「Sequential transitions」の値は、非常に小さくー この統計情報は、各リーフページの最後のところでインデックスエントリーのためだけにインクリメントされます。

「Random transitions」の値は、隣接インデックスエントリーによって示されたローが１以上のテーブルによって分けられている場合、インデックスエントリーを示します。

正確には、値がどの程度「ランダム」にローにわたって分散するかは、三番目の統計情報「Random Distance」（現在グラフィカルなプランには表示されません）によります。

これは、隣接インデックスによって示されたロー間のテーブルページ数の累積合計です。これの最大値は、テーブル内のロー数にテーブルページの数を掛けたものです。

3 つの統計情報は全てサーバーによってメンテナンスされ、SYSPHYSIDX システムビュー内のカタログ内で利用可能です: カラム名は、seq_transitions、rand_transitions、rand_distance　です。

trn_endtime 上のインデックスは、オプティマイザーにとって経験のないものでした。

なぜならば、ランダムな遷移数 (6329390) が非常に大きく、クラスター化されたインデックスがヒドイ状態だったからです。

クラスタリングの欠如が原因で、インデックスのクラスター化があまりにもひどいため、オプティマイザーは、 TJ_TransactionJournal テーブルをシーケンシャルにスキャンする方が、ランダムにページにアクセスするよりもチープであると見積もっていました。

この単一のグラフィカルなプランからは、インデックスがなぜクラスター化されていないのか証明できませんが、最も簡単な方法は、テーブル内のローの物理レイアウトを再編してフォローアップするのではなく、 ALTER INDEX を使用してシンプルにインデックスを「クラスター化されている」と注釈をつけることです。

必要な再編を遂行する一つの方法として、REORGANIZE TABLE 文を発行するという方法があります。

 

 

 

===

SQL Anywhere に関してはまずはこちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。

 

その他 SAP SQL Anywhere ブログページ や、SAP SCN Wiki などもご参照ください。

 

SQL Anywhere に関して技術的な質問のある方はコミュニティに登録し、
「Ask a Question」機能をご利用ください。

Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
User Tagに「sql anywhere」「sql anywhere Japanese question」

を入力してください。

 

不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。

 

======================
ご購入に関するお問い合わせ

こちらよりお問い合わせください。