Skip to main content

クエリでの最適化ヒントの指定

既定で、InterSystems SQL クエリ・オプティマイザは、高度で柔軟性の高いアルゴリズムを使用して、複数のインデックスを含む複雑なクエリのパフォーマンスを最適化します。大半の場合、このような既定の設定によって最適なパフォーマンスが得られます。一方、InterSystems SQL には、実行プランの手動変更に使用できるヒントが用意されています。ほとんどの場合、インターシステムズのサポート窓口の助言に従い、これらのヒントを使用して最適なクエリ実行プランを構成します。SQL のパフォーマンスについてサポート窓口の助言を得るには "レポート生成" を参照してください。

このようなヒントをクエリ・オプティマイザに提供して、特定の最適化を採用し、他を除外することを指定するには 2 つの方法があります。その 1 つは SELECT 文の FROM 節にキーワードを使用する方法で、もう 1 つはコマンド・オプションを指定する方法です。

FROM 節のキーワード

SELECT 文には FROM 節を使用できますが、特定のクエリ最適化動作を指定するキーワードをその節に記述できます。複数のキーワードを、空白で区切って任意の順序で指定できます。

%ALLINDEX

このオプションのキーワードを指定すると、何らかの利点をもたらすインデックスはすべて、クエリ結合順の最初のテーブルに使用されます。このキーワードは、定義された複数のインデックスがある場合にのみ使用する必要があります。オプティマイザの既定では、オプティマイザが最も有用であると判断したインデックスのみが使用されます。既定では、これには、有用なすべての等価インデックス、および選択したその他のタイプのインデックスが含まれます。%ALLINDEX では、あらゆるタイプの有用と思われるインデックスが使用されます。すべてのインデックスをテストするとオーバーヘッドが増加しますが、状況によっては、既定の最適化よりもパフォーマンスが向上する場合があります。このオプションは、複数の値域条件インデックスおよび非効率的な等価条件インデックスを使用する場合に、特に有用です。このような状況では、正確なインデックスの選択性がクエリ・オプティマイザで使用できない可能性があります。%ALLINDEX は、%IGNOREINDEX と併用することで、特定のインデックスの組み込みと除外を行うことができます。通常、%ALLINDEX は TOP 節クエリと共に使用しないでください。

%STARTTABLE を %ALLINDEX と組み合わせて使用すると、%ALLINDEX が適用されるテーブルを指定できます。

特定の条件で条件レベル・ヒント %NOINDEX を使用して、%ALLINDEX に対する例外を指定することができます。%NOINDEX ヒントは、インデックスを使用しない各クエリ選択条件の前に配置します。例えば、WHERE %NOINDEX hiredate < ? のようにします。通常、この方法は条件によって圧倒的多数のデータが除外されない場合に使用します。「より小さい (<)」または「より大きい (>)」条件では、通常、条件レベル・ヒント %NOINDEX を使用すると効果的です。等価条件では、条件レベル・ヒント %NOINDEX を使用しても効果はありません。結合条件では、%NOINDEX は、ON 節の結合でサポートされます。詳細は、"SQL 最適化ガイド" の “クエリ処理でのインデックスの使用” を参照してください。

%FIRSTTABLE

%FIRSTTABLE tablename

このオプション・キーワードを指定すると、クエリ・オプティマイザは指定された tablename から結合を開始します。tablename は、結合シーケンスで後に指定されるテーブルに名前を付けます。残りのテーブルの結合順序は、クエリ・オプティマイザに委任されます。このヒントは機能的には %STARTTABLE と同じですが、結合テーブル・シーケンスを任意の順序で指定できます。

tablename は、テーブル・エイリアスか未修飾のテーブル名のどちらかの単純な識別子でなくてはなりません。修飾されたテーブル名 (schema.table) は使用できません。クエリでテーブル・エイリアスを指定する場合は、テーブル・エイリアスを tablename として使用する必要があります。以下に例を示します。

