はじめに

RDBMS特にDWHにおけるユーザー定義関数呼び出しオーバーヘッドによる性能劣化についてという非常にいい記事にて、SQLマクロという機能のご紹介もいただいているのですが、検証環境の問題で実例がなかったため、本記事を作成してみました。

SQLマクロとは

元記事にもあるように、Oracle Databaseに限らず、SQL文からストアドファンクションを呼び出すオーバーヘッドは大きいものがあります。SQLマクロは、1文で書けるシンプルなファンクション、もしくはSELECT文を返すファンクションをSQLマクロとしてコンパイルすることで、SQL実行時にファンクションの内容をSQL文に組み込み、ストアドファンクションを使用しない形に変更してから実行するものです。結果的にストアドファンクションを使用していないSQL文が実行されるため、ストアドファンクションを使用している場合に比べて大幅に速度が向上します。一方で、ストアドファンクションを使用した場合のメリットである、処理の共通化と、SQL文の簡素化というメリットはストアドファンクション利用時と同様に得ることが可能です。

SQLマクロはOracle Database 20cの新機能となります。ただし、Oracle CloudのAutonomous Database Cloud Serviceの19cはこの機能が移植されています。また、オンプレスミスの19cでも、19.7以降にアップグレードいただければ、SQLマクロの利用が可能となります。本記事のSQLはAutonomous Database 19c(1oCPU)にて検証していますが、処理時間は筆者の立場上公表できません。とはいえ、素のSQLが元々高速なのもあり、元記事と大差ないと思ってください。

事前準備

元記事から流用できる部分は流用させてもらいつつ、再検証しやすいよう、テーブルの作成やデータ投入のSQLも用意してみました。SQLマクロ利用時のファンクション作成SQLは次節で提示します。

テーブル作成SQL

CREATE TABLE ORDERS (
    ORDER_ID     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    CUSTOMER_AGE NUMBER,
    ORDER_AMT    NUMBER
);

データ投入SQL

INSERT /*+ APPEND */ INTO ORDERS(CUSTOMER_AGE, ORDER_AMT)
WITH SUBQUERY1(ROW_ID, CUSTOMER_AGE, ORDER_AMT) AS (
    SELECT 1,
           TRUNC(DBMS_RANDOM.VALUE(15, 80)),
           TRUNC(DBMS_RANDOM.VALUE(100, 200000))
      FROM DUAL
    UNION ALL
    SELECT ROW_ID + 1,
           TRUNC(DBMS_RANDOM.VALUE(15, 80)),
           TRUNC(DBMS_RANDOM.VALUE(100, 200000))
      FROM SUBQUERY1
     WHERE ROW_ID < 10000000
)
SELECT CUSTOMER_AGE, ORDER_AMT FROM SUBQUERY1
;

サンプルファンクション

CREATE OR REPLACE FUNCTION get_age_group(age NUMBER)
RETURN VARCHAR2
AS
BEGIN
    RETURN CASE WHEN age < 20 THEN '20歳未満'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;
END;
/

ファンクション未使用のSELECT文

SELECT
    CASE WHEN customer_age < 20 THEN '20歳未満'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    CASE WHEN customer_age < 20 THEN '20歳未満'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END;

ファンクション使用時のSELECT文

SELECT
    get_age_group(customer_age) age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    get_age_group(customer_age);

SQLマクロのソースコードの例

CREATE OR REPLACE FUNCTION get_age_group(age NUMBER)
RETURN VARCHAR2 SQL_MACRO(SCALAR)
AS
BEGIN
    RETURN CASE WHEN age < 20 THEN '20歳未満'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;
END;
/

SQLの書き方は、基本的には普通のファンクションと同じで、RETURN句の後に「SQL_MACRO(SCALAR)」と記述するだけです。もちろんパッケージ中のファンクションとして定義することも可能です。検証した限りでは、サンプルソースコードのストアドファンクションを使用したSQL文でも、未使用時のSQLと同等のパフォーマンスで稼働しました。

SQLマクロの使いどころ

シンプルなユーザー定義SQL関数を高速に実行する
他のRDBMSのSQL関数の移植性を高める

特に2.に関して、例えばOracle Databaseで文字列の部分抽出を行うSUBSTR関数は、他のRDBMSだとSUBSTRINGという名称である場合があります。SUBSTRING関数を使用しているSQL文はOracle Databaseではそのままでは動きませんが、SUBSTRINGという名称のSQLマクロを作成することで、処理速度を損なうことなくSUBSTRING関数のままSQL文を実行することが可能になります。結果、移植難易度が低下します。

SELECT文のSQLマクロ

以下のように、SELECT文自体を返すSQLマクロを作成することで、SQL文短縮とSELECT文の再利用性向上の利点が得られます。付加する句は「SQL_MACRO(TABLE)」となります。なお、「SQL_MACRO()」と指定した場合のデフォルトは「SQL_MACRO(TABLE)」となります。

CREATE OR REPLACE FUNCTION sample
RETURN VARCHAR2 SQL_MACRO(TABLE)
AS
BEGIN
    RETURN q'{SELECT
            CASE WHEN customer_age < 20 THEN '20歳未満'
                WHEN customer_age < 30 THEN '20代'
                WHEN customer_age < 40 THEN '30代'
                WHEN customer_age < 50 THEN '40代'
                WHEN customer_age < 60 THEN '50代'
                ELSE '60歳以上' END age_group,
            sum(order_amt)
        FROM
            orders
        GROUP BY
            CASE WHEN customer_age < 20 THEN '20歳未満'
                WHEN customer_age < 30 THEN '20代'
                WHEN customer_age < 40 THEN '30代'
                WHEN customer_age < 50 THEN '40代'
                WHEN customer_age < 60 THEN '50代'
                ELSE '60歳以上' END}';
END;
/

SELECT文のSQLマクロを実行する場合は、PL/SQL表関数と同様、FROM句にてファンクション名を指定します。

select * from sample();

参考サイト

Oracle 20c new SQL features

修正履歴

2020/9/30 : SQLマクロが19.7以降で利用できる点を追記

Oracle DatabaseのSQLマクロを検証する