Teradata - JOIN Index

JOIN INDEX to zmaterializowany widok. Jego definicja jest trwale przechowywana, a dane są aktualizowane za każdym razem, gdy aktualizowane są tabele podstawowe, do których odnosi się indeks łączenia. JOIN INDEX może zawierać jedną lub więcej tabel, a także wstępnie zagregowane dane. Indeksy złączeń służą głównie do poprawiania wydajności.

Dostępne są różne typy indeksów złączeń.

• Indeks łączenia pojedynczej tabeli (STJI)
• Wskaźnik łączenia wielu tabel (MTJI)
• Aggregated Join Index (AJI)

Indeks łączenia pojedynczej tabeli

Indeks łączenia pojedynczej tabeli umożliwia podzielenie dużej tabeli na podstawie innych kolumn indeksu podstawowego niż ta z tabeli podstawowej.

Składnia

Poniżej znajduje się składnia JOIN INDEX.

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

Przykład

Weź pod uwagę poniższe tabele pracowników i wynagrodzeń.

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

Poniżej znajduje się przykład, który tworzy indeks Join o nazwie Employee_JI w tabeli Employee.

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

Jeśli użytkownik wyśle ​​zapytanie z klauzulą ​​WHERE w polu EmployeeNo, system wyśle ​​zapytanie do tabeli Employee przy użyciu unikalnego indeksu podstawowego. Jeśli użytkownik zapyta tabelę pracowników używając nazwa_pracownika, wtedy system może uzyskać dostęp do indeksu dołączenia Employee_JI używając Employee_name. Wiersze indeksu złączenia są haszowane w kolumnie nazwa_pracownika. Jeśli indeks łączenia nie jest zdefiniowany, a nazwa_pracownika nie jest zdefiniowana jako indeks pomocniczy, system wykona pełne skanowanie tabeli w celu uzyskania dostępu do wierszy, co jest czasochłonne.

Możesz uruchomić następujący plan EXPLAIN i zweryfikować plan optymalizacji. W poniższym przykładzie widać, że optymalizator używa indeksu łączenia zamiast podstawowej tabeli Employee, gdy tabela wysyła zapytanie za pomocą kolumny 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.

Indeks łączenia wielu tabel

Indeks łączenia wielu tabel jest tworzony przez połączenie więcej niż jednej tabeli. Indeks łączenia wielu tabel może służyć do przechowywania zestawu wyników często łączonych tabel w celu poprawy wydajności.

Przykład

Poniższy przykład tworzy JOIN INDEX o nazwie Employee_Salary_JI, łącząc tabele Employee i 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);

Za każdym razem, gdy aktualizowane są podstawowe tabele Pracownik lub Wynagrodzenie, automatycznie aktualizowany jest również indeks Dołącz do Employee_Salary_JI. Jeśli uruchamiasz zapytanie łączące tabele pracowników i wynagrodzeń, optymalizator może wybrać bezpośredni dostęp do danych z Employee_Salary_JI zamiast dołączać do tabel. Plan EXPLAIN zapytania może być użyty do sprawdzenia, czy optymalizator wybierze tabelę bazową lub indeks sprzężenia.

Indeks łącznych połączeń

Jeśli tabela jest konsekwentnie agregowana w określonych kolumnach, można zdefiniować indeks łączenia zagregowanego w tabeli, aby poprawić wydajność. Jednym z ograniczeń indeksu łączenia zagregowanego jest to, że obsługuje on tylko funkcje SUMA i COUNT.

Przykład

W poniższym przykładzie pracownik i wynagrodzenie są łączone, aby określić łączne wynagrodzenie na dział.

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