FROM %FIRSTTABLE P Sample.Employee AS E JOIN Sample.Person AS P ON E.Name = P.Name

%FIRSTTABLE と %STARTTABLE の両方とも、結合操作に使用する最初のテーブルを指定できます。%INORDER を使用すると、結合操作に使用するすべてのテーブルの順序を指定できます。これらの 3 つのキーワードは相互排他的です。1 つだけ指定してください。これらのキーワードが使用されない場合、クエリ・オプティマイザは、テーブルがリストされている順番に関係なく、最適と考えられる順序でテーブルの結合を実行します。

%FIRSTTABLE または %STARTTABLE を使用して、LEFT OUTER JOIN の右側 (または RIGHT OUTER JOIN の左側) で結合順序を開始することはできません。これを実行しようとすると、SQLCODE -34 エラーである "オプティマイザが使用可能な結合順序を見つけることができませんでした”" 生成されます。

詳細は、%STARTTABLE クエリ最適化オプションを参照してください。

%FULL

このオプション・キーワードにより、コンパイラ・オプティマイザは、すべての代替結合シーケンスを検査し、アクセス・パフォーマンスを最大限に引き出します。例えば、ストアド・プロシージャを生成する場合、コンパイル時間が長くなると最適なアクセス・パフォーマンスを引き出す場合があります。FROM 節にテーブル名が多数ある場合、既定では可能性の小さな結合シーケンスは検証しないように最適化されます。%FULL は、この既定の動作をオーバーライドします。

矢印構文でアクセスされるテーブルが FROM 節にある場合は、%INORDER キーワードと %FULL キーワードの両方を指定できます。それにより、テーブルの順序に制約がなくなります。

%IGNOREINDEX

このオプション・キーワードを指定すると、クエリ・オプティマイザは指定されたインデックス、またはインデックスのリストを無視します (同義の %IGNOREINDICES は非推奨ですが、下位互換性保持のためにサポートされます)。

このキーワードに続いて、1 つ以上のインデックス名を指定します。複数のインデックス名はコンマで区切る必要があります。無視されるインデックスは、以下のような方法で指定できます。

  • %IGNOREINDEX * — FROM 節に指定されたテーブルに対するクエリによって使用されるすべてのインデックスが無視されます。

  • %IGNOREINDEX SchemaName.* — SchemaName 内のすべてのインデックスが無視されます。

  • %IGNOREINDEX SchemaName.TableName.* — 特定のテーブル内のすべてのインデックスが無視されます。

  • %IGNOREINDEX SchemaName.TableNameIndexName — テーブル内の特定のインデックスが無視されます。

以下のクエリ例では、クエリで使用されるはずのすべてのインデックスが無視されます。

SELECT ID FROM %IGNOREINDEX * Opus.Fact WHERE Date > '2023-01-22'

この最適化制約を使用すると、特定のクエリに対して最適ではないインデックスを使用しないよう、クエリ・オプティマイザに指定することができます。1 つを除くすべてのインデックス名を指定することで、事実上、クエリ・オプティマイザで残りのインデックスを使用するよう強制できます。

また、最初に %NOINDEX キーワードで条件を記述することで、特定の条件式で特定のインデックスを無視することができます。詳細は、"SQL 最適化ガイド" の “クエリ処理でのインデックスの使用” を参照してください。

%INORDER

このオプション・キーワードを指定すると、クエリ・オプティマイザは FROM 節にリストされるテーブルの順番で結合を実行します。コンパイル時間を最小限にします。矢印構文で参照されるテーブルの結合順には制限はありません (矢印構文の使用法の詳細は、"InterSystems SQL の使用法" の "暗黙結合" を参照してください)。サブクエリを平坦化しても、インデックスを使用しても、影響を受けることはありません。

