Teradata - Indice JOIN

JOIN INDEX è una vista materializzata. La sua definizione viene memorizzata in modo permanente ei dati vengono aggiornati ogni volta che vengono aggiornate le tabelle di base a cui fa riferimento l'indice di join. JOIN INDEX può contenere una o più tabelle e contenere anche dati pre-aggregati. Gli indici di join vengono utilizzati principalmente per migliorare le prestazioni.

Sono disponibili diversi tipi di indici di join.

• Indice Single Table Join (STJI)
• Indice di unione di più tabelle (MTJI)
• Indice aggregato di join (AJI)

Indice join tabella singola

L'indice Single Table Join consente di partizionare una tabella di grandi dimensioni in base alle diverse colonne dell'indice primario rispetto a quella della tabella di base.

Sintassi

Di seguito è riportata la sintassi di un JOIN INDEX.

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

Esempio

Considera le seguenti tabelle dei dipendenti e degli stipendi.

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

Di seguito è riportato un esempio che crea un indice di join denominato Employee_JI sulla tabella Employee.

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

Se l'utente invia una query con una clausola WHERE su EmployeeNo, il sistema interrogherà la tabella Employee utilizzando l'indice primario univoco. Se l'utente interroga la tabella dei dipendenti utilizzando nome_impiegato, il sistema può accedere all'indice di join Employee_JI utilizzando nome_impiegato. Le righe dell'indice di join vengono sottoposte ad hashing nella colonna dipendente_name. Se l'indice di join non è definito e il dipendente_name non è definito come indice secondario, il sistema eseguirà la scansione completa della tabella per accedere alle righe che richiede tempo.

È possibile eseguire il seguente piano EXPLAIN e verificare il piano di ottimizzazione. Nell'esempio seguente è possibile vedere che l'ottimizzatore utilizza l'indice di join anziché la tabella Employee di base quando la tabella esegue una query utilizzando la colonna 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.

Indice di unione di più tabelle

Un indice di join multi-tabella viene creato unendo più di una tabella. L'indice di join multi-tabella può essere utilizzato per archiviare il set di risultati delle tabelle unite di frequente per migliorare le prestazioni.

Esempio

Il seguente esempio crea un JOIN INDEX denominato Employee_Salary_JI unendo le tabelle Employee e 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);

Ogni volta che le tabelle di base Employee o Salary vengono aggiornate, anche l'indice di join Employee_Salary_JI viene aggiornato automaticamente. Se si esegue una query che unisce le tabelle Employee e Salary, l'ottimizzatore può scegliere di accedere direttamente ai dati da Employee_Salary_JI invece di unirsi alle tabelle. Il piano EXPLAIN sulla query può essere utilizzato per verificare se l'ottimizzatore sceglierà la tabella di base o l'indice di join.

Indice di join aggregato

Se una tabella viene aggregata in modo coerente su determinate colonne, è possibile definire l'indice di join aggregato sulla tabella per migliorare le prestazioni. Una limitazione dell'indice di join aggregato è che supporta solo le funzioni SUM e COUNT.

Esempio

Nell'esempio seguente Employee e Salary vengono uniti per identificare lo stipendio totale per reparto.

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