Join indexes are not indexes in the usual sense of the word. Join indexes are file structures designed to permit queries (join queries in the case of multitable join indexes) to be resolved by accessing the index instead of having to access and join their underlying base tables.
- Define a prejoin table on frequently joined columns (with optional aggregation) without denormalizing the database.
- Create a full or partial replication of a base table with a primary index on a foreign key column table to facilitate joins of large tables by hashing their rows to the same AMP as the large table.
- Define a summary table without denormalizing the database.
You can define a join index on one or more tables.
Join indexes can be useful for queries where the index structure does not contain all columns referenced in the statement. This situation is called a partial cover of the query.
Unlike traditional indexes, join indexes do not implicitly store pointers to their associated base table rows. Join indexes are used as a quick access method that eliminates the need to access and join their base tables. Join indexes substitute fo,r rather than point to, base table rows. The exception is when an index partially covers a query. If the index is defined using the ROWID keyword or the UPI or USI of its base table as a column, the index can be used to join with the base table to cover the query.