タイトルロゴ

記載日 2006/03/03
はじめに

 SELECT文のSELECTリストやWHERE句等で関数を用いた場合、その関数が何回実行されているのか 疑問に思ったことはないでしょうか。

 例えば、偶数・奇数を判別する関数JUDGE_FUNCを自作したとします。この関数を次の様に使用した場合、JUDGE_FUNC関数は 何回実行されていると思いますか?

     SELECT N, JUDGE_FUNC(N) FROM table WHERE JUDGE_FUNC(N) > 1;

 疑問に思ったことは何が何でも調べたくなります。色々考えたあげく、 シーケンスオブジェクトを用いた調査方法を思い付いたので実施しました。



セクション













検証環境

 
ページのTOPへ

 Oracleには、シーケンスオブジェクトと言うオブジェクトが 存在しています。連続した一意の整数を生成することができ、 伝票番号等の採番に、一意キーを生成する目的で使用することができます。

 シーケンスオブジェクトの作成構文を紹介します。


■ シーケンスオブジェクト作成構文

  CREATE SEQUENCE シーケンス名                --// シーケンスオブジェクト名称
  START WITH 初期値                           --// シーケンスの最初の数値を何にするか指定します。
  INCREMENT BY 増分                           --// シーケンスをいくつずつ増加させるか指定します。
  MAXVALUE 最大値                             --// シーケンスの最大値を指定します。
  MINVALUE 最小値                         --// シーケンスの最小値を指定します。
  CYCLE/NOCYCLE                          --// シーケンスをサイクルして使用するか指定します。
  CACHE キャッシュする数/NOCACHE;         --// シーケンスをキャッシュするか指定します。

 このシーケンスオブジェクトを利用して、SELECT文に記載した関数が、何回実行されているのか 調べてみたいと思います。

 まずは、次のシーケンスオブジェクトを作成します。
CREATE SEQUENCE  ← こちらをクリック

/* -------------------------------- */
/* テストシーケンス                 */
/* -------------------------------- */
CREATE SEQUENCE TEST_SEQ 
START WITH         1 
INCREMENT BY       1 
MAXVALUE           10000 
MINVALUE           1 
CYCLE 
NOCACHE 
;

 次に、下記の関数を用意します。 この関数は、NUMBER型の引数を一つ受取り、その引数に何も手を入れずそのまま戻り値として呼出元に戻します。 しかし、この関数は、戻り値を呼出元に戻す前に、上記シーケンスオブジェクトを一つ進める処理を 行います。 つまり、この関数をSELECT文に組込んで、SELECT文の実行前後におけるシーケンスの値を確認すれば、 SELECT文の中で、何回この関数が呼出されたのかわかります。
CREATE FUNCTION  ← こちらをクリック

/* ------------------------------------------ */
/* 関数呼出しチェック                         */
/* 引 数:IP_NUM NUMBER                      */
/* 戻り値:NUMBER                             */
/* 説 明:引数に渡した数値をそのまま戻す     */
/* ------------------------------------------ */
CREATE OR REPLACE FUNCTION CALL_FUNCTION(
      IP_NUM  NUMBER 
) RETURN NUMBER
IS
    WK_NUM NUMBER;  /* シーケンスオブジェクトを一つ進めるための作業用変数。 */
BEGIN
    /* シーケンスオブジェクトを一つ前進させます。 */
    SELECT TEST_SEQ.NEXTVAL INTO WK_NUM FROM DUAL;

    /* 引数で受取ったものをそのまま呼出元に返します。 */
    RETURN IP_NUM;
END CALL_FUNCTION;
/

 最後に、以下の様な数値型の列を一つだけ持つ単純なテーブルを用意します。 これで、事前準備は完了です。
CREATE TABLE  ← こちらをクリック

SQL> DESC NUM_T;
 Name                  Null?    Type
 --------------------- -------- ---------
 N                              NUMBER

SQL> SELECT N FROM NUM_T;

         N
----------
         1
         2
         3
         4
         5

5 rows selected.

SQL>


 
ページのTOPへ

 条件文無しのSELECT文のSELECTリストに関数を用いた場合、何度その関数が呼出されるか確認してみます。


    SELECT function( ) FROM table;

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
         1

1 row selected.

SQL> SELECT CALL_FUNCTION(N) FROM NUM_T;

CALL_FUNCTION(N)
----------------
               1
               2
               3
               4
               5

5 rows selected.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
         6

1 row selected.

SQL>

 シーケンスは5つ前進しました。NUM_Tテーブルには 5レコード存在しています。検索したレコード全てに対して関数が実行されるので、 関数は5回呼出されたことを表しています。


 
ページのTOPへ

 条件文有りのSELECT文のSELECTリストに関数を用いた場合、何度その関数が呼出されるか確認してみます。


    SELECT function( ) FROM table WHERE 条件式;

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
         6

1 row selected.

SQL> SELECT CALL_FUNCTION(N) FROM NUM_T
  2 WHERE N > 2;

CALL_FUNCTION(N)
----------------
               3
               4
               5

3 rows selected.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
         9

1 row selected.

SQL>

 シーケンスは3つ前進しました。NUM_Tのレコードの中で、 条件式「N > 2」を満たすレコードが3レコード有るので、 検索したレコード数分、関数が呼出されたことを表しています。

 次の条件式の場合はどうでしょうか。


    SELECT function( ) FROM table WHERE function( )を用いた条件式;

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
         9

1 row selected.

SQL> SELECT CALL_FUNCTION(N) FROM NUM_T
  2 WHERE CALL_FUNCTION(N) > 2;

CALL_FUNCTION(N)
----------------
               3
               4
               5

