Social Icons

Join Index

TeradataWiki-Teradata 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.
TeradataWiki-Teradata Single-Table 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.
TeradataWiki-Teradata Multi-Table Join index

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

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

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.
TeradataWiki-Teradata Sparse Join Index
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.
TeradataWiki-Teradata Hash Indexes
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.

3 comments: