ITコーディネータのシュウです。

先日、妻の知人の誘いで、埼玉県日高市のほうで行われた、馬射戲（マサヒ）騎射競技大会、それから高麗神社に行ってきました。馬射戲（マサヒ）というのは、高句麗古墳壁画の世界を再現した騎射競技ということで、日本の流鏑馬（やぶさめ）のルーツではないかと言われているようです。上の写真は、馬上から射た弓が的に的中して、まさに的が割れたところを撮った写真です。
埼玉県の日高市、飯能市の辺りは、716年に建郡された高麗郡といわれた地域であり、来年2016年が、建郡1300年ということで、いろいろな記念事業が行われていくようです。
実は、歴史やルーツというものが結構好きな私は、今回高麗（こま）神社も初めて行ってきたのですが、歴史を感じるとともに、埼玉県というのは渡来人とのつながりが深い地域だということを改めて知った1日でした。久しぶりに妻と一緒に歩いたのも、昔に戻ったようでうれしかったですね！

＜本日の題材＞
ストアド・ファンクション

今まで、何度かストアド・プロシージャを使用するような例を上げたことがありますが、ストアド・ファンクションについてはあまり取り上げて来なかったと思いますので、今回、題材に上げてみたいと思います。

ファンクションは、処理後に計算結果を1つだけ戻すもので、戻り値を持っています。戻り値は、Oracleの場合はRETURN句、SQL Serverの場合はRETURNS句でデータ型を設定します。戻す値については、ORACLE、SQL Serverともに、BEGIN ～ ENDで囲まれる部分の中でRETURN文によって設定します。
なお、プロシージャの場合は、引数でOUTパラメータを設定することで、複数の値を戻すことができますが、それは引数であって戻り値ではないということです。

また、プロシージャの場合は、CALL文やEXEC文で実行しますが、ファンクションの場合は、通常のSQLのSELECT文に直接記述して結果を得ることができます。
SELECT ファンクション名(xx) … FROM …

例１）
あるシステムで、年ごとに、いつからいつまでが第何週かを独自に設定している「WEEKマスタ」というものを準備し、指定した日付が第何週目かを確認するファンクションを作成します。

