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

列印頁面