Chapter 14 - Join Indexes

“Every sunrise is a second chance”

– Unknown

Creating a Multi-Table Join Index

CREATE JOIN INDEX EMP_DEPT_IDX AS
SELECT Employee_No
      ,E.Dept_No
      ,First_Name
      ,Last_Name
      ,Salary
      ,Department_Name
FROM Employee_Table as E
INNER JOIN
       Department_Table as D
ON    E.Dept_No = D.Dept_No
PRIMARY INDEX (Employee_No) ;

 

The Syntax above will create a Multi-Table Join Index with a NUPI on Employee_No. The next slide will illustrate a visual so you can see the data in the Join Index. Join Indexes are created so data doesn't have to move to satisfy the join. The Join Index essentially pre-joins the table and keeps it hidden for the Parsing Engine to utilize.

Visual of a Join Index

The Join Index looks like an Answer Set, but each ...

Get Tera-Tom's 1000 Page e-Book on Teradata Architecture and SQL, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.