Oracleの「ファンクション・インデックス」について

どうも。採用担当兼エンジニアの小田です。

今日は、最近現場で便利だなと思ったOracleの「ファンクション・インデックス」を紹介します。
※「ファンクション・インデックス」はOracle8iからある機能だそうです。

■SELECT文①

仮に、キーNo.の10文字目から3文字が検索条件となっているようなテーブルを検索する場合、以下のようなSELECT文を実行しますよね。


SELECT * FROM SAMPLE_TABLE01
WHERE SUBSTR(KEY_NO, 10, 3) = 'ABC';
このSELECT文①の実行計画を確認してみると、通常は「TABLE ACCESS FULL」となります。

■ファンクション・インデックスの作成

こういう場合に便利なのが、「ファンクション・インデックス」です。


CREATE INDEX SAMPLE_INDEX01
ON SAMPLE_TABLE01 (SUBSTR(KEY_NO, 10, 3));
ファンクション・インデックス作成後、上記のSELECT文①を実行してみると、作成したインデックス(SAMPLE_INDEX01)を使用していることが確認できます。

また、このファンクション・インデックスが便利なのは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 <パラメータ名>」のコマンドで。

いかがでしたか?次回もお楽しみに。それではまたお会いしましょう。
DXO株式会社

DXO株式会社

〒103-0014
東京都中央区日本橋蛎殻町2-13-6
EDGE水天宮8F
E-Mail : contact-info@dxo.co.jp
URL : https://dxo.co.jp