ファンクション・インデックス(ファンクション索引)の作成

Oracle 標準関数とユーザー定義関数(PL/SQL) を使用したファンクションインデックスの作成方法と注意点について

  • サンプル・テーブル

見た目は全角カタカナですが、半角カタカナとみなしてください。

CREATE TABLE RIVUS.ADDRESS_SAMPLE
(
    POST_CODE        VARCHAR2(8)  NOT NULL,
    ADDRESS_KANA     VARCHAR2(50) NOT NULL,
    ADDRESS_KANJI    VARCHAR2(50) NOT NULL
);
POST_CODEADDRESS_KANAADDRESS_KANJI
100-0014トウキョウトチヨタ゛クナガタチョウ東京都千代田区永田町
102-0082トウキョウトチヨタ゛クイチハ゛ンチョウ東京都千代田区一番町
100-0013トウキョウトチヨタ゛クカスミカ゛セキ東京都千代田区霞が関
106-6101トウキョウトミナトクロッホ゜ンキ゛東京都港区六本木
108-0074トウキョウトミナトクタカナワ東京都港区高輪

半角カタカナの ADDRESS_KANA に通常のインデックスを作成しても辞書順のレンジスキャン、ソートではインデックスを使用することができない。(濁点、半濁点などが1文字として扱われる)

-- 辞書順(検索、並び替え)では、意味のないインデックス
CREATE INDEX ADDRESS_SAMPLE_IX1
 ON ADDRESS_SAMPLE ( ADDRESS_KANA );

ファンクション・インデックスの定義

Oracle の標準関数 NLSSORT を使用したインデックスの例

CREATE INDEX ADDRESS_SAMPLE_IX2
 ON ADDRESS_SAMPLE
 (NLSSORT(ADDRESS_KANA, 'NLS_SORT = JAPANESE_M'));

関数を使用しなくても 式でもファンクションインデックス作成は可能。

CREATE INDEX triangle_index
 ON triangles
 ( ( col1 * col2 ) / 2 );

のように定義式でも良い。

ユーザー定義関数でのファンクションインデックス

ユーザー定義関数を使用する場合には、関数に特別な定義の記述を追加する必要がある。

DETERMINISTIC 定義関数

将来にわたり入力引数値の集合に対して常に決定的(同じ出力戻り値を戻す)であることを保証しているという定義。 記述すれば Oracle が保証するというものではなく、関数の定義者が責任をもって対応している*1ということの証。

インデックスには計算済みの値が格納されるため、戻り値が変化するとインデックススキャンは間違った結果を戻すことになる。 そのため、システム日時やテーブルによって結果が影響を受ける関数の利用は控える。
たとえ理論上は決定的でも過失などでインデックスの値が変化したことに、だれも気づかず原因不明の検索結果の異常からユーザーに説明するなどの事態は避けたい。

定義例

CREATE OR REPLACE FUNCTION ROUNDUP(P_NUM IN NUMBER, P_SCALE IN INTEGER := 0)
RETURN NUMBER
DETERMINISTIC       -- ← ここ
AS
    vBase NUMBER;
BEGIN
    vBase := POWER(10, P_SCALE);
    RETURN CEIL(ABS(P_NUM) * vBase) / vBase * SIGN(P_NUM);
END;
/

ファンクション索引が無効になるケース

以下の条件に該当する場合、インデックスが無効化されるので注意

  • インデックスの所有者からユーザー定義関数の実行権限が消失した場合
  • ファンクションに再コンパイルが必要になる状況になった場合

パッケージに内包する関数などを使用している場合には、要注意。
使用する関数は、最高品質で非常に安定している(依存を含め頻繁な拡張予定のない)パッケージに含めるなどの配慮をしておきたい。

  • 関連初期化パラメータ

    SKIP_UNUSABLE_INDEXES : インデックスが無効化している場合 DML が失敗する、しないを制御する。

ファンクションインデックスの制限

  • インデックスの作成後に 統計情報の収集を行わなければならない。(インデックスの統計情報が必要になる)
    統計情報の収集 (DBMS_STATS)
    インデックス統計を収集できれば、どの収集タイプでも良い。
  • 式では 表の同一レコードのみ使用できる(集計、分析、モデル関数など不可)。
    VARCHAR2、RAW などの関数において、列長の上限が不明(未設定)になる引数は使用不可
    SUBSTR(col, 1) ... NG
    SUBSTR(col, 1, 99) ... OK
  • ビットマップでの降順配置は不可


関連事項

ライセンス

Oracle 9i リリース2 より以前の場合にはエンタープライズ・エディションが必要

サイト統合にともない代替情報の URL は不明

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター

*1 DETERMINISTIC 定義関数 から DETERMINISTIC 定義していない関数も呼び出すことも可能になっている。