Teradata-join-index

Teradata-JOINインデックス

JOIN INDEXはマテリアライズドビューです。 その定義は永続的に保存され、データは、結合インデックスで参照されるベーステーブルが更新されるたびに更新されます。 JOIN INDEXには、1つ以上のテーブルが含まれ、事前に集計されたデータも含まれます。 結合インデックスは、主にパフォーマンスを改善するために使用されます。

さまざまな種類の結合インデックスが利用可能です。

• 単一テーブル結合インデックス（STJI）
• マルチテーブル結合インデックス（MTJI） *集約結合インデックス（AJI）

単一テーブル結合インデックス

単一テーブル結合インデックスを使用すると、基本テーブルの列とは異なるプライマリインデックス列に基づいて大きなテーブルをパーティション化できます。

構文

JOIN INDEXの構文は次のとおりです。

CREATE JOIN INDEX <index name>
AS
<SELECT Query>
<Index Definition>;

例

次のEmployeeテーブルとSalaryテーブルを検討してください。

CREATE SET TABLE EMPLOYEE,FALLBACK (
   EmployeeNo INTEGER,
   FirstName VARCHAR(30) ,
   LastName VARCHAR(30) ,
   DOB DATE FORMAT 'YYYY-MM-DD',
   JoinedDate DATE FORMAT 'YYYY-MM-DD',
   DepartmentNo BYTEINT
)
UNIQUE PRIMARY INDEX ( EmployeeNo );

CREATE SET TABLE SALARY,FALLBACK (
   EmployeeNo INTEGER,
   Gross INTEGER,
   Deduction INTEGER,
   NetPay INTEGER
)
PRIMARY INDEX ( EmployeeNo )
UNIQUE INDEX (EmployeeNo);

以下は、EmployeeテーブルにEmployee_JIという名前の結合インデックスを作成する例です。

CREATE JOIN INDEX Employee_JI
AS
SELECT EmployeeNo,FirstName,LastName,
BirthDate,JoinedDate,DepartmentNo
FROM Employee
PRIMARY INDEX(FirstName);

ユーザーがEmployeeNoのWHERE句を使用してクエリを送信すると、システムは一意のプライマリインデックスを使用してEmployeeテーブルをクエリします。 ユーザーがemployee_nameを使用して従業員テーブルを照会すると、システムはemployee_nameを使用して結合インデックスEmployee_JIにアクセスできます。 結合インデックスの行は、employee_name列でハッシュされます。 結合インデックスが定義されておらず、employee_nameがセカンダリインデックスとして定義されていない場合、システムは全テーブルスキャンを実行して行にアクセスしますが、これには時間がかかります。

次のEXPLAINプランを実行して、オプティマイザープランを検証できます。 次の例では、テーブルがEmployee_Name列を使用してクエリを実行するときに、オプティマイザーがベースEmployeeテーブルの代わりに結合インデックスを使用していることがわかります。

EXPLAIN SELECT* FROM EMPLOYEE WHERE FirstName='Mike';
*** Help information returned. 8 rows.
*** Total elapsed time was 1 second.
Explanation
------------------------------------------------------------------------
   1) First, we do a single-AMP RETRIEVE step from EMPLOYEE_JI by
      way of the primary index "EMPLOYEE_JI.FirstName = 'Mike'"
      with no residual conditions into Spool 1 (one-amp), which is built
      locally on that AMP.  The size of Spool 1 is estimated with low
      confidence to be 2 rows (232 bytes).  The estimated time for this
      step is 0.02 seconds.
   → The contents of Spool 1 are sent back to the user as the result of
      statement 1.  The total estimated time is 0.02 seconds.

マルチテーブル結合インデックス

複数テーブル結合インデックスは、複数のテーブルを結合することにより作成されます。 マルチテーブル結合インデックスを使用して、頻繁に結合されるテーブルの結果セットを保存し、パフォーマンスを向上させることができます。

例

次の例では、EmployeeテーブルとSalaryテーブルを結合して、Employee_Salary_JIという名前のJO​​IN INDEXを作成します。

CREATE JOIN INDEX Employee_Salary_JI
AS
SELECT a.EmployeeNo,a.FirstName,a.LastName,
a.BirthDate,a.JoinedDate,a.DepartmentNo,b.Gross,b.Deduction,b.NetPay
FROM Employee a
INNER JOIN Salary b
ON(a.EmployeeNo = b.EmployeeNo)
PRIMARY INDEX(FirstName);

ベーステーブルEmployeeまたはSalaryが更新されるたびに、結合インデックスEmployee_Salary_JIも自動的に更新されます。 EmployeeテーブルとSalaryテーブルを結合するクエリを実行している場合、オプティマイザーはテーブルを結合する代わりにEmployee_Salary_JIのデータに直接アクセスすることを選択できます。 クエリのEXPLAINプランを使用して、オプティマイザがベーステーブルまたは結合インデックスを選択するかどうかを確認できます。

集約結合インデックス

テーブルが特定の列で一貫して集約される場合は、集約結合インデックスをテーブルで定義してパフォーマンスを改善できます。 集約結合インデックスの1つの制限は、SUM関数とCOUNT関数のみをサポートすることです。

例

次の例では、EmployeeとSalaryを結合して、部門ごとの合計給与を識別しています。

CREATE JOIN INDEX Employee_Salary_JI
AS
SELECT a.DepartmentNo,SUM(b.NetPay) AS TotalPay
FROM Employee a
INNER JOIN Salary b
ON(a.EmployeeNo = b.EmployeeNo)
GROUP BY a.DepartmentNo
Primary Index(DepartmentNo);