Teradata-JOINインデックス

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

使用可能な結合インデックスにはさまざまなタイプがあります。

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

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

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

構文

以下は、JOININDEXの構文です。

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

例

次の従業員と給与の表を検討してください。

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テーブルにクエリを実行すると、システムはemployee_nameを使用して結合インデックスEmployee_JIにアクセスできます。結合インデックスの行は、employee_name列でハッシュされます。結合インデックスが定義されておらず、employee_nameがセカンダリインデックスとして定義されていない場合、システムは全表スキャンを実行して行にアクセスしますが、これには時間がかかります。

次のEXPLAINプランを実行して、オプティマイザープランを確認できます。次の例では、テーブルがEmployee_Name列を使用してクエリを実行するときに、オプティマイザがベースのEmployeeテーブルではなくJoinIndexを使用していることがわかります。

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という名前のJOININDEXを作成します。

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関数のみをサポートすることです。

例

次の例では、従業員と給与を結合して、部門ごとの合計給与を識別します。

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);