Teradata - Índice JOIN

JOIN INDEX é uma visão materializada. Sua definição é armazenada permanentemente e os dados são atualizados sempre que as tabelas base referenciadas no índice de junção são atualizadas. JOIN INDEX pode conter uma ou mais tabelas e também conter dados pré-agregados. Os índices de junção são usados ​​principalmente para melhorar o desempenho.

Existem diferentes tipos de índices de junção disponíveis.

• Índice de junção de tabela única (STJI)
• Índice de junção de múltiplas mesas (MTJI)
• Índice de junção agregado (AJI)

Índice de junção de mesa única

O índice Single Table Join permite particionar uma grande tabela com base nas diferentes colunas de índice primário do que a da tabela base.

Sintaxe

A seguir está a sintaxe de um JOIN INDEX.

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

Exemplo

Considere as seguintes tabelas de funcionários e salários.

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

A seguir está um exemplo que cria um índice de junção denominado Employee_JI na tabela Employee.

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

Se o usuário enviar uma consulta com uma cláusula WHERE em EmployeeNo, o sistema consultará a tabela Employee usando o índice primário exclusivo. Se o usuário consultar a tabela de funcionários usando nome_de_funcionário, então o sistema pode acessar o índice de junção Employee_JI usando nome_de_funcionário. As linhas do índice de junção são hash na coluna employee_name. Se o índice de junção não estiver definido e o employee_name não estiver definido como índice secundário, o sistema executará uma varredura completa da tabela para acessar as linhas, o que é demorado.

Você pode executar o seguinte plano EXPLAIN e verificar o plano otimizador. No exemplo a seguir, você pode ver que o otimizador está usando o índice de junção em vez da tabela base Employee quando a tabela consulta usando a coluna 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.

Índice de junção de múltiplas mesas

Um índice de junção de várias tabelas é criado juntando mais de uma tabela. O índice de junção de várias tabelas pode ser usado para armazenar o conjunto de resultados de tabelas frequentemente associadas para melhorar o desempenho.

Exemplo

O exemplo a seguir cria um JOIN INDEX denominado Employee_Salary_JI unindo as tabelas 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);

Sempre que as tabelas base Employee ou Salary são atualizadas, o índice Join Employee_Salary_JI também é atualizado automaticamente. Se você estiver executando uma consulta juntando as tabelas Employee e Salary, o otimizador pode escolher acessar os dados de Employee_Salary_JI diretamente em vez de juntar as tabelas. O plano EXPLAIN na consulta pode ser usado para verificar se o otimizador escolherá a tabela base ou o índice de junção.

Índice de junção agregado

Se uma tabela for agregada de forma consistente em certas colunas, o índice de junção agregado pode ser definido na tabela para melhorar o desempenho. Uma limitação do índice de junção agregado é que ele suporta apenas as funções SUM e COUNT.

Exemplo

No exemplo a seguir, Funcionário e Salário são unidos para identificar o salário total por Departamento.

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