FROM

テーブル参照に対するスキーマ名の指定

table-ref 名は、修飾付き (schema.tablename) でも未修飾 (tablename) でもかまいません。未修飾のテーブル名には、システム全体のデフォルト・スキーマ名、または (提供されている場合は) スキーマ検索パスのスキーマ名が指定されます。

• 埋め込み SQL では、#SQLCompile Path マクロ指示文を使用して、未修飾のテーブル名のスキーマを指定できます。以下の例では、2 つのスキーマ名を記述して検索パスを指定しています。

  #SQLCompile Path=Cinema,Sample

  

  詳細は、"Caché ObjectScript の使用法" の “ObjectScript マクロとマクロ・プリプロセッサ” を参照してください。

• ダイナミック SQL では、%SQL.StatementOpens in a new tab の %New メソッドを使用して、Caché で未修飾テーブル名を解決するときに使用するスキーマ検索パスを指定できます。以下の例では、2 つのスキーマ名を記述して検索パスを指定しています。

    SET tStatement = ##class(%SQL.Statement).%New(0,"Cinema","Sample")

  

  詳細は、"Caché SQL の使用法" の “ダイナミック SQL の使用法” を参照してください。

FROM 節の複数のテーブル名

FROM 節で複数のテーブル名を指定すると、Caché SQL はこれらのテーブルに対して結合操作を実行します。実行される結合のタイプは、join キーワード句またはテーブル名の各ペアの間の記号で指定します。2 つのテーブル名がコンマで区切られているときは、交差結合が実行されます。結合のさまざまなタイプと構文の詳細は、"JOIN" を参照してください。

結合が実行される順序は、SQL クエリ・オプティマイザによって自動的に決定され、クエリにテーブルがリストされる順序には基づきません。必要に応じて、クエリ・オプティマイザのオプションを指定して結合を実行する順序を制御できます。

以下の 3 つの SELECT 文は、2 つの個別のテーブルに対する行カウントと、両方のテーブルを指定する SELECT に対する行カウントを示しています。後者の例は、より大きなテーブル、デカルト積になります。この場合、1 番目のテーブルのそれぞれの行が、2 番目のテーブルのそれぞれの行に一致します。これは、Cross Join という処理です。

SELECT COUNT(*)
FROM Sample.Company

SELECT COUNT(*)
FROM Sample.Vendor

SELECT COUNT(*)
FROM Sample.Company,Sample.Vendor

CROSS JOIN 構文を明示的に使用しても同じ動作を実行できます。

SELECT COUNT(*)
FROM Sample.Company CROSS JOIN Sample.Vendor

多くの場合、Cross Join の広範囲なデータ重複は望ましくありません。その他の Join タイプをお勧めします。

SELECT 文で WHERE 節を指定すると、交差結合が実行され、WHERE 節の述語によって結果セットが決まります。これは ON 節を指定した INNER JOIN を実行することと同じです。したがって、以下の 2 つの例は同じ結果を返します。

SELECT p.Name,p.Home_State,em.Name,em.Office_State
FROM Sample.Person AS p, Sample.Employee AS em
WHERE p.Name %STARTSWITH 'E' AND em.Name %STARTSWITH 'E'

SELECT p.Name,p.Home_State,em.Name,em.Office_State
FROM Sample.Person AS p INNER JOIN Sample.Employee AS em
ON p.Name %STARTSWITH 'E' AND em.Name %STARTSWITH 'E'

FROM table-ref リストで明示的な結合構文を指定すると (コンマを使用するのではない) 、別のタイプの結合操作を実行できます。詳細は、"JOIN" を参照してください。

%ALLINDEX

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

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

