Define a simple join index as a join index that does not specify aggregate operations.
The primary function of a simple join index is to provide the Optimizer with a high-performing, cost-effective means for satisfying any query that specifies a frequently performed join operation. The simple join index permits you to define a permanent prejoin table without violating schema normalization.
You can also define a simple join index on a single table. This enables you to hash a subset of the columns of a large base table on a foreign key that hashes rows to the same AMP as another large table to which it is frequently joined. This is most commonly done by projecting a proper subset of the columns from the base table into a single-table join index, which is often called vertical partitioning. This is not related to the range partitioning performed with a partitioned join index. But if your design requires it, you can also project all columns from the base table into a second physical table that differs from its underlying base table only in the way its rows are hashed to the AMPs.
This may be more high-performing than building a multitable join index on the same columns due to less internal update maintenance on the single table form of the index.
Only prototyping can determine which is the better design for a given set of tables, applications, and hardware configuration.
- Determine whether the partitioning for the join index must be a nonpartitioned, row-partitioned, column-partitioned, or column-partitioned with a mix of row partitioning.
If you decide to define the index using partitioning, determine whether that partitioning must be a single-level or multilevel.
- Define a join index on the frequently joined columns of the table to be distributed on a new partitioning.
- Define a column_1_name for each column_name in the primary base table to be included in the single-table join index.
Include the keyword ROWID as a value in the column_name list to enable the Optimizer to join a partial covering index to its base table to access any non-covered columns. You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement.
Even though you do not explicitly specify this join when you write your query, it counts against the 64 table restriction on joins.
- For a primary-indexed join index, define the primary index on the column set on which the join is made. This is typically a foreign key from the table to which the index table is to be joined.
- If performance suggests it, use CREATE INDEX to create one or more NUSIs on the join index.
- If performance suggests it, collect the appropriate statistics on the join columns, the indexes, and the ORDER BY column. In most applications, collect statistics on the base table columns on which the index is defined rather than on the index columns themselves. See Collecting Statistics on a Single-Table Join Index and Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns for clarification.
Outer Joins and Simple Join Indexes
Because join indexes generated from inner joins do not preserve unmatched rows, consider using outer joins to define simple join indexes. This practice empowers the join index to satisfy queries with fewer join conditions than those used to generate the index.
You can define a join index using both left and right outer joins, but full outer joins are prohibited.
When you define a join index using an outer join, you must reference all the columns of the outer table in the select list of the join index definition. If any of the outer table columns are not referenced in the select list for the join index definition, the system returns an error to the requestor.
See Rules and Restrictions for Query Coverage in Join Indexes for considerations about how to define outer joins to maximize the coverage possibilities of a join index.