Teradata - JOIN Index

JOIN INDEX es una vista materializada. Su definición se almacena permanentemente y los datos se actualizan cada vez que se actualizan las tablas base referidas en el índice de unión. JOIN INDEX puede contener una o más tablas y también contener datos agregados previamente. Los índices de unión se utilizan principalmente para mejorar el rendimiento.

Hay diferentes tipos de índices de combinación disponibles.

Índice de unión de una sola tabla (STJI)
Índice de unión de múltiples tablas (MTJI)
Índice de unión agregado (AJI)

Índice de unión de una sola tabla

El índice de unión de tabla única permite particionar una tabla grande en función de las diferentes columnas de índice primario que la de la tabla base.

Sintaxis

A continuación se muestra la sintaxis de JOIN INDEX.

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

Ejemplo

Considere las siguientes tablas de empleados y salarios.

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

A continuación, se muestra un ejemplo que crea un índice de unión llamado Employee_JI en la tabla de empleados.

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

Si el usuario envía una consulta con una cláusula WHERE en EmployeeNo, el sistema consultará la tabla de empleados utilizando el índice primario único. Si el usuario consulta la tabla de empleados usando employee_name, entonces el sistema puede acceder al índice de unión Employee_JI usando employee_name. Las filas del índice de combinación tienen un hash en la columna employee_name. Si el índice de unión no está definido y el nombre_empleado no está definido como índice secundario, entonces el sistema realizará un escaneo completo de la tabla para acceder a las filas, lo que requiere mucho tiempo.

Puede ejecutar el siguiente plan EXPLAIN y verificar el plan optimizador. En el siguiente ejemplo, puede ver que el optimizador usa el índice de unión en lugar de la tabla base de empleados cuando la tabla consulta usando la columna Employee_Name.

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.

Índice de unión de varias tablas

Un índice de combinación de varias tablas se crea uniendo más de una tabla. El índice de combinación de varias tablas se puede utilizar para almacenar el conjunto de resultados de las tablas unidas con frecuencia para mejorar el rendimiento.

Ejemplo

El siguiente ejemplo crea un JOIN INDEX llamado Employee_Salary_JI uniendo las tablas de Empleado y Salario.

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

Siempre que se actualizan las tablas base Empleado o Salario, el índice de unión Empleado_Salario_JI también se actualiza automáticamente. Si está ejecutando una consulta que une las tablas de Empleados y Salarios, entonces el optimizador puede optar por acceder a los datos de Employee_Salary_JI directamente en lugar de unirse a las tablas. El plan EXPLAIN de la consulta se puede utilizar para verificar si el optimizador elegirá la tabla base o el índice de unión.

Índice de unión agregado

Si una tabla se agrega consistentemente en ciertas columnas, entonces el índice de combinación agregado se puede definir en la tabla para mejorar el rendimiento. Una limitación del índice de combinación agregado es que solo admite funciones SUM y COUNT.

Ejemplo

En el siguiente ejemplo, Empleado y Salario se unen para identificar el salario total por Departamento.

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