O'Reilly logo

Tera-Tom's 1000 Page e-Book on Teradata Architecture and SQL, 2nd Edition by Tom Coffing, William Coffing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required