Join Index

A Join Index is an optional index which may be created by a User. Join indexes provide additional processing efficiencies:

• Eliminate base table access
• Eliminate aggregate processing
• Reduce joins
• Eliminate redistributions
• Eliminate Summary processing

The following are wide variety of Join Index names. We will discuss each in clear detail.

1. Single Table Join Index
2. Multi-Table Join Index
3. Multi-Table Compressed Join Index
4. Aggregate Join Index
5. Sparse Join Index
6. Global Join Index
7. Hash Index

A Single-Table Join Index duplicates a single table, but changes the Primary Index. Users will only query the base table, but the Parsing Engine will use the Join Index.

The reason to create a Single-Table Join Index is so joins can be performed faster because no Redistributions or Duplication needs to occur.

A Multi-Table Join index is a Join Index that involves two or more tables.
Facilitates join operations by possibly eliminating join processing or by reducing/eliminating join data redistribution.

A Compressed Join Index is designed to save space by not REPEATING the repeating values

An Aggregate Join Index will allow tracking of the Aggregates SUM and COUNT on any table

A Sparse Join Index is a Join Index that doesn‘t use every row because it has a WHERE Clause. This is done to save space and time.

Hash Indexes are used similar to a Join Index, but Hash Indexes are maintained in AMP-Local tables and used to quickly find certain key columns in a base table.

Join Index Details:

• Max 64 columns per table per Join Index.
• BLOB and CLOB types cannot be defined.
• Triggers with Join Indexes are allowed V2R6.
• After Restoring a Table, Drop and Recreate the Join Index.
• Automatically updated as table changes.
• Fast load/Multi load wont load with them.
• can have NUPI and NUSI.
• Collect statistics on Primary and Secondary.