メインコンテンツへスキップ

TDVにおけるOracle SQL関数の扱いについて

  • 更新

    概要

    TDVのデータソースとしてOracleデータベースのメタデータをインポートしてビューを作成していくにあたり、ビューに記述するTDV SQLが最終的にOracleに対して実行されるクエリにおいてどのように反映されるかを以下の２つのパターンで説明します。

    1. TDV SQL準拠の関数がそのままOracleに対するクエリにも反映される場合
    2. TDV SQL準拠の関数がOracleに対するクエリに反映されない場合

     

     

    検証環境

    製品 バージョン 実行環境
    TDV 8.5.1 Windows Server 2019
    Oracle 19c Amazon RDS

     

     

    TDV SQL

    TDV StudioでビューやSQLスクリプトの開発時に使用するSQLをTDV SQLと呼びます。
    TDV SQLは、ANSISQL-92およびANSISQL-99のサブセットをサポートします。

     

     

    1. TDV SQL準拠の関数がそのままOracleに対するクエリにも反映される場合

    ここでは、以下の2種類の関数について、TDVでどのように処理されるかを説明します。

    • LPAD / RPAD 関数
    • DECODE 関数

     

    LPAD / RPAD 関数

    以下の図にように、/shared/oracle-19c/application/views/ordersビューに LPAD / RPAD 関数を利用した文字列処理をSQLとして記述していますが、「結果」タブに表示されているとおり、SQLに記述したとおりの結果が返って来ていることがわかります。

    oracle_func04.png

    ビューに記述したクエリがデータソース（Oracle）では実際にどのように処理されたかを「実行計画」にて確認します。

    以下の図が示すとおり、LPAD / RPAD 関数は、TDVからOracleに対して実行されるクエリに反映されており、LPAD / RPAD の処理は、TDVのリソースを使用することなく、Oracle側にて実行されていることがわかります。

    oracle_func05.png

     

    DECODE 関数

    以下の図にように、/shared/oracle-19c/physical/formatting/customersビューにDECODE 関数を利用した計算カラム追加処理をSQLとして記述していますが、「結果」タブに表示されているとおり、SQLに記述したとおりの結果が返って来ていることがわかります。

    oracle_func06.png

    ビューに記述したクエリがデータソース（Oracle）では実際にどのように処理されたかを「実行計画」にて確認します。

    以下の図が示すとおり、DECODE 関数は、TDVからOracleに対して実行されるクエリに反映されており、DECODE の処理は、TDVのリソースを使用することなく、Oracle側にて実行されていることがわかります。

    oracle_func07.png

     

     

    2. TDV SQL準拠の関数がOracleに対するクエリに反映されない場合

    TDVのビューに記述したSQL関数がデータソース側に反映されず、その処理がTDVのリソースを使用して実行されることがあります。

    以下の図の例では、/shared/oracle-19c/application/views/ordersにSPLIT_PART 関数を利用していますが、最終的にTDVからクエリが実行されるOracleデータソース側がSPLIT_PART 関数をサポートしていないために、Oracleに対して実行されるクエリ（図中のFETCHブロックの１つ）にはSPLIT_PARTは反映されず、TDV側のリソース（図中の選択されているSELECTブロック）でSPLIT_PARTが実行されています。

    oracle_func02.png

    このままでもビューの実行は問題なく成功しますが、SPLIT_PART に相当する処理もOracle側で実行（＝Oracleに対して実行されるクエリに反映）することができれば、TDVへの負荷をより低減させることができます。

    このような場合には、例えば、以下のようにビュー中のクエリを変更することで対応することができます。

    変更前：

    SPLIT_PART(customers.NAME, ' ', 1) 姓, 
    SPLIT_PART(customers.NAME, ' ', 2) 名,

    変更後：

    SUBSTR(customers.NAME, 1, INSTR(customers.NAME, ' ', 1, 1) - 1) 姓, 
    SUBSTR(customers.NAME, INSTR(customers.NAME, ' ', 1, 1) + 1, LENGTH(customers.NAME)) 名,

    変更後のクエリでは、以下の図のような結果になります。

    実行計画が改善されており、SPLIT_PARTに相当するクエリとして記述した SUBSTR、INSTR、LENGTH を利用したクエリがOracleに対して実行されるクエリにも反映されていることがわかります。

    oracle_func03.png

     

     

    まとめ

    これまでに述べたように、TDV SQLで記述したクエリに含まれる関数をOracleがサポートしていればクエリの内容がすべてOracleにも反映され、実行計画が最適化されます。

    しかし、クエリの内容によってはデータソースに対して実行されるクエリに反映されない場合があり、レコード件数の少ない軽いデータを処理するビューであればあまり問題になることはありませんが、大量レコードを処理するビューであればデータサービスのパフォーマンス低下につながる可能性があります。

    このような場合には、クエリを一部変更するなどの対応によって解決することができます。