Teradata - индекс JOIN

JOIN INDEX - это материализованное представление. Его определение постоянно хранится, и данные обновляются всякий раз, когда обновляются базовые таблицы, указанные в индексе соединения. JOIN INDEX может содержать одну или несколько таблиц, а также предварительно агрегированные данные. Индексы соединения в основном используются для повышения производительности.

Доступны различные типы индексов соединения.

• Индекс объединения одной таблицы (STJI)
• Индекс соединения нескольких таблиц (MTJI)
• Агрегированный индекс соединения (AJI)

Индекс объединения одной таблицы

Индекс объединения одной таблицы позволяет разделить большую таблицу на основе столбцов первичного индекса, отличных от столбцов базовой таблицы.

Синтаксис

Ниже приведен синтаксис JOIN INDEX.

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

пример

Рассмотрим следующие таблицы сотрудников и зарплат.

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

Ниже приведен пример создания индекса соединения с именем Employee_JI в таблице Employee.

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

Если пользователь отправляет запрос с предложением WHERE для EmployeeNo, то система будет запрашивать таблицу Employee, используя уникальный первичный индекс. Если пользователь запрашивает таблицу сотрудников с помощью employee_name, то система может получить доступ к индексу соединения Employee_JI с помощью employee_name. Строки индекса объединения хешируются в столбце employee_name. Если индекс соединения не определен, а имя сотрудника не определено как вторичный индекс, система выполнит полное сканирование таблицы для доступа к строкам, что отнимает много времени.

Вы можете запустить следующий план EXPLAIN и проверить план оптимизатора. В следующем примере вы можете видеть, что оптимизатор использует индекс соединения вместо базовой таблицы сотрудников, когда таблица запрашивает с использованием столбца 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.

Индекс объединения нескольких таблиц

Индекс соединения нескольких таблиц создается путем объединения более чем одной таблицы. Индекс соединения нескольких таблиц можно использовать для хранения результирующего набора часто соединяемых таблиц для повышения производительности.

пример

В следующем примере создается JOIN INDEX с именем Employee_Salary_JI путем объединения таблиц Employee и 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);

Каждый раз, когда обновляются базовые таблицы Employee или Salary, индекс соединения Employee_Salary_JI также обновляется автоматически. Если вы выполняете запрос, соединяющий таблицы Employee и Salary, тогда оптимизатор может выбрать доступ к данным из Employee_Salary_JI напрямую, а не присоединяться к таблицам. План EXPLAIN в запросе можно использовать для проверки того, выберет ли оптимизатор базовую таблицу или индекс соединения.

Агрегированный индекс соединения

Если таблица последовательно агрегируется по определенным столбцам, то для повышения производительности в таблице можно определить индекс агрегированного соединения. Одним из ограничений индекса агрегированного соединения является то, что он поддерживает только функции SUM и COUNT.

пример

В следующем примере "Сотрудник" и "Заработная плата" объединяются для определения общей заработной платы по отделам.

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