Functions of Single-Table Join Indexes
Even though each single-table join index you create partly or entirely replicates its base table, you cannot query or update them directly just as you cannot directly query or update any other join index.
When you have an application for which join queries against a base table would benefit from replicating some or all of its columns in a different table hashed on the join key (usually the primary index of the table to which it is to be joined) rather than the primary index of the original base table, then you should consider creating one or more single-table join indexes on that table.
For example, you might want to create a single-table join index to avoid redistributing a large base table or to avoid the sometimes prohibitive storage requirements of a multitable join index. A single-table join index might be useful for commonly made joins having low predicate selectivity but high join selectivity, for example.
This strategy substitutes the join index for the underlying base table and defines a primary index that ensures that rows containing only the columns to be joined are hashed to the same AMPs, eliminating the need to redistribute rows when the database manager joins the tables.
As another example, suppose you have a primary index defined on a major entity column that joins with many foreign key subentity columns. The cost of the maintenance required to update a multitable join index defined on this table is many times greater than the cost of maintaining the underlying base table.
The Optimizer can use unique single‑table join indexes to access base table rows.
When you have a table with a large number of columns that is queried frequently, but only on a small subset of those columns, you can create either a hash index or a single-table join index to effectively partition the table vertically. Partitioning the rows of a table, as Teradata Database does to distribute rows to the AMPs, is often called horizontal partitioning. This is not what a single‑table join index or hash index does. Instead, those indexes effectively partition tables on their columns, a method referred to as vertical partitioning. For example, for a table with 1,500 columns, only 25 of which are frequently queried, you could create a hash or single‑table join index on those 25 frequently queried columns, which has the same effect as vertically partitioning the base table itself into two sets of columns: one set of 25 frequently queried columns and another set of 1,475 infrequently queried columns. Note that neither horizontal partitioning nor vertical partitioning is related in any way to how Teradata Database partitions the rows of a table having a partitioned primary index on an AMP, and that is why the terms horizontal partitioning and vertical partitioning are generally avoided in this book.
With a hash or single-table join index available that contains all of the frequently queried columns from the base table (and in the case of a join index, either the ROWID key word, the unique primary index of the base table, or a USI from the base table), the Optimizer can use that index to cover queries on that column subset, and then join to the base table to pick up any additional columns from the table that a query might specify in its select list.
You can also use single‑table join indexes as a mechanism to collect statistics on complex expressions that are defined in their select list. The Optimizer can then either use mapping to exploit a matched expression that it finds in a non‑matching predicate by mapping to the join index column statistics, or it can use matching when it detects identical predicates in both the join index definition and in a query made against the base table on which the join index is defined. See SQL Request and Transaction Processing for details.