by Hayato Matsuura.

ユーザ定義関数


関数インデックスは、非常に一般的なアプローチです。 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)を使います。こういった関数は、 selectwhere句といった 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)を使用します。

注意

PostgreSQLやOracleは、DETERMINISTICあるいは IMMUTABLEの宣言を信じます。つまり、開発者を 信じているということです。

GET_AGE関数を確定的であると宣言し、 インデックスの定義の中で使うことは可能です。しかしその宣言に 関わらず、年が変わってもインデックスに保存された年齢は変わらず、 少なくともインデックスに保存された従業員は歳を取らないことに なるので、正常な動作はしません

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

ヒント

42歳の従業員全てを検索するクエリを最適化するのに、 それでもインデックスを使うにはどうしたらよいでしょうか?

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license