Teradata JOIN 索引
JOIN INDEX 是一个物化视图。它的定义被永久存储，并且只要连接索引中引用的基表更新，数据就会更新。 JOIN INDEX 可能包含一个或多个表，也包含预先聚合的数据。连接索引主要用于提高性能。
有不同类型的连接索引可用。
单表连接索引 (STJI)
多表连接索引 (MTJI)
聚合连接索引 (AJI)
单表连接索引
单表连接索引允许根据与基表中的主索引列不同的主索引列对大表进行分区。
语法
以下是 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 表上创建名为 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 列上散列。如果没有定义join索引，也没有定义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 的 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 更新时，Join 索引 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);
