Chapter 10
Join Indexes
“A wise man thinks it more advantageous not to join the battle than to win.”
Table of Contents Chapter 10 – Join Indexes
– Creating a Multi-Table Join Index
– Outer Join Multi-Table Join Index
– Visual of a Left Outer Join Index
– Compressed Multi-Table Join Index
– A Visual of a Compressed Multi-Table Join Index
– Creating a Single-Table Join Index
– Conceptual of a Single Table Join Index on an AMP
– Compressed Single-Table Join Index
– A Global Multi-Table Join Index
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.
A 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) ;
We've duplicated the Employee_Table with a different Primary Index.
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 won't 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.
Compressed Single-Table Join Index
We've duplicated the Employee_Table with a different Primary Index.
This is the compressed version of a Single-Table Join Index. Notice the brackets 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;
Only Sum and Count can be used with an Aggregate Join Index.
Users never query the Join Index directly. You can extract the Month and Year. Calculations are updated as the Base Table changes. Count and Sum are required to be a data type of FLOAT.
Sparse Join Index
A Sparse Join Index is a Join Index with a WHERE Clause!
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 Users 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