- Eliminate base table access
- Eliminate aggregate processing
- Reduce joins
- Eliminate redistributions
- Eliminate Summary processing
- Single Table Join Index
- Multi-Table Join Index
- Multi-Table Compressed Join Index
- Aggregate Join Index
- Sparse Join Index
- Global Join Index
- Hash 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.
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.