%INORDER は、CROSS JOIN または RIGHT OUTER JOIN で使用することはできません。指定されたテーブル順が外部結合に必要な順番と一致しない場合、SQLCODE -34 エラーである "オプティマイザが使用可能な結合順序を見つけることができませんでした" が生成されます。これを避けるために、外部結合で使用する場合は、%INORDER のみを ANSI 形式の左外部結合または FULL 外部結合で使用することをお勧めします。

シャード・テーブルに対してクエリを実行するときに %INORDER を使用することはできません。"スケーラビリティ・ガイド" の “シャーディングによるデータ量に応じた InterSystems IRIS の水平方向の拡張” の章にある "シャード・クラスタにおけるクエリ" を参照してください。

ビューとテーブル・サブクエリは、FROM 節で指定された順番で処理されます。

  • Streamed View ： %INORDER は、ビュー内のテーブル処理順に影響を与えません。

  • Merged View ： %INORDER は、ビューの参照時にビューのテーブルをビューの FROM 節順に処理します。

このキーワードと %FIRSTTABLE および %STARTTABLE を比べた場合、2 つは完全な結合順序でなく最初の結合テーブルのみを指定する点が異なります。さまざまな結合順序の最適化によるテーブルのマージの動作については、"%STARTTABLE" を参照してください。

%INORDER の最適化と %PARALLEL の最適化は同時に使用できません。両方を指定すると、%PARALLEL は無視されます。

%NOFLATTEN

このオプションのキーワードは、ブーリアン値を返すサブクエリである修飾付きサブクエリの FROM 節で指定します。これにより、コンパイラ・オプティマイザでサブクエリの平坦化を抑制する必要があることを指定します。この最適化オプションは、サブクエリをクエリに効果的に統合して修飾付きサブクエリを含むクエリを最適化する “平坦化” (既定) を無効にします。この平坦化では、サブクエリのテーブルをクエリの FROM 節に追加し、サブクエリの条件をクエリの WHERE 節の結合または制限に変換します。

以下に、%NOFLATTEN を使用する修飾付きサブクエリの例を示します。

SELECT Name,Home_Zip FROM Sample.Person WHERE Home_Zip IN 
      (SELECT Office_Zip FROM %NOFLATTEN Sample.Employee)
SELECT Name,(SELECT Name FROM Sample.Company WHERE EXISTS
             (SELECT * FROM %NOFLATTEN Sample.Company WHERE Revenue > 500000000))
 FROM Sample.Person

%INORDER および %STARTTABLE の最適化では、暗黙的に %NOFLATTEN が指定されます。

%NOMERGE

このオプションのキーワードは、サブクエリの FROM 節で指定します。これにより、コンパイラ・オプティマイザではサブクエリをビューに変換しないことを指定します。この最適化オプションは、サブクエリを含むクエリの最適化を無効にします。この最適化では、サブクエリをインライン・ビューとしてクエリの FROM 節に追加し、クエリのフィールドに対するサブクエリの比較が、結合としてクエリの WHERE 節に移動します。

%NOREDUCE

このオプションのキーワードは、ストリーム化されたサブクエリの FROM 節で指定します。ここのサブクエリとは、行の結果セットを返すサブクエリ、つまりクエリに含まれる FROM 節内のサブクエリです。これは、コンパイラ・オプティマイザではサブクエリ (またはビュー) とそれを含むクエリとの結合を抑制する必要があるということを指定します。

以下の例では、クエリ・オプティマイザは、通常、Sample.Person とサブクエリとのデカルト積結合を実行することで、このクエリを “削減” します。%NOREDUCE 最適化オプションは、これを防止します。InterSystems IRIS は、代わりに gname に一時インデックスを構築し、この一時インデックスに結合を実行します。

SELECT * FROM Sample.Person AS p, 
   (SELECT Name||'goo' AS gname FROM %NOREDUCE Sample.Employee) AS e
    WHERE p.name||'goo' = e.gname

%NOSVSO

