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 row is stored like a normal table in that the rows of the Join Index are spread amongst the AMPs. Users can't query the Join Index, but the Parsing Engine gets data from the Join Index when it chooses.

Outer Join Multi-Table Join Index

A Multi-Table Outer Join Index has some very specific rules above to remember. Turn the page to see a visual of the data.

Visual of a Left Outer Join Index

The Outer Join Index has the additional rows that did NOT match.

Compressed Multi-Table Join Index

  CREATE JOIN INDEX Cust_Order_IDX AS
   SELECT
   (C.Customer_Number, Customer_Name),
   (Order_Number, Order_Date, Order_Total)
   FROM Customer_Table as C
   INNER JOIN
         Order_Table as O
   ON    C.Customer_Number =
         O.Customer_Number
   PRIMARY INDEX (Customer_Number) ;

A Compressed Multi-Table Join Index won't keep repeating the same Customer_Number and Customer_Name, but only list it once. A visual example follows on the next page.

Visual of a Compressed Multi-Table Join Index

Billy's Best Choice is Customer_Number 11111111 and they have placed two orders, but the Customer_Number and Customer_Name don't repeat unnecessarily.

Creating a Single-Table Join Index

   CREATE JOIN INDEX Employee_IDX
   AS
   SELECT Employee_No
       ,Dept_No
       ,First_Name
       ,Last_Name
       ,Salary
 FROM Employee_Table
 PRIMARY INDEX (Dept_No) ;

If a USER queries with the Dept_No in the WHERE clause this will be a Single-AMP retrieve. If the USER joins the Employee and Department Tables together then Teradata wont need to Redistribute or Duplicate to get the data AMP local. The next page will give you a visual of how that looks on a particular AMP.

Conceptual of a Single Table Join Index on an AMP

Notice the Primary Indexes on both tables and the Single Table Join Index. The Join Index gives the Parsing Engine options. If a query is run against the Employee_Table with Employee_No in the WHERE clause, it will use the normal table, but if a user Uses Dept_No in the WHERE clause, it will use the Join Index. If a user needs to join the Department_Table to the Employee_Table, the Join Index is used so no data moves.

Single Table Join Index Great For LIKE Clause

Build a STJI with column that contains three columns. They are the LIKE column being queried, the primary index column of the base table and the keyword ROWID!

The PE will choose to scan the narrow table (the Join Index) and qualify all rows that qualify with a car license LIKE “TeraT%”, then the PE uses the ROWID to get data from the BMV_Table where row is on the same AMP because both the base table and join index are on the same AMP because they both have the same Primary Index. This can save enormous time for queries using the LIKE command.

A LIKE command on a base table will never use a Non-Unique Secondary Index (NUSI). The above technique should be tested and only used if a lot of users are utilizing the LIKE command on a large table. If that is the case a lot of time can be saved.

Single Table Join Index with Value Ordered NUSI

A Value Ordered NUSI can only be done on columns that are 4-byte integers. Dates qualify because they are stored internally in Teradata as 4-byte integers.

  CREATE JOIN INDEX OrdersJI AS
  Select * from Order_Table
  Primary Index(Customer_Number) ;

  Create Index (Order_Date) order by values
  on OrdersJI;

A value ordered index has been expanded from 16 to 64 columns.

Indexes are always sorted by their hash, but a Value Ordered index is sorted on each AMP by values and not hash. This is a great technique for you to run trials on.

Aggregate Join Indexes

Aggregate Join Indexes may be defined on:

Single Tables -A columnar subset of a base table with aggregates automatically maintained by Teradata.

Multiple Tables -A columnar subset of as many as 64 base tables with aggregate columns automatically maintained by Teradata.

Sparse Join Indexes are defined with a WHERE clause that limits the number of base table rows included and the space required to store them.

Aggregate Join Indexes can only include SUM and COUNT values. You can calculate Averages from these two columns though.

Compressed Single-Table Join Index

This is the compressed version of a Single-Table Join Index. Notice the parentheses around Customer_Number in the SELECT list. A single row is used for each customer, with repeating orders per customer inside the Join Index.

Aggregate Join Index

  CREATE JOIN INDEX Agg_Order_IDX AS
  SELECT
     Customer_Number
    ,Extract(Year from Order_Date) As Yr
    ,Extract(Month from Order_Date) As Mon
    ,Count(*) as County
    ,Sum(Order_Total) as Summy
  FROM Order_Table
  Group by 1, 2, 3;

Users never query the Join Index directly. It is the Parsing Engine that commands the AMPs to pull the data from the Join Index. You can extract the Month and Year and calculations are updated as the Base Table changes. Count and Sum are required to be a data type of FLOAT. Why can you only have Count and Sum? Max and Min aren't that important to know and Average isn't all that important either. Business users want to know how much and how many so AVERAGE, MIN, and MAX are excluded!

New Aggregate Join Index (Teradata V14.10)

  CREATE JOIN INDEX Agg_Order_IDX AS
  SELECT
     Customer_Number
     ,Extract(Year from Order_Date) As Yr
     ,Extract(Month from Order_Date) As Mon
	 ,Count(*) as County
     ,Sum(Order_Total) as Summy
     ,MAX (Order_Total) as Max_Ord
     ,MIN(Order_Total) as Min_Ord
   FROM Order_Table
   Group by 1, 2, 3;

Aggregate Join Index (AJI) has always supported (SUM and COUNT), but with Teradata V14.10, there is support for MIN/MAX aggregates also.

Sparse Join Index

A Sparse Join Index has a WHERE clause so it doesn't take all the rows in the table, but only a portion. This is a very effective way to save space and focus on the latest data.

A Global Multi-Table Join Index

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

With the ROWID inside the Join Index, the PE can get columns in the User's SQL NOT specified in the Join Index directly from the Base Table by using the Row-ID.

Creating a Hash Index

A Hash Index can be Ordered by Values or by Hash.

Join Index Details

•   Join Indexes are physically stored exactly like normal Teradata tables.

•   Users can't query the Join Index directly, but PE will decide when to use.

•   Join Indexes are automatically updated as base tables change.

•   Join Indexes can have Non-Unique Primary Indexes (NUPI) only.

•   Join Indexes can have Non-Unique Secondary (NUSI) Indexes.

•   Max 64 Columns per Table per Join Index.

•   BLOB and CLOB types cannot be defined.

•   Triggers with Join Indexes allowed V2R6.2.

•   After Restoring a Table, Drop and Recreate the Join Index.

•   FastLoad/MultiLoad won't load to tables with a Join Index defined.