17.10 - SQLプロシージャのLOBの操作に関する指針 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 詳細トピック

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1184-171K-JPN
Language
日本語 (日本)
LOBにアクセスするSQLプロシージャを作成する際には常に注意が必要です。これは、次の理由によります。
  • プロシージャはPEで実行されますが、LOBはAMPに保管されます。
  • プロシージャは、AMP上に存在するLOBへの参照を保管します。
  • LOBへのアクセスが必要なプロシージャのすべての操作では、プロシージャが実行されているPEにそのLOB全体を転送しなければなりません。
次の例は、XMLの販売データを含むCLOBを、販売テーブルに挿入される行のセットに変換します。プロシージャはさらに次の処理も行ないます。
  • ログ テーブルにCLOBを保管します。
  • ローカル変数にCLOBの半分を保管します。
  • 別のログ テーブルにCLOBの半分を保管します。
        CREATE TABLE sales (
          partnum   INTEGER, 
          qtysold   INTEGER,
          storecode INTEGER,
          salesdate DATE)
        PRIMARY INDEX (partnum, qtysold, storecode, salesdate);
    
        CREATE TABLE SalesLog (
          storecode INTEGER, 
          salesdate DATE,
          sales     CLOB,
          logdate   DATE,
          logtime   TIME)
        PRIMARY INDEX (storecode, salesdate, logdate, logtime);
    
        CREATE TABLE saleshalflog (
          storecode INTEGER, 
          salesdate DATE,
          sales     CLOB,
          logdate   DATE,
          logtime   TIME)
        PRIMARY INDEX (storecode, salesdate, logdate, logtime);
    
        CREATE PROCEDURE storessalesprocedure (
          storecode INTEGER,
          salesdate DATE,
          salesclob CLOB)
            BEGIN
             DECLARE localclob CLOB;
             SET localclob = SUBSTR(:salesclob,1,CHARACTERS(:salesclob)/2 );
             INSERT saleslog (:storecode, :salesdate, :salesclob,
                              CURRENT_DATE, CURRENT_TIME );
             INSERT saleshalflog (:storecode, :salesdate, :localclob,
                                  CURRENT_DATE, CURRENT_TIME );
             INSERT sales SELECT * FROM TABLE (xmlparser(:salesclob));
            END;

次のように、1 MBのCLOBを渡すことによってプロシージャが呼び出されるとします。

    USING (
      storecode INTEGER,
      salesdate DATE,
      salesclob CLOB AS DEFERRED)
     CALL storessalesprocedure (:storecode, :salesdate, :salesclob);

ここで次のプロセスが発生します。

  1. CLOBがクライアント アプリケーションからAMPに転送されます。
  2. プロシージャが1 MBのCLOBおよびその他のデータへの参照によって呼び出されます。
  3. プロシージャは、1 MBのCLOBのサブストリングを取ることによって、localclobと呼ばれる新しいCLOBを構築します。
  4. 1 MBのCLOBがすべてPEに転送されます。
  5. LocalClobで作成するために、0.5 MBのCLOBがAMPに転送されます。
  6. LocalClobへの参照がPEに保管されます。
  7. SalesLogテーブルへのINSERTが、1 MBのCLOBへの参照とともに構文解析プログラムに送信されます。
  8. 構文解析プログラムは、1 MBのCLOBへの参照を含むINSERTステップをAMPに送信します。
  9. AMPは、1 MBのCLOBが保管されているAMPでそれを検索し、SalesLogテーブルに挿入します。
  10. SalesHalfLogテーブルへのINSERTが、0.5 MBのCLOBへの参照とともに構文解析プログラムに送信されます。
  11. 構文解析プログラムは、0.5 MBのCLOBへの参照を含むINSERTステップをAMPに送信します。
  12. AMPは、0.5 MBのCLOBが保管されているAMPでそれを取得し、SalesHalfLogテーブルに挿入します。
  13. SalesテーブルへのINSERTが、1 MBのCLOBへの参照とともに構文解析プログラムに送信されます。
  14. 構文解析プログラムは、1MBのCLOBへの参照を含むINSERT ... SELECTステップをすべてのAMPに送信します。
  15. 1 MBのCLOBを含むAMPは、それを他のAMPにハッシュ再配布される行に変換します。
  16. それらの行がSalesテーブルに挿入されます。
