Teradata JOIN索引

JOIN INDEX是一個(gè)物化視圖。 其定義是永久存儲(chǔ)的，并且只要更新連接索引中引用的基表，就會(huì)更新數(shù)據(jù)。 JOIN INDEX可以包含一個(gè)或多個(gè)表，并且還包含預(yù)聚合數(shù)據(jù)。 連接索引主要用于提高性能。
有不同類型的連接索引可用。
單表連接索引（STJI）
多表連接索引（MTJI）
聚合連接索引（AJI）
單表連接索引
單表聯(lián)接索引允許基于不同于基表中的主索引列的主索引列來分區(qū)大表。

語法

以下是JOIN INDEX的語法。

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表上創(chuàng)建名為Employee_JI的聯(lián)接索引的示例。

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

如果用戶在EmployeeNo上提交帶有WHERE子句的查詢，那么系統(tǒng)將使用唯一的主索引查詢Employee表。 如果用戶使用employee_name查詢employee表，則系統(tǒng)可以使用employee_name訪問連接索引Employee_JI。 連接索引的行在employee_name列上進(jìn)行散列。 如果未定義連接索引且employee_name未定義為輔助索引，則系統(tǒng)將執(zhí)行全表掃描以訪問耗時(shí)的行。
您可以運(yùn)行以下EXPLAIN計(jì)劃并驗(yàn)證優(yōu)化程序計(jì)劃。 在以下示例中，您可以看到，當(dāng)表使用Employee_Name列查詢時(shí)，優(yōu)化程序正在使用Join Index而不是base 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. 

多表連接索引

通過連接多個(gè)表來創(chuàng)建多表連接索引。 多表連接索引可用于存儲(chǔ)結(jié)果集的頻繁連接的表以提高性能。

例子

以下示例通過連接Employee和Salary表來創(chuàng)建一個(gè)名為Employee_Salary_JI的JOIN 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);

每當(dāng)更新基表Employee或Salary時(shí)，連接索引Employee_Salary_JI也會(huì)自動(dòng)更新。 如果您正在運(yùn)行連接Employee和Salary表的查詢，則優(yōu)化程序可以選擇直接從Employee_Salary_JI訪問數(shù)據(jù)，而不是連接表。 EXPLAIN計(jì)劃對查詢可以用來驗(yàn)證優(yōu)化器是否會(huì)選擇基表或Join索引。

聚合聯(lián)接索引

如果表在某些列上始終聚合，則可以在表上定義聚合連接索引以提高性能。 聚合連接索引的一個(gè)限制是它僅支持SUM和COUNT函數(shù)。

例子

在以下示例中，加入員工和工資以確定每個(gè)部門的總工資。

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