17.10 - 結合インデックスによるクエリーのカバー - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 詳細トピック

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1184-171K-JPN
Language
日本語 (日本)

クエリーが要求している列がインデックスから検索可能であるため、インデックスによってサポートされる実テーブルを走査する必要がない場合、そのインデックスはクエリーをカバーすると表現します。ベンダーによってはこれをインデックス専用アクセスと言います。セカンダリ インデックスが小規模なクエリーをカバーすることもありますが、通常はハッシュ インデックスおよび結合インデックスがさまざまな目的のクエリーに応答するために呼び出されます。結合インデックスが適切に定義されている場合、そのインデックスが対象としない列を要求するクエリーをカバーすることもできます。この状況は部分的なクエリーのカバーと呼ばれ、結合インデックスが部分的にクエリーをカバーする場合、最適化プログラムはこの方法を使用する際のコストとインデックスによってサポートされる実テーブルを操作する際のコストとを比較します。部分的なクエリーのカバーとして検討されるように結合インデックスを定義するための条件を、以下の段落に示します。

結合インデックスがクエリーをカバーできるかどうかを判別するための他のより複雑な条件については、結合インデックス定義がクエリーよりも多くのテーブルを参照する際の、結合インデックスによるカバーの制限および追加のテーブルを持つ結合インデックスがクエリーをカバーできるかどうかに関するルールで説明しています。

選択リストに式を使用して定義された結合インデックスのカバーは、選択リストに基本列を使用して指定された結合インデックスよりも狭くなります。

例えば、クエリー リライト サブシステムでは、次の結合インデックスji_f1を使用して、列f1に対して何らかの文字関数を指定するクエリーを書き換えることができます。

CREATE JOIN INDEX ji_f1 AS
  SELECT b1, f1
  FROM t1
  WHERE a1 > 0;

しかし、結合インデックスji_substr_f1が選択リストで指定しているのと同じSUBSTR関数を列f1に対して指定するクエリーを書き換える場合、クエリー リライト サブシステムが使用できるのは、結合インデックスji_substr_f1のみです。

CREATE JOIN INDEX ji_substr_f1 AS
  SELECT b1, SUBSTR(f1,1,10) AS s
  FROM t1
  WHERE a1 > 0;

グローバル結合インデックスを戦術的クエリーで使用する方法については、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

定義DDLに指定された基本アクセス キーによって実テーブルの列にアクセスできる結合インデックスは、部分的なクエリーのカバーとして使用可能です。この方法で定義された結合インデックスは、グローバル インデックスまたはグローバル結合インデックスと呼ばれることもあります。

グローバル結合インデックスは、以下の代用ロジックによってサポートされます。

単一テーブル結合インデックスの定義の指定 その結合インデックスの状態
次のうちの1つ以上が指定されている場合。
  • column_nameリスト内の列名としてのROWIDキーワード

    ROWIDを指定できる場所は、CREATE JOIN INDEX文の最も外側にあるSELECTだけです。

  • 実テーブルのUPIを定義する列セット
  • 実テーブルのNUPIを定義する列セットとROWIDキーワード
  • 実テーブルのNUPIを定義する列セットと実テーブルのUSIを定義する列セット
クエリーで指定されている列のサブセットも含まれている場合、部分的にカバーするインデックスとなります。

部分的なカバーは、単一テーブル結合インデックスに限定されません。

これらの列セットの1つと共に定義された結合インデックスは、グローバル インデックスまたはグローバル結合インデックスと呼ばれることもあります。グローバル結合インデックスを適用するいくつかの具体的な方法については、<Teradata Vantage™ - データベースの設計、B035-1094>を参照してください。

最適化ルーチンは、クエリーによって要求されているが単一テーブル結合インデックスの定義には含まれていない列を含めるため、結合インデックスから実テーブルへの結合を指定します。

ROWIDキーワード、実テーブルUPI列、または実テーブルNUPI列と、ROWIDキーワードか実テーブルUSI列セットのいずれかが指定されていない場合。 クエリーを部分的にカバーするのには使用できません。

最適化ルーチンは、クエリーによって要求されているが結合インデックスには含まれていない列を含めるため、結合インデックスから実テーブルへの結合を指定します。

結合インデックスの定義に指定されている項目 単一テーブル結合インデックスから実テーブルへの結合で使用される結合タイプ
実テーブルROWIDのみ

ROWIDを指定できる場所は、CREATE JOIN INDEX文の最も外側にあるSELECTだけです。

行ID
実テーブルのプライマリ インデックス マージ

この場合、プライマリ インデックス列セットとROWIDが存在するのであれば、両方とも結合のために使用されます。

クエリーの作成時にこの結合を明示的に指定しなかった場合でも、結合に関して1つのクエリー ブロックに128個のテーブルおよびビューという制限が適用されます。

結合インデックスの定義に指定されている項目 結果
UPI 実テーブルの行を識別するためにはUPIで十分なので、部分的なカバーとして使用するためインデックス定義にROWIDを含める必要はありません。

ROWIDを指定できる場所は、CREATE JOIN INDEX文の最も外側にあるSELECTだけです。

NUPI クエリーの部分的なカバーとして使用するためには、次の任意のの指定を含める必要があります。
  • 実テーブルNUPI列セットROWIDをインデックス定義の中に指定。
  • 実テーブルNUPI列セット実テーブルUSI列セットをインデックス定義の中に指定。

