ユーザ定義関数

関数インデックスは、非常に一般的なアプローチです。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関数を含んだインデックスは作成できません。 それは、この関数が確定的でないからです。つまり、関数を呼び出した結果が、パラメータによって一意に決まらないのです。同じパラメータに対して 同じ結果が常に返る、つまり確定的な関数にのみ、インデックスを作成できます。

この制限の理由は簡単です。新しい行を挿入した時、データベースは 関数を呼び出し、その結果をインデックスに保存します。この時、インデックスの場所は変わりません。インデックスを更新する定期的な処理というのは存在 しません。インデックスされた年齢をデータベースが更新するのは、update文により誕生日が更新される時のみです。 従って、そのままだと、次の誕生日の後にはインデックスに入っている年齢は間違ったものになってしまいます。

PostgreSQLとOracleにおいて、インデックス内で使用する 関数が確定的であるためには、確定的で あると宣言したものである必要があります。 これには、DETERMINISTIC (Oracle)あるいはIMMUTABLE (PostgreSQL)を使用します。

「インデックスを作れない」関数の他の例としては、乱数発生器や、環境変数に依存する関数があります。