ファンクション・インデックス（ファンクション索引）の作成
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_CODE | ADDRESS_KANA | ADDRESS_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 が失敗する、しないを制御する。
ファンクションインデックスの制限
関連事項
ライセンス
Oracle 9i リリース2 より前の場合にはエンタープライズ・エディションが必要
サイト統合にともない代替情報の URL は不明
