今日は、最近現場で便利だなと思ったOracleの「ファンクション・インデックス」を紹介します。
※「ファンクション・インデックス」はOracle8iからある機能だそうです。
■SELECT文①
仮に、キーNo.の10文字目から3文字が検索条件となっているようなテーブルを検索する場合、以下のようなSELECT文を実行しますよね。
SELECT * FROM SAMPLE_TABLE01
WHERE SUBSTR(KEY_NO, 10, 3) = 'ABC';
■ファンクション・インデックスの作成
こういう場合に便利なのが、「ファンクション・インデックス」です。
CREATE INDEX SAMPLE_INDEX01
ON SAMPLE_TABLE01 (SUBSTR(KEY_NO, 10, 3));
また、このファンクション・インデックスが便利なのはOracle独自の関数だけでなく、自分で作成した関数も指定できるところです。
■Functionの作成
例えば、税抜の金額のみを保持している項目「AMOUNT」があるとして、消費税込の金額を条件に指定してSELECT文を作成したい場合、まず、関数（Function）を作成します。
CREATE OR REPLACE FUNCTION TAX_IN_AMOUNT (P_AMOUNT IN NUMBER)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
RETURN CEIL(P_AMOUNT * 1.08);
END;
/
■ファンクション・インデックスの作成
上記同様にファンクション・インデックスを作成します。
CREATE INDEX SAMPLE_INDEX02
ON SAMPLE_TABLE02 ( TAX_IN_AMOUNT(AMOUNT) );
■SELECT文②
これでSELECT文②を実行すると、無事作成したインデックス（SAMPLE_INDEX02）が使用されます。
SELECT * FROM SAMPLE_TABLE02
WHERE TAX_IN_AMOUNT(AMOUNT) >= 108000;
ぜひ、お試しください。
■注意点
なおファンクション・インデックス作成時の注意点を挙げておきます。・ファンクション・インデックス作成後は、必ず統計情報の収集（ANALYZE）を行う。
・作成したFunctionをファンクション・インデックスに使用する場合、必ず「DETERMINISTIC」を指定。
・「QUERY_REWRITE_ENABLED」パラメータは「TRUE」。
※パラメータの確認はSQL*Plus等で、「SHOW PARAMETERS ＜パラメータ名＞」のコマンドで。
いかがでしたか？次回もお楽しみに。それではまたお会いしましょう。
