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

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

この制限の理由は簡単です。新しい行を挿入した時、データベースは 関数を呼び出し、その結果をインデックスに保存します。この時、インデックスの場所は変わりません。インデックスを更新する定期的な処理というのは存在 しません。インデックスされた年齢をデータベースが更新するのは、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/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license