Teradata - Index JOIN

JOIN INDEX est une vue matérialisée. Sa définition est stockée en permanence et les données sont mises à jour chaque fois que les tables de base référencées dans l'index de jointure sont mises à jour. JOIN INDEX peut contenir une ou plusieurs tables et également des données pré-agrégées. Les index de jointure sont principalement utilisés pour améliorer les performances.

Il existe différents types d'index de jointure disponibles.

Index de jointure de table unique (STJI)
Index de jointure multi-table (MTJI)
Index de jointure agrégé (AJI)

Index de jointure de table unique

L'index de jointure de table unique permet de partitionner une grande table en fonction des différentes colonnes d'index primaire que celle de la table de base.

Syntaxe

Voici la syntaxe d'un JOIN INDEX.

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

Exemple

Considérez les tableaux des employés et des salaires suivants.

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

Voici un exemple qui crée un index de jointure nommé Employee_JI sur la table Employee.

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

Si l'utilisateur soumet une requête avec une clause WHERE sur EmployeeNo, le système interrogera la table Employee à l'aide de l'index primaire unique. Si l'utilisateur interroge la table des employés en utilisant nom_employé, le système peut accéder à l'index de jointure Employee_JI en utilisant nom_employé. Les lignes de l'index de jointure sont hachées sur la colonne nom_employé. Si l'index de jointure n'est pas défini et que le nom_employé n'est pas défini comme index secondaire, le système effectuera une analyse complète de la table pour accéder aux lignes, ce qui prend du temps.

Vous pouvez exécuter le plan EXPLAIN suivant et vérifier le plan d'optimisation. Dans l'exemple suivant, vous pouvez voir que l'optimiseur utilise l'index de jointure au lieu de la table Employee de base lorsque la table interroge à l'aide de la colonne 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.

Index de jointure multi-table

Un index de jointure multi-table est créé en joignant plusieurs tables. L'index de jointure multi-table peut être utilisé pour stocker le jeu de résultats des tables fréquemment jointes afin d'améliorer les performances.

Exemple

L'exemple suivant crée un JOIN INDEX nommé Employee_Salary_JI en joignant les tables Employee et Salary.

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

Chaque fois que les tables de base Employee ou Salary sont mises à jour, l'index de jointure Employee_Salary_JI est également mis à jour automatiquement. Si vous exécutez une requête joignant les tables Employee et Salary, l'optimiseur peut choisir d'accéder directement aux données de Employee_Salary_JI au lieu de joindre les tables. Le plan EXPLAIN sur la requête peut être utilisé pour vérifier si l'optimiseur choisira la table de base ou l'index de jointure.

Index de jointure agrégé

Si une table est agrégée de manière cohérente sur certaines colonnes, un index de jointure agrégé peut être défini sur la table pour améliorer les performances. Une limitation de l'index de jointure agrégé est qu'il prend en charge uniquement les fonctions SUM et COUNT.

Exemple

Dans l'exemple suivant, l'employé et le salaire sont joints pour identifier le salaire total par service.

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