- Replicates all, or a vertical subset, of a single base table and partitions its rows using a different primary index (or a different column partitioning if the base table is a NoPI column-partitioned table) than the base table, such as a foreign key column to facilitate joins of large tables by hashing the tables to the same AMP.A partitioning expression for a row-partitioned join index cannot contain a row-level security constraint column.
- Joins multiple tables (optionally with aggregation) in a prejoin table.
- Aggregates one or more columns of a single table as a summary table.
Join indexes are updated automatically, so the only administrative task a DBA must perform is to keep the statistics on multitable join index columns and their indexes current. For non-sparse single-table join indexes, the best policy is to use base table statistics rather than to collect statistics directly on the columns of the index.
The recommended practice for recollecting statistics is to set appropriate thresholds for recollection using the THRESHOLD options of the COLLECT STATISTICS (Optimizer Form) statement.
You cannot collect statistics on complex expressions specified in a base table definition. However, if you frequently submit queries that specify complex base table predicate expressions, you can create a single-table join index that specifies those frequently used predicate expressions in its select list or column list, respectively, and then collect statistics on the expression defined as a simple column in your index.
- The case where an EXTRACT expression specified in a query predicate can be matched with a join index predicate.
- The case where an EXTRACT/DATE expression specified in a query predicate condition can be mapped to an expression specified in the select list of a join index.
The Optimizer uses expression mapping for an identical query expression or a matching query expression subset within a nonmatching predicate. In this situation, the Optimizer maps the predicate to the identical column of the join index, which enables the Optimizer to use the statistics collected on the join index column to estimate the cardinality of the expression result.
When you create a single-table join index that specifies a complex expression, Vantage transforms the expression into a simple join index column. This enables the Optimizer to map the statistics collected on those complex expressions to the base table to facilitate single-table cardinality estimates or to match the predicates (see Using Join Index Statistics to Estimate Single-Table Expression Cardinalities for details).
The derived statistics framework supports bidirectional inheritance of statistics between a nonsparse single-table join index and its base table (see Deriving Column Demographics ), so the entity on which statistics are collected is less important than in the past.