特定の条件で条件レベル・ヒント %NOINDEX を使用して、%ALLINDEX に対する例外を指定することができます。%NOINDEX ヒントは、インデックスを使用しない各クエリ選択条件の前に配置します。例えば、WHERE %NOINDEX hiredate < ? のようにします。通常、この方法は条件によって圧倒的多数のデータが除外されない場合に使用します。「より小さい (<)」または「より大きい (>)」条件では、通常、条件レベル・ヒント %NOINDEX を使用すると効果的です。等価条件では、条件レベル・ヒント %NOINDEX を使用しても効果はありません。結合条件では、%NOINDEX は、WHERE 節の外部結合 =* および *= ではサポートされませんが、ON 節の結合ではサポートされます。詳細は、“Caché 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 [[schemaname.]tablename.]indexname [,...] %IGNOREINDEX [[schemaname.]tablename.]* [,...]

%INORDER

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

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

ビューとテーブル・サブクエリは、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 最適化オプションは、これを防止します。Caché は、代わりに 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 はこのために、その条件を満たすデータ値を一時インデックスに移入します。そのサブクエリを繰り返し実行する代わりに、Caché はこれらの値を一時インデックス内で検索します。例えば、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 やその他の下位の述語を Caché クエリ・オプティマイザ・インデックスで使用できる最上位の条件に移行できます。これらの既定の変換は、ほとんどの状況に適しています。

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

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

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

%PARALLEL

このオプションのキーワードは、クエリの FROM 節で指定します。これは、Caché が複数のプロセッサを使用してクエリの並列処理を実行することを示しています (該当する場合)。これにより、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 を指定してクエリを実行すると、総合的なパフォーマンスが低下する可能性があります。

%PARALLEL キーワードが FROM 節に指定されているかどうかに関係なく、一部のクエリは並列処理ではなく線形処理を使用する場合があります。一部のクエリは並列処理をサポートしておらず、一部のクエリは最適化すると並列処理の利点を享受できなくなります。Caché が並列処理のためにクエリを分配したか、またはどのようにそうしたかは、プラン表示を使用して確認できます。

詳細は、“Caché 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 の最適化によりスーパークエリの親とインライン・ビューを組み合わせる場合のマージの動作を示します。

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

FROM サブクエリと %VID

FROM サブクエリが呼び出されると、このサブクエリは、返されるサブクエリ行ごとに %VID を返します。%VID は整数カウンタ・フィールドです。その値は、システムによって割り当てられる一意の非 NULL かつ非ゼロ値であり、変更することはできません。%VID は、明示的に指定された場合にのみ返されます。これはデータ型 INTEGER として返されます。%VID の値は連続した整数であるため、サブクエリが順序付きデータを返す場合はとても有意義なものになります。TOP 節と組み合わせるときには、サブクエリでは ORDER BY 節以外は使用できません。

%VID は連続した整数であるため、%VID を使用して、ORDER BY 節が使用されたサブクエリ内の項目のランキングを特定できます。以下の例では、最も新しい 10 件のレコードが Name の順にリストされますが、それらのタイムスタンプ・ランキングは %VID の値を使用して簡単に確認できます。

SELECT Name,%VID,TimeStamp FROM
   (SELECT TOP 10 * FROM MyTable ORDER BY TimeStamp DESC)
ORDER BY Name 

%VID の一般的な用途の 1 つは、実行内容を 1 つの表示ウィンドウに収まる行数に合致する連続したサブセットに分割して、結果セットを “ウィンドウ表示” することです。例えば、20 件のレコードを表示してから、ユーザが Enter キーを押すまで待ち、次の 20 件のレコードを表示できます。

以下の例は、%VID を使用して、結果を 10 件のレコードごとのサブセットに分割して “ウィンドウ表示” します。

  ZNSPACE "SAMPLES"
  SET myq=4
  SET myq(1)="SELECT %VID,* "
  SET myq(2)="FROM (SELECT TOP 60 Name,Age FROM Sample.Person "
  SET myq(3)="WHERE Age > 55 ORDER BY Name) "
  SET myq(4)="WHERE %VID BETWEEN ? AND ?"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myq)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  FOR i=1:10:60 {
  SET rset = tStatement.%Execute(i,i+9)
  WHILE rset.%Next() {
     DO rset.%Print() } 
  WRITE !!
  }
  WRITE "End of data"

%VID の使用の詳細は、"Caché SQL の使用法" の "ビューの定義と使用" の章を参照してください。