SQL Serverの場合：
テーブルの定義は、
CREATE TABLE [dbo].[WEEKマスタ](
        [年] [decimal](4, 0) NOT NULL,
        [WEEK] [decimal](2, 0) NOT NULL,
        [開始日] [date] NOT NULL,
        [終了日] [date] NOT NULL,
CONSTRAINT [PK_WEEKマスタ] PRIMARY KEY CLUSTERED ([年] ASC,[WEEK] ASC)
/

データを確認すると、
SELECT年, WEEK, 開始日,終了日
  FROM dbo.WEEKマスタ
 ORDER BY 年, WEEK;

ファンクションは例えば以下のようになります。

CREATE FUNCTION dbo.USF001_WEEK取得(
    @P年月日   DATE
)   RETURNS    DECIMAL(2,0)
AS
BEGIN
    DECLARE    @WEEK    DECIMAL(2,0) = 0;
    SELECT @WEEK = WEEK
      FROM dbo.WEEKマスタ
     WHERE @P年月日 BETWEEN開始日 AND終了日;
    RETURN    @WEEK
END;
GO

実際にこのファンクションを使って、指定した日付が第何週になるかを確認してみます。

SELECT dbo.USF001_WEEK取得('2015-11-16')

46週めという結果が出ました。
これを、Oracleで同じように試してみます。

テーブル定義は、
CREATE TABLE WEEKマスタ(
年     NUMBER(4) NOT NULL,
WEEK NUMBER(2) NOT NULL,
開始日 DATE NOT NULL,
終了日 DATE NOT NULL,
 CONSTRAINT PK_WEEKマスタ PRIMARY KEY(年, WEEK));

ファンクションは先ほどと同様にすると、以下のようにできます。

CREATE OR REPLACE FUNCTION WEEK取得(P_年月日 IN DATE)
RETURN NUMBER
AS
  V_WEEK    DECIMAL(2,0) := 0;
BEGIN
  SELECT WEEK INTO V_WEEK
    FROM WEEKマスタ
    WHERE P_年月日 BETWEEN 開始日 AND 終了日;
  RETURN    V_WEEK;
END;
/

このファンクションをSELECT文の中で使用して、WEEKを取得すると

SELECT WEEK取得('2015-11-16') FROM DUAL;

 結果は先ほどと同じく46週めですね。

例２）
メタボの予防などで、肥満度の話が出てきますが、その肥満度をチェックするのに、BMI（肥満指数）というものを用いることが多いと思います。今回は、その肥満指数BMIの計算をファンクションで実行してみたいと思います。
ファンクションは以下のようにできます。（SQL Server）

CREATE FUNCTION dbo.BMI取得(
    @体重      DECIMAL(5,2)
   ,@身長      DECIMAL(5,2)
)   RETURNS    DECIMAL(7,5)
AS
BEGIN
    DECLARE    @BMI    DECIMAL(7,5) = 0;
    SELECT @BMI = @体重 /(@身長/100 * @身長/100);
    RETURN    @BMI
END;
GO

実際に試してみると、体重：61.0kg、身長：165.5cmの場合

SELECT dbo.BMI取得(61.0, 165.5)

 

結果は、22.27070 ということで、肥満度は普通ということですね。
調べてみると、BMI指数の値によって、以下のように言われているようです。
・18.5未満 ＝痩せ
・18.5～25＝普通
・25～30 ＝肥満レベル1
・30～35 ＝肥満レベル2
・35～40 ＝肥満レベル3
・40～ ＝肥満レベル4

試しに、知人の値を確認してみます。

SELECT dbo.BMI取得(82.0, 164.0)

 BMI指数が30を超えているので、肥満レベル2ですね。生活習慣病には是非気を付けてほしいものです。

ちなみに、ORACLEでも同様のファンクションを作成すると、以下のようになります。

CREATE OR REPLACE FUNCTION BMI取得 (P_体重 NUMBER, P_身長 NUMBER)
RETURN NUMBER
AS
  V_BMI    NUMBER(7,5) := 0;
BEGIN
    SELECT P_体重 / (P_身長/100 * P_身長/100) INTO V_BMI
      FROM DUAL;
    RETURN    V_BMI;
END;
/

実行してみると、

SELECT BMI取得(61.0, 165.5) AS BMI FROM DUAL;

結果はSQL Serverのときと同じ値が出ました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

コスモスがとてもきれいだったので写真に撮りました。ここしばらく、仕事のほうが忙しく、なかなかブログを作成できなかったのですが、久しぶりの投稿です。

さて、実りの秋、食欲の秋、スポーツの秋、読書の秋、いろいろな言葉で表現される秋もかなり深まってきました。
私も先月は、妻に引っ張られながら、なまった体に鞭打って運動会にも何とか頑張って参加しました。子供の学校においては、高校では文化祭、中学では合唱のクラス対抗発表会などもあり、たまには父親らしいことをしようと、子供たちの姿を見に行って来ました。
プロ野球は日本シリーズが終わり、今はプレミア12が始まっています。日本は3連勝で頑張っていますね。体操の世界選手権では日本が37年ぶりの金メダル、そして、内村航平選手が、前人未到の個人総合6連覇！　いやあ、挙げ出すといろいろありますね。そしてみんな頑張っているんですよね。私も、ちょっと疲れているけど、頑張るぞ！
．．．と気合は入れてみたんですが、なかなか力が出ないのも事実。今日は久しぶりに早く帰ろうかな～。

＜本日の題材＞
ファンクションインデックス

いろいろなシステムを担当すると、あるデータを抽出しなければならないときに、テーブル同士のジョインに、既存のキーとなる項目をそのまま使用することができず、関数を使用して項目を加工したかたちで条件を設定しなければならない場合に時折遭遇します。
そんなとき、データ件数が多い場合には、インデックスをうまく使えないために処理時間がかなりかかってしまい、問題になることがあります。

最近の開発案件でもそういうケースがあり、どうしたらよいかを検討したところ、ORACLEの機能にファンクションインデックスというものがあり、それを使うことで処理時間を短縮することができました。
今日は、それを取り上げてみたいと思います。

実際に行ったケースはちょっと複雑だったため、簡単な例で試してみたいと思います。

テーブル「TAB_C」、テーブル「TAB_D」があり、定義は以下のようだとします。

CREATE TABLE TAB_C(
 C_CODE_1   VARCHAR2(20)
,C_数量       NUMBER(12)
,CONSTRAINT PK_TAB_C PRIMARY KEY (C_CODE_1));

CREATE TABLE TAB_D(
 D_CODE_1   VARCHAR2(20)
,D_数量       NUMBER(12)
,CONSTRAINT PK_TAB_D PRIMARY KEY (D_CODE_1));

データを以下のように作成します。
TAB_Cの「C_CODE_1」は、最初の文字が「C」で後は1からの連番、TAB_Dの「D_CODE_1」は、最初の文字が「D」で後は1からの連番とします。また、C_数量、D_数量については、1～1000000 の間のランダムな整数を設定することにします。

DECLARE
  v_count NUMBER := 0;
  v_ccode VARCHAR2(20) := ' ';
  v_dcode VARCHAR2(20) := ' ';
BEGIN
  WHILE v_count < 1000000 LOOP
    v_count := v_count + 1;
    v_ccode := 'C'||CAST(v_count AS VARCHAR2);
    v_dcode := 'D'||CAST(v_count AS VARCHAR2);

    INSERT INTO TAB_C(C_CODE_1, C_数量)VALUES
  (v_ccode, FLOOR(DBMS_RANDOM.VALUE(1, 1000001)));
   INSERT INTO TAB_D(D_CODE_1, D_数量)VALUES
  (v_dcode, FLOOR(DBMS_RANDOM.VALUE(1, 1000001)));
END LOOP;
END;
/

※DBMS_RANDOM.VALUEは乱数を取得するのに使えます。

データの作成結果を確認してみます。
SELECT * FROM TAB_C
ORDER BY CAST(SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) AS NUMBER);