LocalClobの使用は必須ではありません。ローカル変数を使用せずに、次のようにINSERT文にサブストリングの式を置くと、さらに効率的です。
    INSERT INTO saleshalflog VALUES (:storecode, :salesdate, SUBSTR(
                             :salesclob, 1, CHARACTERS(:salesclob)/2),
                             CURRENT_DATE, CURRENT_TIME);

SQL文にLOB関数を置けない場合は、LOBデータ型をVARBYTEまたはVARCHARに変換することを検討してください。どちらを変換するにしてもLOB全体をPEに転送できますが、小さなLOBの場合、これはオプションです。

プロシージャ内部でのSELECT操作の場合、FOR構文を使用することによってLOBへの不要な転送を避けられます。次の例を考慮してみましょう。これは、LOBを選択し、それを別のテーブルに挿入するプロシージャの例です。

    CREATE TABLE sales (
      partnum   INTEGER, 
      qtysold   INTEGER,
      storecode INTEGER,
      salesdate DATE)
    PRIMARY INDEX (partnum, qtysold, storecode, salesdate);

    CREATE TABLE saleslog (
      storecode INTEGER, 
      salesdate DATE,
      sales     CLOB,
      logdate   DATE,
      logtime   TIME)
    PRIMARY INDEX (storecode, salesdate, logdate, logtime);

    CREATE PROCEDURE stores_sales_log_procedure
      (storecode INTEGER, salesdate DATE )
      BEGIN
          FOR cur AS curname CURSOR FOR
            SELECT * 
            FROM saleslog
            WHERE storecode = :storecode 
            AND   salesdate = :salesdate;
           DO
            IF (:cur.lobdate = DATE-1) THEN
               INSERT sales 
               SELECT *
               FROM TABLE (xml_sales_parser(:cur.sales));
            END IF;
          END FOR;
      END;

プロシージャstores_sales_log_procedureは、次のプロセスを実行します。

  1. :cur.salesに、販売CLOBへの参照を保管します。
  2. salesテーブル リクエストにINSERTリクエストを使用して、この参照を構文解析プログラムと、AMPに送られたINSERT ... SELECTステップに送信します。
  3. :cur.salesがローカル変数にコピーされると、CLOBはAMPからPEへ転送されます。

プロシージャのSELECT文のSELECT INTOスタイルおよびFETCH INTOスタイルはローカル変数に対してコピーを作成しますが、これによってAMPとPEの間でのLOBの余分な転送が発生します。

以下に、LOBデータの処理において効率的なプロシージャを作成しない例を示します。

    CREATE TABLE sales (
      partnum   INTEGER, 
      qtysold   INTEGER,
      storecode INTEGER,
      salesdate DATE)
    PRIMARY INDEX (partnum, qtysold, storecode, salesdate);

    CREATE TABLE saleslog (
      storecode INTEGER, 
      salesdate DATE,
      sales     CLOB,
      logdate   DATE,
      logtime   TIME)
    PRIMARY INDEX (storecode, salesdate, logdate, logtime);

    CREATE PROCEDURE stores_sales_stream_proc
     (storecode INTEGER, salesdate DATE, salesclob CLOB)
      BEGIN
          DECLARE resultrownum INTEGER;
          DECLARE partnum      INTEGER;
          DECLARE qtysold      INTEGER;
          INSERT INTO SalesLog VALUES (:storecode, :salesdate, :sales,
                                       CURRENT_DATE, CURRENT_TIME );
        CALL xmlsalesparser(:resultrownum,:partnum,:qtysold,:salesclob);
        WHILE (resultrownum> 0 )
        DO
          INSERT INTO Sales VALUES (:partnum, :qtysold, :storecode,
                                    :salesdate);
        CALL xmlsalesparser(:resultrownum,:partnum,:qtysold,:salesclob);
        END WHILE;
      END;