Teradata 简明教程

Teradata - JOIN Index

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

有不同类型的 join 索引可用。

单表连接索引 (STJI)
多表连接索引 (MTJI)
Aggregated Join Index (AJI)

Single Table Join Index

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

Syntax

以下是 JOIN INDEX 的语法。

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

Example

考虑以下 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.

Multi Table Join Index

多表连接索引通过连接多张表来创建。多表连接索引可用于存储常用连接表的 result 集，以提高性能。

Example

以下示例通过连接 Employee 和 Salary 表创建名为 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);

每当更新基础表 Employee 或 Salary 时，连接索引 Employee_Salary_JI 也会自动更新。如果您正在运行一个连接 Employee 和 Salary 表的查询，那么优化器可能会选择直接从 Employee_Salary_JI 访问数据，而不是连接表。可以对查询使用 EXPLAIN 计划来验证优化器是否会选择基础表或连接索引。

Aggregate Join Index

如果某个表总是在某些列上聚合，那么可以在表上定义聚合连接索引来提升性能。聚合连接索引的一个限制是它仅支持 SUM 和 COUNT 函数。

Example

在以下示例中，员工和薪水连接起来，从而按部门识别总薪水。

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