SELECT * FROM TAB_D
ORDER BY CAST(SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1) AS NUMBER);

TAB_C、TAB_Dとも1000000件作成されていて、数量はランダムな整数（1～1000000の間）になっているのが確認できます。

この2つのテーブルは、それぞれのテーブルの主キーである「C_CODE_1」「D_CODE_1」の2桁目以下の値でジョインすることで、1対1のデータを抽出できます。

このときのジョインの条件は、例えば以下のようになります。
SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) = SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)

この場合、C_CODE_1、C_CODE_2は、それぞれのテーブルのプライマリーキーであったとしても、SUBSTRやLENGTHという関数を使っているためにうまくインデックスを使った検索をしてくれない（全件検索になる）ので、テーブル件数が多い場合には、処理時間が非常にかかってしまいます。

実際に、ジョインした結果を抽出してみます。
（条件として、キーの2桁目以降が700000～710000のものに絞っています）

SELECT
SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1), SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)
  FROM TAB_C C
  JOIN TAB_D D
    ON SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) = SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)
 WHERE SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) BETWEEN 700000 AND 710000
 ORDER BY SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1);

実際に実行計画を取得してみると、
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 126399
    SORT ORDER BY 
        HASH JOIN  
            TABLE ACCESS FULL TAB_C
            TABLE ACCESS FULL TAB_D
-----------------------------------------------------------------
「TAB_C」「TAB_D」とも「TABLE ACCESS FULL」となっていて、フルスキャンしていることがわかります。
 
そこで、ファンクションインデックスを作成してみます。
CREATE  INDEX  IX_TAB_C_FUNC  ON  TAB_C
(SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1));
 
CREATE  INDEX  IX_TAB_D_FUNC  ON  TAB_D
 (SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1));

ファンクションインデックスは、索引自体と索引が定義される表が分析されるまで、使用されないということなので、分析します。

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'BLOG_TEST', TABNAME => 'TAB_C');

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'BLOG_TEST', TABNAME => 'TAB_D');

この状態で、再度、先ほどのSQLの実行計画を取得してみます。

------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 1445
    SORT ORDER BY 
        HASH JOIN  
            INDEX FAST FULL SCAN IX_TAB_C_FUNC
            INDEX FAST FULL SCAN IX_TAB_D_FUNC
-----------------------------------------------------------------

すると、確かに作成したファンクションインデックス「IX_TAB_C_FUNC」「IX_TAB_D_FUNC」が利用されていることが確認できますし、COSTもかなり小さな値になっています。
実際の抽出結果は、

処理自体、先ほどよりは早く結果が返って来ました。
こういうケースで、ファンクションインデックスを作成することは、レスポンス改善としては効果があることが分かります。

今日は以上まで

にほんブログ村