ストアド(PLSQL)でバッチを作る時の色々

最近引越しが終わった人です。

ここ数週間は業務用のバッチをストアドで書くことが多かったのでその時の注意メモ
特に夜間バッチ系で動くやつは速度やエラーハンドリング間違えると
監視の都合で検知はされると思いますが、まあ中々致命的な問題も引き起こすケースありますからね。

今から話す内容はこの本を購入して読めばこの記事で書いてあること大体できると思うので
ぜひ読んでみてください。

(SQLが書けること前提ですです)

速度編

バルク処理

自分はoracle12c環境でゴリゴリ書いているわけですが、
実はSQLの記載とPL/SQLの記載って実行エンジンが違うそう。
なのでエンジンが細かく切り替わる記載方法はコストがかかるので良くないそう

SQLエンジン

SELECT 1
  FROM DUAL;

PL/SQLエンジン

WK_NUM := 1 + 1;

簡単に言うとこういう記述を同じSQL内で書けますが、
内部的に解析するエンジンが違うぽいですね。

よくある例でFOR～LOOPとFORALLでは
FORALLのほうが早いと言われていますが、これはFORALLの記載がPLSQLエンジンのみ処理するからです

詳しくはここに書いてますね。

EMP表にNUMという数字を入れるカラムを作成して10000行入れると言う単純な処理を書いたときに2つを比べると処理に差が出てくるので試してみてください。

ダメな例

DECLARE
BEGIN
    --FOR記載のPLSQLエンジンと
    --INSERT分のSQLエンジンの切替にコストが掛かる
    FOR I IN 1..10000 LOOP

        INSERT INTO EMP
        VALUES(I);

    END LOOP;
    COMMIT;
END;

良い例

DECLARE
    --タイプ作成
    TYPE HOGE_T IS TABLE OF EMP.NUM%TYPE INDEX BY PLS_INTEGER; 
    --リスト作成
    L_HOGE   HOGE_T;
BEGIN

    FOR I IN 1..10000 LOOP
        --リストに要素を拡張
         L_HOGE(L_HOGE.COUNT + 1) := I;
    END LOOP;

    --PLSQLエンジンのみで一括格納
    FORALL I IN 1 .. L_HOGE.COUNT
    INSERT INTO EMP
    VALUES(L_HOGE(I));


    COMMIT;
END;

ストアドファンクションのコールについて

たまに「共通化や！！！」みたいなことを言ってカプセル化をする人多いのですが、
ビジネスロジックが99%のこの言語では、可読性も大事ですが、速度は維持したい所。
ですがなんでもカプセル化して共通化して頭を殺して使用するとブーメランが帰ってきます。

ファンクション内でSQLエンジンを動かすリスク

例えばとある管理マスタの項目の数字を引数の数字と掛け算して返すというモジュール作ります。
(先程のEMP表を使います1レコードしか帰ってこない前提で)

ファンクション

CREATE OR REPLACE FUNCTION FN_MULTI_MASTER
(
    IN_NUM     IN NUMBER
) RETURN NUMBER
IS
    WK_RTN NUMBER;
BEGIN
    SELECT EMP.NUM * IN_NUM
      INTO WK_RTN
      FROM EMP
    ;
    RETURN WK_RTN;
END FN_MULTI_MASTER;

これに対してSELECT文で下記の様な使い方をすると…

ダメ

SELECT FN_MULTI_MASTER(10) AS NUM
  FROM DUAL

計算結果が帰ってきますがこれはダメなやつです。
なぜかと言うと
1. 実行計画のコストが正確に出てこない。
2. レコードの件数だけFUNCTION内部でSELECT文を実行してしまっている。
上記2点に引っかかります。
DUAL文ではなく、複数行あるテーブルに対して上記関数をしかけると
各レコードに対してファンクション内部のSQL文を実行するのでパフォーマンスが悪くなります。

なのでこういうときは外部結合,内部結合等を使って取得するか。
マスタの値を返すファンクションに変更してバインド変数で固めた後使用しましょう。

思いついたら追記していきます。