Teradata - JOIN Index

JOIN INDEX ist eine materialisierte Ansicht. Die Definition wird dauerhaft gespeichert und die Daten werden immer dann aktualisiert, wenn die im Join-Index angegebenen Basistabellen aktualisiert werden. JOIN INDEX kann eine oder mehrere Tabellen enthalten und auch voraggregierte Daten enthalten. Join-Indizes werden hauptsächlich zur Verbesserung der Leistung verwendet.

Es stehen verschiedene Arten von Join-Indizes zur Verfügung.

• Single Table Join Index (STJI)
• Multi Table Join Index (MTJI)
• Aggregierter Join-Index (AJI)

Single Table Join Index

Mit dem Join-Index für einzelne Tabellen kann eine große Tabelle basierend auf den anderen primären Indexspalten als der aus der Basistabelle partitioniert werden.

Syntax

Es folgt die Syntax eines JOIN INDEX.

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

Beispiel

Betrachten Sie die folgenden Mitarbeiter- und Gehaltstabellen.

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

Das folgende Beispiel erstellt einen Join-Index mit dem Namen Employee_JI für die Employee-Tabelle.

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

Wenn der Benutzer eine Abfrage mit einer WHERE-Klausel für EmployeeNo sendet, fragt das System die Employee-Tabelle unter Verwendung des eindeutigen Primärindex ab. Wenn der Benutzer die Mitarbeitertabelle mit Employee_Name abfragt, kann das System mit Employee_Name auf den Join-Index Employee_JI zugreifen. Die Zeilen des Join-Index werden in der Spalte employee_name gehasht. Wenn der Verknüpfungsindex nicht definiert ist und der Mitarbeitername nicht als Sekundärindex definiert ist, führt das System einen vollständigen Tabellenscan durch, um auf die Zeilen zuzugreifen, was zeitaufwändig ist.

Sie können den folgenden EXPLAIN-Plan ausführen und den Optimierungsplan überprüfen. Im folgenden Beispiel sehen Sie, dass das Optimierungsprogramm den Join-Index anstelle der Employee-Basistabelle verwendet, wenn die Tabelle die Spalte Employee_Name abfragt.

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.

Multi Table Join Index

Ein Multi-Table-Join-Index wird erstellt, indem mehr als eine Tabelle verbunden wird. Der Multi-Table-Join-Index kann zum Speichern der Ergebnismenge häufig verknüpfter Tabellen verwendet werden, um die Leistung zu verbessern.

Beispiel

Im folgenden Beispiel wird ein JOIN INDEX mit dem Namen Employee_Salary_JI erstellt, indem die Tabellen Employee und Salary verknüpft werden.

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

Immer wenn die Basistabellen Employee oder Salary aktualisiert werden, wird auch der Join-Index Employee_Salary_JI automatisch aktualisiert. Wenn Sie eine Abfrage ausführen, die Mitarbeiter- und Gehaltstabellen verbindet, kann der Optimierer entscheiden, direkt auf die Daten von Employee_Salary_JI zuzugreifen, anstatt die Tabellen zu verbinden. Der EXPLAIN-Plan für die Abfrage kann verwendet werden, um zu überprüfen, ob das Optimierungsprogramm die Basistabelle oder den Join-Index auswählt.

Aggregierter Join-Index

Wenn eine Tabelle in bestimmten Spalten konsistent aggregiert wird, kann der aggregierte Verknüpfungsindex für die Tabelle definiert werden, um die Leistung zu verbessern. Eine Einschränkung des aggregierten Join-Index besteht darin, dass er nur SUM- und COUNT-Funktionen unterstützt.

Beispiel

Im folgenden Beispiel werden Mitarbeiter und Gehalt zusammengeführt, um das Gesamtgehalt pro Abteilung zu ermitteln.

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