Oracleで値の偏りが強い列を索引項目とする場合
Oracleで値が偏る項目を索引付け、検索する場合には、統計情報の採り方に着目すること。
経緯
Oracle DBMSを対象とした、とあるテーブルメンテ（古いレコードを消すだけ）機能で、母数1000万件（うち削除対象が1000件）程度の大量データでテストすると性能が出ないとのことで「鋼のオラクルマスター」の称号を持つ私が調べてみた。
作業にあたってDBA権限をもつユーザでログイン（ちなみに私は本来この環境でDBA権限など持つ立場ではない*1が、幸いデフォルトのDBA権限ユーザとパスワードが割れた）。
アクセスパスを調べると、削除対象を絞り込むSELECT文で索引が上手く適用されない。疑わしきは統計情報。DBA_INDEXESを調べるとLAST_ANALYZED列が空白なので、統計情報を採っていないのだと判明。
そこで表と索引の統計情報を採ってみたが、結果、問題のSELECT文に関しては変わらず。
次にデータの分布を疑うと、テスト担当者は、検索に用いる索引項目の値を、処理対象1種類（件数1000）と非処理対象（件数1000万）の合わせて2種類にのみ分けてテストデータを用意していた。この索引項目値の強い偏りが、最適な実行計画を導出できない原因であった。
値の偏る項目への索引付けについて
- 表の全件数
- 項目の最大値・最小値
- 項目の一意な値の数 (ALL_TAB_COLUMNS等のDISTINCT_KEYS)
ただし、項目の値は一様に分布するものと仮定される。
そのために、値の分布に偏りが顕著な項目に索引付けした場合には、実状に即した最適なアクセスパスが現れないことがある。
こういった項目に索引付けする場合は、列統計情報を取得し、ヒストグラムを生成する必要がある。
具体的には、DBMS_STATS.GATHER_TABLE_STATSにmethod_optパラメータを指定する。
Oracle9i PL/SQL パッケージ・プロシージャおよびタイプ・リファレンス(PDF)より
method_opt 次を受け入れます。
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_
clause] [,column|attribute [size_clause]...]
size_clause は次のように定義されます。
size_clause := SIZE {integer | REPEAT | AUTO |
SKEWONLY}
integer −ヒストグラム・バケット数。 1 〜 254 の範囲です。
REPEAT −すでにヒストグラムがある列に関してのみ、ヒストグ
ラムを収集します。
AUTO −列のデータ配分とワークロードに基づいて、ヒストグラ
ムを収集する列が判断されます。
SKEWONLY −列のデータ配分に基づいて、ヒストグラムを収集す
る列が判断されます。
このパラメータのデフォルト値は'FOR ALL COLUMNS SIZE 1'
つまり内部的にはヒストグラムはバケット1で生成されている。ここにAUTOなりSKEWONLYなり指定すれば、適切なバケット数でヒストグラムが作成されるはずだ。
