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 <パラメータ名>」のコマンドで。

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

株式会社ライズウィル

株式会社ライズウィル

〒103-0013
東京都中央区日本橋人形町2-15-1
フジタ人形町ビル7F
TEL : 03-4590-3200
FAX : 03-4590-3201
E-Mail : info@risewill.co.jp
URL : https://www.risewill.co.jp

海上コンテナ輸送管理システム「海コン魂!」
海コン魂!
フルカスタマイズ可能な求人サイトパッケージ
jobtter