SAP SQL Anywhere におけるヒントを使用した SQL リクエストのカスタマイズ – パート 1（過去のブログより）

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。

https://blogs.sap.com/2014/03/26/customizing-sql-requests-with-hints/

この記事のオリジナルは、 Glenn Paulley がsybase.com に 2009 年 6 月に掲載したものです。その中で、Glenn は SQL Anywhere におけるヒントの使用によるクエリセマンティクスのカスタマイズについて解説しています。

特定の状況においては、ヒントは非常に便利になりえますが、ほとんどの場合、SQL Anywhere のクエリのオプティマイゼーションとクエリの実行においては、クエリとDMLの性能の実行を実現するために全てのコンテキストを考慮して「正しいこと」をする、というとてつもない仕事を行っているということに留意することが重要です。

SQL クエリの正確なセマンティクスに影響を与えるために使用できるメカニズム、特に、特定の SQL 文の結果への同時トランザクションの影響を隔離する（あるいは晒す）ためのものはたくさんあります。このようなメカニズムの 1 つが、使用されるカーソルのタイプです。

例を挙げると、INSENSITIVE カーソルは、同時アップデートの影響からクエリを隔離し、同じトランザクションからであっても、その結果の最初の FETCH より前に、OPEN 時の SQL クエリの全結果セットを実体化します。

一方、SQL Anywhere の KEYSET-DRIVEN (または SCROLL・value-sensitive) カーソルは、それらのローがアプリケーションによってFETCHされたものであると記憶し、同じ結果のローが再FETCH され、同時アップデート（または削除）される場合には、アプリケーションに警告（エラー）を返します。

他のメカニズムとして、SQL 文で使用されている分離レベルがあります。

SERIALIZABLEより低い ANSI SQL 分離レベルには、明らかに同時実行性を改善する利点があります。

しかしながら、同時更新やそれら間の相互作用が理由で、クエリ実行中にエラーが発生するリスクがあります。

他のセマンティックの効果のうち、テーブルヒントの使用は、クエリあるいはテーブルベースでさえセマンティックの変更の指定が可能です。

もともと、私は SQL クエリのヒントの使用は好きではありません。なぜならば、「クエリオプティマイザーに最適なアクセスプランを選択させなければならない」という強い偏見があるからです。

しかしながら、多くの場合において、クエリまたはテーブルヒントは、特にロックの動作を細かく制御するのに、非常に便利です。

SELECT FOR UPDATE

テーブルヒントの話をする前に、FOR UPDATE 構文で指定された文レベルの 2 つの同時実行性制御ヒントについて述べたいと思います。

FOR UPDATE句の基本は、明示的に更新可能なカーソルを宣言します。しかしながら、分離レベル0 と 1 のロックでは、現在の行以外ロックは取得されていないので、その他のローは他の接続による修正や削除がオープンに行えます。

これらのローのその後の更新の可否を確認するには、2つのオプションがあります。

1. FOR UPDATE BY LOCK. を指定します。これは、アプリケーションでFETCHされているので、各ローで INTENT ローロックの取得が発生します。INTENT ロックは、他の接続がローを読むことを許可しますが、他の接続はそれの INTENT または WRITE ロックを取得することはできません。INTENT ロックは、COMMIT/ROLLBACKまで保たれる長期のロックです。
2. FOR UPDATE BY TIMESTAMP または FOR UPDATE BY VALUES。を指定します。この場合、SQL Anywhereは、他の接続によって特定のローが替えられた、または削除されたという通知を有効化するため、楽観的同時実行制御の形式としてKEYSET-DRIVEN カーソルの使用を強要します。

テーブルヒント

Microsoft SQL Serverのような他のシステムと同様に、SQL Anywhere では追加の WITH 句を使うことでテーブルヒントをサポートしています。以下に demo.db サンプルデータベースを使用した例を挙げます。

SELECT *
 FROM CUSTOMERS WITH ( NOLOCK )

NOLOCKテーブルヒントは、サーバーが Customers テーブルに分離レベル0でアクセスさせます。テーブルヒントは、ベースまたはグローバル共有 temp テーブルにのみ適用されることに留意してください。ビューまたはプロキシテーブルで使用される場合、ヒントは無視されます。以下が SQL Anywhere 11.0.1 サーバーでサポートされているテーブルヒントのリストです。

• NOLOCK</code– 分離レベル 0 (READ ロッキングなし) を使用してください。Microsoft SQL Serverと互換です。
• READUNCOMMITTED – NOLOCK – NOLOCKと同義です。
• READCOMMITTED – 分離レベル 1で短期のread ロックを使用します。
• REPEATABLEREAD – 分離レベル2でreadロックを使用します。
• SERIALIZABLE – 分離レベル 3 でread ロックを使用します。
• HOLDLOCK – SERIALIZABLE</codeと同義です。Microsoft SQL Serverや、SAP Adaptive Server Enterprise でもサポートされています。
• READPAST. READPAST テーブルヒントは、SELECT 文 (のみ)分離レベル 1との使用においてサポートされています。READPAST は、インデックススキャンであっても、テーブルスキャンであっても、INTENT または WRITE ロックでロックされたローへ単純に「ジャンプ」することでスキャン時のブロックを避けます。この意味において、READPAST は、シンプルにコミットされていない更新を計算から除くことでセマンティクスが安全でないことを示します。しかしながら、このREADPASTの重要な長所は、ブロックが原因で同時条件を避けてるにも関わらず、これがベーステーブル内のキューまたはキーのプールのメンテナンスに非常に便利だということです。 READPASTはまた、Microsoft SQL Server や SAP Adaptive Server Enterprise でもサポートされています。
• UPDLOCK – INTENT ロックをスキャンのそれぞれのローに適用します。
• XLOCK – WRITE ロックをスキャンのそれぞれのローに適用し、分離レベル0のトランザクションを除いて他の接続がローにアクセスするのを禁じます。

最後に、テーブルヒントFASTFIRSTROW は、SQL Anywhere のオプティマイザーに対し、テーブル参照を含むSELECTブロックにFIRST-ROW の最適化ゴールを使用させます。

どのテーブルがヒントを受け取るかは関係ないことに留意してください。つまり、FASTFIRSTROWヒントに付随するSELECTブロックのあらゆるテーブル参照がゴールを変更します。

OPTION 句

SQL Anywhere 10.0.1 より、データ操作言語 (DML) 文(SELECT, UPDATE, etc) は OPTION 句をサポートしています。OPTION 句の役に立つ特徴として、アプリケーション開発者側で最適化バイパスを無効にし、コストベースのオプティマイゼーションを強要できることがあります。さらに、この文単体で、これらのオプションの設定を個別にSET OPTION文で替える必要を避け、ISOLATION LEVEL または OPTIMIZATION_GOALのような特定の接続オプションを設定することができます。

例えば SELECT * FROM Customers OPTION (OPTIMIZATION_LEVEL = 2, ISOLATION_LEVEL = 2 )

現時点では、以下の接続オプションがOPTION句のクエリの中で設定できます。

• ISOLATION_LEVELオプション
• MAX_QUERY_TASKSオプション
• OPTIMIZATION_GOALオプション
• OPTIMIZATION_LEVELオプション
• OPTIMIZATION_WORKLOADオプション
• USER_ESTIMATESオプション