このオプションのキーワードは、ブーリアン値を返すサブクエリである修飾付きサブクエリの FROM 節で指定します。これにより、コンパイラ・オプティマイザで集合値サブクエリの最適化 (SVSO) を抑制する必要があることを指定します。

ほとんどの場合、集合値サブクエリの最適化によって [NOT] EXISTS および [NOT] IN サブクエリのパフォーマンスが向上し、特に、1 つだけの分離可能な相関条件が指定されたサブクエリのパフォーマンスが向上します。SVSO はこのために、その条件を満たすデータ値を一時インデックスに移入します。そのサブクエリを繰り返し実行する代わりに、InterSystems IRIS はこれらの値を一時インデックス内で検索します。例えば、SVSO は、P.num の一時インデックスを作成することで、NOT EXISTS (SELECT P.num FROM Products P WHERE S.num=P.num AND P.color='Pink') を最適化します。

SVSO は、ALL または ANY キーワードが比較演算子 (>、>=、<、または <=) およびサブクエリと共に使用されているサブクエリ (例 ： ...WHERE S.num > ALL (SELECT P.num ...)) を最適化します。これを行うには、サブクエリ式 sqbExpr (この例では P.num) を、適宜 MIN(sqbExpr) または MAX(sqbExpr) で置換します。これにより、sqbExpr に対するインデックスが存在する場合に高速の計算が実現されます。

%INORDER および %STARTTABLE の最適化では、集合値サブクエリの最適化が抑制されません。

%NOTOPOPT

このオプションのキーワードは、TOP 節を ORDER BY 節と共に使用する場合に指定します。既定では、TOP を ORDER BY と共に使用すると、最初の行に移動する時間が最速になるように最適化されます。%NOTOPOPT (TOP 最適化でない) を指定することでクエリが最適化され、完全な結果セットを最速で取得できるようになります。

%NOUNIONOROPT

このオプションのキーワードは、クエリまたはサブクエリの FROM 節で指定します。これにより、複数の OR 条件および UNION クエリ式に対するサブクエリに実行される自動最適化が無効化されます。これらの自動最適化では、適切な場合には、複数の OR 条件が UNION サブクエリに変換されたり、UNION サブクエリが OR 条件に変換されたりします。これらの UNION/OR 変換により、EXISTS やその他の下位の述語を InterSystems IRIS クエリ・オプティマイザ・インデックスで使用できる最上位の条件に移行できます。これらの既定の変換は、ほとんどの状況に適しています。

ただし、状況によってはこれらの UNION/OR 変換によって大幅なオーバーヘッドが生じることがあります。%NOUNIONOROPT を指定すると、この FROM 節に関連付けられた WHERE 節内のすべての条件に対して自動の UNION/OR 変換が無効になります。そのため、複雑なクエリでは、この自動 UNION/OR 最適化を 1 つのサブクエリにのみ無効にしてその他のサブクエリには有効にすることができます。

UNION %PARALLEL キーワードは、自動の UNION から OR への最適化を無効にします。

%INORDER および %STARTTABLE の最適化では、OR から UNION への最適化が抑制されますが、UNION から OR への最適化は抑制されません。

%PARALLEL

このオプションのキーワードは、クエリの FROM 節で指定します。これは、InterSystems IRIS が複数のプロセッサを使用してクエリの並列処理を実行することを示しています (該当する場合)。これにより、1 つ以上の COUNT、SUM、AVG、MAX、または MIN 集約関数または GROUP BY 節 (あるいはその両方) を使用するクエリや、他の多くのタイプのクエリで、パフォーマンスを大幅に向上させることができます。一般にこれらは、大量のデータを処理し、小規模な結果セットを返すクエリです。例えば、SELECT AVG(SaleAmt) FROM %PARALLEL User.AllSales GROUP BY Region は、並列処理を使用する可能性が高くなります。

個々のフィールドと集約関数の両方を指定しているが GROUP BY 節を含まないクエリは、並列処理を実行できません。例えば、SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person は並列処理を実行しませんが、SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person GROUP BY Home_State は並列処理を実行します。

