2章:where句
4.関数
結論
-
UPPER()やLOWER()を WHERE句の列側に使うと、通常のインデックスは使われない - 大文字・小文字を区別しない検索を パフォーマンスを落とさずに行うには、関数インデックスが必要
- 関数インデックス作成後は、統計情報の更新が必須
なぜ通常のインデックスが使われないのか
SQL例
SELECT first_name, last_name, phone_number
FROM employees
WHERE UPPER(last_name) = UPPER('winand');
問題点
-
last_nameにインデックスがあっても使われない - 行計画は TABLE ACCESS FULL
理由（オプティマイザ視点）
- データベースにとって
last_nameとUPPER(last_name)は 完全に別物 - オプティマイザからは次のように見える
WHERE BLACKBOX(last_name) = 'WINAND'
-
UPPER()は「中身が分からない関数（ブラックボックス）」 - 列の値と結果の対応関係が分からないため、インデックスが使えない
解決策：関数インデックスを使う
インデックス作成
CREATE INDEX emp_up_name
ON employees (UPPER(last_name));
効果
-
UPPER(last_name)をキーにした Bツリーインデックス - 実行計画は INDEX RANGE SCAN
- フルテーブルスキャンを回避できる
実行計画の見積もりが変になる理由
見積もりの矛盾例
- INDEX RANGE SCAN：40行取得と見積もり
- TABLE ACCESS：100行取得と見積もり
これは 論理的にありえない
（インデックスで40行しか返さないのに、テーブルから100行読むことはできない）
原因：統計情報が古い
- 関数インデックス作成時、Oracleは自動で統計情報を更新しない場合がある
- 結果として、オプティマイザが誤った行数・コストを見積もる
対応
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
- 統計情報更新後、行数・コスト見積もりは正常化
- オプティマイザは正しい実行計画を選択できる
まとめ
- UPPER() / LOWER() を WHERE句で使うと 通常インデックスは無効
- 解決には 関数インデックス（Function-Based Index）
- インデックス作成後は 必ず統計情報を更新
- 「関数を使った検索」は
インデックス設計 + 統計情報 の両方が揃って初めて高速になる