ストアド プロシージャと戦術的クエリー - Teradata Database - Teradata Vantage NewSQL Engine

ストアド プロシージャを使用した複雑な戦術的更新の実行

戦術的クエリーでの複雑な更新は、逆アセンブルして1つのストアド プロシージャに統合すると、より簡単に処理される場合があります。ストアド プロシージャ制御文の計算を完了しておくことで、SQL文を反復的かつ暫定的に実行できます。これにより、多くの複雑な更新を作成するのに必要なネストされた副クエリーよりも実行内容を明快にできるだけでなく、処理もしやすくなります。

たとえば、次の複雑な更新を実行する場合、ストアド プロシージャでは、2つの単一AMP文を実行します。それぞれの文は1つの行のハッシュ ロックだけに適用されます。

UPDATE orders
SET o_orderpriority = 5
WHERE o_orderkey = 39256
AND EXISTS
  (SELECT * FROM lineitem
   WHERE l_orderkey = o_orderkey);

次の2つのEXPLAIN報告は、逆アセンブルされたSQL文を表わします。これは、以前に記述された複雑な更新を置き換えるためのストアド プロシージャ内に作成できます。

EXPLAIN
SELECT *
FROM lineitem
WHERE l_orderkey = 39256;
Explanation
------------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from CAB.lineitem by
     way of the primary index "CAB.lineitem.L_ORDERKEY = 39256"
     with no residual conditions into Spool 1, which is built locally
     on that AMP.  The input table will not be cached in memory, but it
     is eligible for synchronized scanning.  The size of Spool 1 is
     estimated with high confidence to be 4 rows.  The estimated time
     for this step is 0.15 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.15 seconds.

最初のリクエストから最低でも1つのlineitem行が戻される場合にのみ、適切な条件ロジックを使用して、プロシージャは2番目の文を実行します。

EXPLAIN
UPDATE orders
SET o_orderpriority = 5
WHERE o_orderkey = 39256
Explanation
------------------------------------------------------------------------
  1) First, we do a single-AMP UPDATE from CAB.orders by way of
     the unique primary index "CAB.orders.O_ORDERKEY = 39256"
     with no residual conditions.

これらの2つの文は、トランザクションの境界を指定するBEGIN TRANSACTIONおよびEND TRANSACTIONを使用して、明示的なトランザクション内にコーディングする必要があります。

ストアド プロシージャは、複数行の結果セットを返せないため、テーブルから複数の行を返す必要がある場合には、通常はマクロを使用するやり方のほうが有効です。

不必要なデータベース作業を省くためのストアド プロシージャの使用

2つの検索対象になり得るテーブルのいずれか1つのデータを使用するアプリケーションがありますが、まずアクセスしてみないと2つのテーブルのどちらが必要なのか知ることができないとします。ストアド プロシージャは、問題を解決するのに必要になる可能性のある処理を省くことができます。

分かりやすくするため、部品情報owned_partsおよびsupplied_partsを含む2つのテーブルを持つデータベースを考えてみます。owned_partsが存在する場合にそれを戻し、指定した基本キーの値を持つowned_partsデータがない場合にsupplied_partsだけを戻すというビジネス ルールがあるとします。さらに、部品キーを指定することによって部品データを突き止める戦略的クエリーも行なうとします。

図に示すように、マクロはリクエストごとに両方のテーブルにアクセスしてからデータをアプリケーションに渡し、2つの行が戻されるときにどちらを使用するか判別する必要があります。

問題を解決するストアド プロシージャを作成する場合、この余分な作業は不必要になります。行がowned_partsテーブルで見つかったものかどうかを判別し、そうであれば、supplied_partsテーブルにアクセスせずにアプリケーションに戻すロジックをストアド プロシージャにコーディングできます。次の図は、関係する処理を概観したものです。

セキュリティと監査

不必要なデータベース作業を省くためのストアド プロシージャの使用の同じ部品の例について考え、選択ユーザーだけがsupplied_partsテーブルへのアクセスを許可されているとします。以前のストアド プロシージャを変更することで、セキュリティ テーブルに対してプロシージャを実行依頼したユーザーの権限を検査するロジックをコーディングできます。プロシージャは、次の図に示されているように、supplied_partsへのアクセスが許可される前に権限を検査しますが、ユーザーは自分のアクセスがモニターされていることに気付きません。

特定のユーザーだけがsupplied_partsテーブル内の特定データを表示でき、他のユーザは表示できないことを検証するため、同様の方法を実行できます。

戦術的クエリーのためのストアド プロシージャまたはマクロ

マクロは、単純なリクエスト、複文リクエスト(複数の文が並行して実行される)、および複数の行を返す文に対して、ストアド プロシージャよりも良い選択でした。これは、ほとんどの場合、マクロの方のパフォーマンスが優れていたいからです。現在では、ストアド プロシージャも複文リクエストと結果セット、およびそれらの条件ロジックをサポートしています。このため、戦術的クエリーを実行するために、マクロよりも良い選択になりました。

単純なリクエスト

簡単なリクエストの場合でも、ストアド プロシージャはマクロよりも効率よく実行する場合があります。簡単なリクエストを実行する場合には、マクロとストアド プロシージャのどちらかを使用できます。

複文リクエスト

マクロとストアド プロシージャは、どちらも複文リクエストをサポートしています。ストアド プロシージャの複文リクエストのパフォーマンスは、マクロ以上でない場合、マクロのパフォーマンスと同じになります。

複数の行を返す文

現在ストアド プロシージャは結果セットをサポートしています。これは、ストアド プロシージャが複数の行を返すことができる、ということです。複数の行を返すという点において、マクロにストアド プロシージャを超えるような長所はありません。

マクロとストアド プロシージャの相違

次の表は、マクロとストアド プロシージャの違いを要約しています。