%PARALLEL は、SELECT クエリとそのサブクエリで使用するためのものです。INSERT コマンド・サブクエリは %PARALLEL を使用できません。

%PARALLEL を指定すると、クエリによってはパフォーマンスが低下する可能性があります。複数の同時ユーザがいるシステム上で %PARALLEL を指定してクエリを実行すると、総合的なパフォーマンスが低下する可能性があります。

Note:

%PARALLEL を指定するクエリは、読み取り専用ではなく、読み取り/書き込み可能なデータベースで実行する必要があります。そうでないと、<PROTECT> エラーが発生する可能性があります。

FROM 節で %PARALLEL キーワードを指定していても、並列処理ではなく線形処理を使用するクエリがあります。特に列指向テーブルでは、DISTINCT、ORDER BY、TOP、UNION を使用するクエリは並列処理されません。また、最適化すると並列処理の利点を得られなくなるクエリもあります。並列処理のために InterSystems IRIS によってクエリが分割されたか、分割された場合はどのように分割されたかを、プラン表示を使用して確認できます。現在のシステム上のプロセッサ数を特定するには、%SYSTEM.Util.NumberOfCPUs()Opens in a new tab メソッドを使用します。

詳細は、"SQL 最適化ガイド" の "並列クエリ処理の構成" を参照してください。

%STARTTABLE

このオプション・キーワードを指定すると、クエリ・オプティマイザは FROM 節にリストされた最初のテーブルから結合を開始します。残りのテーブルの結合順序は、クエリ・オプティマイザに委任されます。このキーワードと %INORDER を比べた場合、%INORDER では完全な結合順序を指定する点が異なります。

%STARTTABLE は、CROSS JOIN または RIGHT OUTER JOIN で使用することはできません。%STARTTABLE (または %FIRSTTABLE) を使用して、LEFT OUTER JOIN の右側 (または RIGHT OUTER JOIN の左側) で結合順序を開始することはできません。指定された先頭テーブルが外部結合に必要なテーブルと一致しない場合、SQLCODE -34 エラーである "オプティマイザが使用可能な結合順序を見つけることができませんでした" が生成されます。これを避けるために、外部結合で使用する場合は、%STARTTABLE のみを ANSI 形式の左外部結合または FULL 外部結合で使用することをお勧めします。

以下の表に、%INORDER および %STARTTABLE の最適化によりスーパークエリの親とインライン・ビューを組み合わせる場合のマージの動作を示します。

  結合オプティマイザを指定しないスーパークエリ %STARTTABLE を指定したスーパークエリ %INORDER を指定したスーパークエリ
結合オプティマイザを指定しないビュー 可能な場合にビューをマージする。

ビューがスーパークエリの先頭の場合、マージしない。

それ以外は、可能な場合にビューをマージする。

可能な場合にマージする。ビューの基本テーブルの順序は不規則になります。
%STARTTABLE を指定したビュー マージしない

ビューがスーパークエリの先頭の場合、可能であればマージする。ビューの先頭テーブルはスーパークエリの先頭テーブルになります。

それ以外はマージしない。

マージしない
%INORDER を指定したビュー マージしない マージしない

ビューが %INORDER で制御されない場合、マージしない。

それ以外は、可能な場合にマージする。ビューの順序は、スーパークエリの結合順序に置き換えられます。

%FIRSTTABLE ヒントは、機能的には %STARTTABLE と同じですが、任意の順序で結合テーブル・シーケンスを柔軟に指定できます。

コメント・オプション

SELECT、INSERT、UPDATE、DELETE、または TRUNCATE TABLE の各コマンド内で、クエリ・オプティマイザに 1 つ以上のコメント・オプションを指定できます。コメント・オプションでは、クエリ・オプティマイザが SQL クエリのコンパイル時に使用するオプションを指定します。コメント・オプションは、特定のクエリのシステム全体の構成の既定をオーバーライドするのに使用されることが多いです。