望ましいのはNUPIとROWIDのオプションです。

最適化ルーチンが結合計画の中で部分的にカバーする結合インデックスを指定するのは、それを使用した場合のコストが使用しない場合のコストより少ないときだけです。それは、クエリーの全体をカバーするインデックスの場合と同じです。

例えば、次のようなテーブルと結合インデックスの定義について考えてみましょう。

CREATE TABLE t1 (
  x1 INTEGER,
  y1 INTEGER,
  z1 INTEGER)
PRIMARY INDEX (x1);
    
CREATE TABLE t2 (
  x2 INTEGER,
  y2 INTEGER,
  z2 INTEGER)
PRIMARY INDEX (x2);
    
CREATE JOIN INDEX j1 AS
  SELECT y1
  FROM t1
PRIMARY INDEX (y1);

最適化ルーチンは、次のようなクエリーの結合計画において、部分的にカバーするインデックスとして単一テーブル結合インデックスj1を選択します。

EXPLAIN
SELECT x1, y1, z2
FROM t1, t2
WHERE y1 = x2
AND   y2 = 1;
    
Explanation
---------------------------------------------------------------
1) First, we lock MyDB.t2 for read on a reserved RowHash to prevent
   global deadlock.
2) Next, we lock MyDB.t1 for read on a reserved RowHash to prevent
   global deadlock.
3) We lock MyDB.t2 for read, and we lock MyDB.t1 for read.
4) We do an all-AMPs RETRIEVE step from MyDB.t1 by way of an all-rows
   scan with a condition of ("NOT (MyDB.t1.y1 IS NULL)") into Spool 2
   (all_amps), which is redistributed by the hash code of (MyDB.t1.y1)
   to all AMPs.  Then we do a SORT to order Spool 2 by row hash. The
   size of Spool 2 is estimated with low confidence to be 4 rows (84
   bytes). The estimated time for this step is 0.03 seconds.
5) We do an all-AMPs JOIN step from MyDB.t2 by way of a RowHash match
   scan with a condition of (""MyDB.t2.y2 = 1""), which is joined to
   Spool 2 (Last Use) by way of a RowHash match scan. MyDB.t2 and
   Spool 2 are joined using a merge join, with a join condition of
   (""y1 = MyDB.t2.x2""). The result goes into Spool 1 (group_amps),
   which is built locally on the AMPs.  The size of Spool 1 is
   estimated with no confidence to be 4 rows (132 bytes).  The
   estimated time for this step is 0.11 seconds.
   -> The contents of Spool 1 are sent back to the user as the result
   of statement 1.  The total estimated time is 0.13 seconds.

このクエリーでテーブルt1とテーブルt2の結合には、t1のすべての行をt1.y1によって再配置することが必要になります。t1が巨大である場合、この操作にはかなりコストがかかることになります。

コストの点でもっと効率的な別の方法は、結合インデックスj1と実テーブルt2の間でローカルに結合を実行した後、結合の中間結果を再配置して実テーブルt1と結合するという方法です。実テーブルt2にはt2.y2 = 1という厳密な制約条件があるため、j1t2の結合結果を再配置した後に結果をt1に結合した場合のコストは、t1の再配置コストよりも低くなります。したがって、この場合、最適化ルーチンはj1-t1の結合処理のためにt2を選択します。

一方、同じクエリーで、t2.y2 = 1という制約のない場合を考えてみましょう。その場合、最適化ルーチンは、部分的にカバーするインデックスとしてj1が適格である場合であっても、クエリーをカバーするのにj1を選択しません。

EXPLAIN SELECT x1, y1, z2
FROM t1,t2
WHERE y1 = x2;
    
 *** Help information returned. 22 rows.
 *** Total elapsed time was 1 second.
    
Explanation
-----------------------------------------------------------
1) First, we lock MyDB.t2 for read on a reserved RowHash to prevent
   global deadlock.
2) Next, we lock MyDB.t1 for read on a reserved RowHash to prevent
   global deadlock.
3) We lock MyDB.t2 for read, and we lock MyDB.t1 for read.
4) We do an all-AMPs RETRIEVE step from MyDB.t1 by way of an all-rows
   scan with a condition of ("NOT (MyDB.t1.y1 IS NULL)") into Spool 2
   (all_amps), which is redistributed by the hash code of(MyDB.t1.y1)
   to all AMPs.  Then we do a SORT to order Spool 2 by row hash. The
   size of Spool 2 is estimated with low confidence to be 4 rows (84
   bytes).  The estimated time for this step is 0.03 seconds.
5) We do an all-AMPs JOIN step from MyDB.t2 by way of a RowHash match
   scan, which is joined to Spool 2 (Last Use) by way of a RowHash
   match scan.  MyDB.t2 and Spool 2 are joined using a merge join,
   with a join condition of ("y1 = MyDB.t2.x2"). The result goes into
   Spool 1 (group_amps), which is built locally on the AMPs. The
   size of Spool 1 is estimated with index join confidence to be 6
   rows (198 bytes). The estimated time for this step is 0.11
   seconds.
-> The contents of Spool 1 are sent back to the user as the result of
   statement 1.  The total estimated time is 0.13 seconds.