関数インデックスは、非常に一般的なアプローチです。UPPER
のような関数だけでなく、A +
B
のような式に対してや、ユーザ定義関数に対してもインデックスを作成することができます。
ただし、これには1つ重要な例外があります。それは、以下の例のように、 直接であるか間接であるかを問わず、インデックスの定義内で現在時刻を参照することはできないということです。
CREATE FUNCTION get_age(date_of_birth DATE)
RETURN NUMBER
AS
BEGIN
RETURN
TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
END
関数GET_AGE
は、与えられた誕生日から年齢を計算するのに
現在の日付(SYSDATE
)を使います。こういった関数は、select
やwhere
句といったSQLクエリのどの部分でも使用できます。
SELECT first_name, last_name, get_age(date_of_birth)
FROM employees
WHERE get_age(date_of_birth) = 42
このクエリは、42歳の従業員を全て列挙します。関数インデックスを使うのは、クエリを最適化するのに明らかにいい考えだと思えますが、
GET_AGE
関数を含んだインデックスは作成できません。
それは、この関数が確定的でないからです。つまり、関数を呼び出した結果が、パラメータによって一意に決まらないのです。同じパラメータに対して
同じ結果が常に返る、つまり確定的な関数にのみ、インデックスを作成できます。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
この制限の理由は簡単です。新しい行を挿入した時、データベースは
関数を呼び出し、その結果をインデックスに保存します。この時、インデックスの場所は変わりません。インデックスを更新する定期的な処理というのは存在
しません。インデックスされた年齢をデータベースが更新するのは、update
文により誕生日が更新される時のみです。
従って、そのままだと、次の誕生日の後にはインデックスに入っている年齢は間違ったものになってしまいます。
PostgreSQLとOracleにおいて、インデックス内で使用する
関数が確定的であるためには、確定的で
あると宣言したものである必要があります。
これには、DETERMINISTIC
(Oracle)あるいはIMMUTABLE
(PostgreSQL)を使用します。
注意
PostgreSQLやOracleは、DETERMINISTIC
あるいは
IMMUTABLE
の宣言を信じます。つまり、開発者を信じているということです。
GET_AGE
関数を確定的であると宣言し、
インデックスの定義の中で使うことは可能です。しかしその宣言に関わらず、年が変わってもインデックスに保存された年齢は変わらず、
少なくともインデックスに保存された従業員は歳を取らないことに
なるので、正常な動作はしません。
「インデックスを作れない」関数の他の例としては、乱数発生器や、環境変数に依存する関数があります。
ヒント
42歳の従業員全てを検索するクエリを最適化するのに、それでもインデックスを使うにはどうしたらよいでしょうか?