Teradata - Tham gia chỉ mục

JOIN INDEX là một dạng xem vật thể hóa. Định nghĩa của nó được lưu trữ vĩnh viễn và dữ liệu được cập nhật bất cứ khi nào các bảng cơ sở được tham chiếu trong chỉ mục nối được cập nhật. JOIN INDEX có thể chứa một hoặc nhiều bảng và cũng chứa dữ liệu được tổng hợp trước. Chỉ mục tham gia chủ yếu được sử dụng để cải thiện hiệu suất.

Có nhiều loại chỉ mục tham gia khác nhau có sẵn.

• Chỉ số tham gia bảng đơn (STJI)
• Chỉ số tham gia nhiều bảng (MTJI)
• Chỉ số tham gia tổng hợp (AJI)

Chỉ mục tham gia bảng đơn

Chỉ mục Nối bảng đơn cho phép phân vùng một bảng lớn dựa trên các cột chỉ mục chính khác với cột chỉ mục từ bảng cơ sở.

Cú pháp

Sau đây là cú pháp của một INDEX JOIN.

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

Thí dụ

Hãy xem xét các bảng Nhân viên và Lương sau đây.

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

Sau đây là một ví dụ tạo chỉ mục Tham gia có tên Employee_JI trên bảng Employee.

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

Nếu người dùng gửi truy vấn có mệnh đề WHERE trên EmployeeNo, thì hệ thống sẽ truy vấn bảng Employee bằng cách sử dụng chỉ mục chính duy nhất. Nếu người dùng truy vấn bảng nhân viên bằng tên worker_name, thì hệ thống có thể truy cập vào chỉ mục tham gia Employee_JI bằng cách sử dụng tên_nhân_vân. Các hàng của chỉ mục kết hợp được băm trên cột tên_nhân_viên. Nếu chỉ mục tham gia không được xác định và tên_người_nghiệp không được xác định là chỉ mục phụ, thì hệ thống sẽ thực hiện quét toàn bộ bảng để truy cập các hàng tốn thời gian.

Bạn có thể chạy kế hoạch EXPLAIN sau đây và xác minh kế hoạch trình tối ưu hóa. Trong ví dụ sau, bạn có thể thấy rằng trình tối ưu hóa đang sử dụng Chỉ mục tham gia thay vì bảng Nhân viên cơ sở khi bảng truy vấn bằng cột Tên nhân viên.

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.

Chỉ mục tham gia nhiều bảng

Chỉ mục tham gia nhiều bảng được tạo bằng cách tham gia nhiều bảng. Chỉ mục tham gia nhiều bảng có thể được sử dụng để lưu trữ tập hợp kết quả của các bảng được tham gia thường xuyên để cải thiện hiệu suất.

Thí dụ

Ví dụ sau tạo một JOIN INDEX có tên Employee_Salary_JI bằng cách tham gia các bảng Nhân viên và Lương.

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

Bất cứ khi nào bảng cơ sở Nhân viên hoặc Lương được cập nhật, thì chỉ mục Tham gia Employee_Salary_JI cũng được cập nhật tự động. Nếu bạn đang chạy một truy vấn tham gia các bảng Nhân viên và Lương, thì trình tối ưu hóa có thể chọn truy cập trực tiếp vào dữ liệu từ Employee_Salary_JI thay vì tham gia các bảng. Kế hoạch GIẢI THÍCH trên truy vấn có thể được sử dụng để xác minh xem trình tối ưu hóa sẽ chọn bảng cơ sở hoặc Tham gia chỉ mục.

Chỉ mục tham gia tổng hợp

Nếu một bảng được tổng hợp nhất quán trên các cột nhất định, thì chỉ số kết hợp tổng hợp có thể được xác định trên bảng để cải thiện hiệu suất. Một hạn chế của chỉ mục kết hợp tổng hợp là nó chỉ hỗ trợ các hàm SUM và COUNT.

Thí dụ

Trong ví dụ sau, Nhân viên và Tiền lương được kết hợp để xác định tổng mức lương cho mỗi Bộ phận.

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