構文

構文 /*#OPTIONS */ (/* と # の間にスペースなし) は、コメント・オプションを指定します。コメント・オプションはコメントではなく、クエリ・オプティマイザに対する値を指定します。コメント・オプションは、JSON 構文 (通常は、/*#OPTIONS {"optionName":value} */ などの key:value ペア) を使用して指定します。入れ子になった値など、より複雑な JSON 構文がサポートされています。

コメント・オプションはコメントではないため、JSON 構文以外のテキストを含めることはできません。JSON ではないテキストに区切り文字として /* ...*/ を使用すると SQLCODE -153 エラーが発生します。InterSystems SQL では、JSON 文字列のコンテンツは検証されません。

#OPTIONS キーワードは、大文字で指定する必要があります。{ } 括弧で囲まれた JSON 構文内でスペースは使用できません。ダイナミック SQL 文などのように SQL コードを引用符で囲む場合、JSON 構文内の引用符は二重にする必要があります。例えば、myquery="SELECT Name FROM Sample.MyTest /*#OPTIONS {""optName"":""optValue""} */" のように指定します。

SQL コード内のコメントを指定できる場所であれば、任意の場所で /*#OPTIONS */ コメント・オプションを指定できます。表示されている文テキストでは、コメント・オプションは常に、文テキスト末尾のコメントとして表示されます。

SQL コードで、/*#OPTIONS */ コメント・オプションを指定できます。複数のコメント・オプションを指定した場合、返される文テキストでは、指定した順序でそれらのコメント・オプションが表示されます。同じオプションに対して複数のコメント・オプションを指定した場合、最後に指定したオプションの値が使用されます。

以下のコメント・オプションについては説明があります。

  • /*#OPTIONS {"NoTempFile":1} */ ： 既定では、モジュールは処理を実行し、その結果を内部一時ファイル (内部一時テーブル) に入力します。NoTempFile コメント・オプションに 1 を指定することにより、内部一時ファイルを生成しないクエリ・プランを作成するようクエリ・オプティマイザに強制することもできます。

Cosharding コメント・オプション

SQL クエリで複数のシャード・テーブルが指定された場合、SQL プリプロセッサは Cosharding コメント・オプションを生成します。これは、クエリ・キャッシュ・テキストの末尾に追加されます。この Cosharding オプションは、指定されたテーブルがコシャードされているかどうかを示します。

Note:

Cosharding オプションは、自動的に適用されます。ユーザがこのオプションを手動で指定することはできません。

以下の例では、指定された 3 つのテーブルがすべてコシャードされています。

/*#OPTIONS {"Cosharding":[["T1","T2","T3"]]} */

以下の例では、指定された 3 つのテーブルがいずれもコシャードされていません。

/*#OPTIONS {"Cosharding":[["T1"],["T2"],["T3"]]} */

以下の例では、テーブル T1 はコシャードされていませんが、テーブル T2 および T3 はコシャードされています。

/*#OPTIONS {"Cosharding":[["T1"],["T2","T3"]]} */

表示

/*#OPTIONS */ コメント・オプションは、SQL コマンドで指定された場所にかかわらず、SQL 文テキストの末尾に表示されます。表示される /*#OPTIONS */ コメント・オプションの中には、SQL コマンドでは指定されず、コンパイラのプリプロセッサによって生成されるものもあります。

/*#OPTIONS */ コメント・オプションは、[プラン表示] の [ステートメント・テキスト]、[クエリキャッシュ] の [クエリ文字列]、および [SQL 文] の [ステートメント・テキスト] に表示されます。

/*#OPTIONS */ コメント・オプションのみが異なる複数のクエリの場合、個別のクエリ・キャッシュが作成されます。


Previous sectionアダプティブ・モードの使用によるパフォーマンスの向上
FeedbackOpens in a new tab