Product Information
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つのオプションがあります。
FOR UPDATE BY LOCK
. を指定します。これは、アプリケーションでFETCH
されているので、各ローで INTENT ローロックの取得が発生します。INTENT ロックは、他の接続がローを読むことを許可しますが、他の接続はそれの INTENT または WRITE ロックを取得することはできません。INTENT ロックは、COMMIT/ROLLBACK
まで保たれる長期のロックです。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
オプション
===
SAP SQL Anywhere に関する詳細情報は、SAP SQL Anywhere Communityページ<英語> を参照してください。
上記のコミュニティーに掲載されている技術情報は、順次SAP SQL Anywhere 日本語コミュニティに掲載しています。
SAP SQL Anywhere に関してはまずはこちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。
SAP SQL Anywhere に関して技術的な質問のある方はコミュニティに登録し、
「Ask a Question」機能をご利用ください。
Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」、
User Tagに「sql anywhere」、「sql anywhere japanese question」
を選択してください。
不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。
======================
ご購入に関するお問い合わせ
こちらよりお問い合わせください。