SQLパフォーマンス詳細 2章:Where句_5.ユーザー定義関数

2章:Where句

5.ユーザー定義関数

結論

• ユーザー定義関数でも関数インデックスは作成できる
• ただし、確定的（deterministic）な関数のみ可能
• 現在時刻（SYSDATE など）を参照する関数は不可

なぜ制限があるのか

• インデックスは「作成・更新時に評価した値」を保持する
• 非確定的関数は、時間経過で結果が変わる
• そのためインデックスの内容が正しくなくなり、DBは作成を許可しない

例：作成できない関数

CREATE FUNCTION get_age(date_of_birth DATE)
RETURN NUMBER
AS
BEGIN
  RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth) / 12);
END;

SELECT *
FROM employees
WHERE get_age(date_of_birth) = 42;

• SYSDATE を使っているため 非確定的
• この関数を使った 関数インデックスは作成不可

作成できる条件

• 同じ引数 → 常に同じ戻り値
• Oracleでは DETERMINISTIC 宣言が必要*

CREATE FUNCTION f(x NUMBER)
RETURN NUMBER DETERMINISTIC
AS
BEGIN
  RETURN x * 2;
END;

まとめ

• 関数インデックスは強力だが 万能ではない
• 非確定的な関数はインデックス化できない
• Where句で関数を使う場合は
  ➡ 「確定的か？」を必ず確認する