Even though each single-table join index you create partly or entirely replicates its base table, you cannot query or update them directly.
When you have an application for which join queries against a base table 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, 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 makes sure 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 a single-table join index to effectively partition the table vertically. Partitioning the rows of a table, as Vantage does to distribute rows to the AMPs, is often called horizontal partitioning. This is not what a single-table join index does. Instead, those indexes effectively partition tables on their columns, a method called vertical partitioning. For example, for a table with 1,500 columns, only 25 of which are frequently queried, you can create a 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. Neither horizontal partitioning nor vertical partitioning is related in any way to how Vantage 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 avoided in this document.
With a single-table join index available that contains all of the frequently queried columns from the base table (and 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. For more information, see Using Join Index Statistics to Estimate Single-Table Expression Cardinalities .