3 rows selected.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        17

1 row selected.

SQL>

 シーケンスは8つ前進しました。 NUM_Tのレコードは全部で5レコードです。 検索条件に見合ったレコードを特定するために全レコードに関数が実行されます。

 条件に見合った行を特定した後、検索結果を返すために、検索条件に見合ったレコード全て に対して関数が実行されます。

NUM_Tのレコードの中で、 条件式「CALL_FUNCTION(N) > 2」を満たすレコードが3レコードあるので、 合計8回関数が呼出されたのです。


 
ページのTOPへ

 インラインビューを用いたSELECT文に関数を用いた場合、何度その関数が呼出されるか確認してみます。 見かけ上、関数の呼出し回数が少ないと言う錯覚に陥りますが、調べてみれば一目瞭然です。


 SELECT func FROM ( SELECT function( ) func FROM table ) WHERE func > 0;

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        17

1 row selected.

SQL> SELECT func FROM ( SELECT CALL_FUNCTION(N) func FROM NUM_T ) WHERE func > 2;

      FUNC
----------
         3
         4
         5

3 rows selected.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        25

1 row selected.

SQL>

 シーケンスは8つ前進しました。

 インラインビューを用いて、SQLの見かけ上、関数の登場回数を減らしましたが、 結果的に呼出される回数は、(NUM_Tの全レコード数)+(NUM_Tの検索条件に一致したレコード数) と言うことになります。


 
ページのTOPへ

 PL/SQL無名ブロック を用いて、明示的カーソル のSELECT文の中で関数を用いた場合、その関数は何度呼出されるか確認してみます。ここでは、 以下のSELECT文を明示的カーソルとして使用します。


    SELECT function( ) FROM table WHERE function( )を用いた条件式;

 まず最初は、カーソルをOPEN して、FETCH せずに直ぐCLOSE するという処理を行ってみます。

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        25

1 row selected.

SQL> DECLARE
  2      CURSOR TEST_CUR
  3      IS
  4          SELECT CALL_FUNCTION(N) FROM NUM_T
  5          WHERE CALL_FUNCTION(N) > 2;
  6  BEGIN
  7      /* カーソルOPEN */
  8      OPEN TEST_CUR;
  9      /* カーソルCLOSE */
 10      CLOSE TESt_CUR;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        25

1 row selected.

SQL>

 シーケンスは全く前進しませんでした。 これは、PL/SQLで明示的カーソルをOPENするだけでは、 SELECT文が検索条件に見合ったレコードを特定するわけではないので、SELECT文に組込んだ関数が 呼出されているわけではないと言うことです。

 次に、カーソルをOPEN して、1レコードだけFETCH して、その後CLOSE するという処理を行ってみます。

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        25

1 row selected.

SQL> DECLARE
  2      FETCH_NUM NUMBER;
  3      CURSOR TEST_CUR
  4      IS
  5          SELECT CALL_FUNCTION(N) FROM NUM_T
  6          WHERE CALL_FUNCTION(N) > 2;
  7  BEGIN
  8      /* カーソルOPEN */
  9      OPEN TEST_CUR;
 10      /* カーソルFETCH */
 11      FETCH TESt_CUR INTO FETCH_NUM;
 12      /* カーソルCLOSE */
 13      CLOSE TESt_CUR;
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        29

1 row selected.

SQL>

 シーケンスは4つ前進しました。

 明示的カーソルでは、検索条件を満たすレコードが見つかるまで関数を呼出して、 レコードを評価します。NUM_Tの3件目のレコードにおいて、条件を満たす レコードを発見したので、そのレコードを検索結果として取り出し、FETCHします。 FETCHする際に、そのレコードに対して再度、関数を呼出しているので、シーケンスが合計4つ前進したのです。

 最後に、カーソルをOPEN して、全レコードをFETCH して、その後CLOSE するという処理を行ってみます。

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        29

1 row selected.

SQL> DECLARE
  2      FETCH_NUM NUMBER;
  3      CURSOR TEST_CUR
  4      IS
  5          SELECT CALL_FUNCTION(N) FROM NUM_T
  6          WHERE CALL_FUNCTION(N) > 2;
  7  BEGIN
  8      /* カーソルOPEN */
  9      OPEN TEST_CUR;
 10      LOOP
 11          /* カーソルFETCH */
 12          FETCH TESt_CUR INTO FETCH_NUM;
 13          EXIT WHEN TESt_CUR%NOTFOUND; 
 14      END LOOP;
 15      /* カーソルCLOSE */
 16      CLOSE TESt_CUR;
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL;

   CURRVAL
----------
        37

1 row selected.

SQL>

 シーケンスは8つ前進しました。

 明示的カーソルでは、検索条件を満たすレコードが見つかるまで関数を呼出して、 レコードを評価します。NUM_Tの3件目のレコードにおいて、条件を満たす レコードを発見し、その後、4件目〜5件目のレコードと同じことを行います。 NUM_Tに評価すべきレコードが5件あり、条件に見合ったレコードが3件あります。 その3件のレコードを特定した後、取り出す際に、再度関数を適応します。

 カーソルFORループを使用したループ方法でも、もちろん同じ結果になります。


 
ページのTOPへ
 SELECT文に組込む関数がユーザ定義関数で、その関数の内部で テーブルアクセスが頻繁に行われる場合など、できるだけSELECT文中での関数呼出回数を減 らしたくなります。 その様な場合は、関数をSELECT文のSELECTリストに組み込み、条件式には組込まない 様にします。そして、検索したレコードをバッファに格納して、ストアドでレコードの振り分けを行いましょう。