Simple Join Indexes
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 permits 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 referred to as vertical partitioning (note that the partitioning here has nothing to do with the sort of range partitioning performed with a partitioned join index), but if your design requires it, you can also project all of the 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.
In some situations, this is more high-performing than building a multitable join index on the same columns. The advantage comes from 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.
The following describes a general procedure for defining a single-table join index:
1 Determine whether the partitioning for the join index should be an unpartitioned, 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 should be a single‑level or multilevel.
2 Define a join index on the frequently joined columns of the table to be distributed on a new partitioning.
3 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.
4 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.
5 If performance suggests it, use CREATE INDEX to create one or more NUSIs on the join index.
6 If performance suggests it, collect the appropriate statistics on the join columns, the indexes, and the ORDER BY column. In most applications, you should 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” on page 376 and “Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns” on page 377 for clarification.