Teradata - 连接索引

连接索引是一种物化视图。其定义被永久存储，并且每当连接索引中引用的基表更新时，数据也会更新。连接索引可能包含一个或多个表，也可能包含预聚合数据。连接索引主要用于提高性能。

有不同类型的连接索引可用。

单表连接索引 (STJI)
多表连接索引 (MTJI)
聚合连接索引 (AJI)

单表连接索引

单表连接索引允许基于与基表不同的主键列对大型表进行分区。

语法

以下是连接索引的语法。

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 表，则系统可能会使用 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 的连接索引。

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 计划可用于验证优化器是否会选择基表或连接索引。

聚合连接索引

如果某个表始终在某些列上进行聚合，则可以在该表上定义聚合连接索引以提高性能。聚合连接索引的一个限制是它仅支持 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);

打印页面