Join indexes are not indexes in the usual sense of the word. They 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.
You can use join indexes to:
You can define a join index on one or several tables.
Depending on how the index is defined, join indexes can also be useful for queries where the index structure contains only some of the columns referenced in the statement. This situation is referred to as a partial cover of the query.
Unlike traditional indexes, join indexes do not implicitly store pointers to their associated base table rows. Instead, they are generally used as a quick access method that eliminates the need to access and join the base tables they represent. They substitute for rather than point to base table rows. The only exception to this is the case where an index partially covers a query. If the index is defined using either the ROWID keyword or the UPI or USI of its base table as one of its columns, then it can be used to join with the base table to cover the query.
Defining Join Indexes
To create a join index, use the CREATE JOIN INDEX statement.
For example, suppose that a common task is to look up customer orders by customer number and date. You might create a join index like the following, linking the customer table, the order table, and the order detail table:
CREATE JOIN INDEX cust_ord2
AS SELECT cust.customerid,cust.loc,ord.ordid,item,qty,odate
FROM cust, ord, orditm
WHERE cust.customerid = ord.customerid
AND ord.ordid = orditm.ordid;
Multitable Join Indexes
A multitable join index stores and maintains the joined rows of two or more tables and, optionally, aggregates selected columns.
Multitable join indexes are for join queries that are performed frequently enough to justify defining a prejoin on the joined columns.
A multitable join index is useful for queries where the index structure contains all the columns referenced by one or more joins, thereby allowing the index to cover that part of the query, making it possible to retrieve the requested data from the index rather than accessing its underlying base tables. For obvious reasons, an index with this property is often referred to as a covering index.
Single-Table Join Indexes
Single-table join indexes are very useful for resolving joins on large tables without having to redistribute the joined rows across the AMPs.
Single-table join indexes facilitate joins by hashing a frequently joined subset of base table columns to the same AMP as the table rows to which they are frequently joined. This enhanced geography eliminates BYNET traffic as well as often providing a smaller sized row to be read and joined.
Aggregate Join Indexes
When query performance is of utmost importance, aggregate join indexes offer an extremely efficient, cost-effective method of resolving queries that frequently specify the same aggregate operations on the same column or columns. When aggregate join indexes are available, the system does not have to repeat aggregate calculations for every query.
You can define an aggregate join index on two or more tables, or on a single table. A single-table aggregate join index includes a summary table with:
Sparse Join Indexes
You can create join indexes that limit the number of rows in the index to only those that are accessed when, for example, a frequently run query references only a small, well known subset of the rows of a large base table. By using a constant expression to filter the rows included in the join index, you can create what is known as a sparse index.
Any join index, whether simple or aggregate, multitable or single-table, can be sparse.
To create a sparse index, use the WHERE clause in the CREATE JOIN INDEX statement.
Effects of Join Indexes
MultiLoad and FastLoad utilities cannot be used to load or unload data into base tables that have a join index defined on them because join indexes are not maintained during the execution of these utilities. If an error occurs because of a join index, take these steps:
The TPump utility, which performs standard SQL row inserts and updates, can be used to load or unload data into base tables with join indexes because it properly maintains join indexes during execution. However, in some cases, performance may improve by dropping join indexes on the table prior to the load and recreating them after the load.
Archive and recovery cannot be used on a join index itself. Archiving is permitted on a base table or database that has an associated join index defined. Before a restore of such a base table or database, you must drop the existing join index definition. Before using any such index again in the execution of queries, you must recreate the join index definition.
Using a permanent journal to recover a base table (that is, ROLLBACK or ROLLFORWARD) with an associated join index defined is permitted. The join index is not automatically rebuilt during the recovery process. Instead, it is marked as nonvalid and it must be dropped and recreated before it can be used again in the execution of queries.
Join Indexes and Base Tables
In most respects, a join index is similar to a base table. For example, you can do the following things to a join index:
Unlike base tables, you cannot do the following things with join indexes:
Note: You can maintain aggregates or sparse indexes if you define the join index to do so.
For more information on …
creating join indexes
“CREATE JOIN INDEX” in SQL Data Definition Language.
dropping join indexes
“DROP JOIN INDEX” in SQL Data Definition Language.
displaying the attributes of the columns defined by a join index
“HELP JOIN INDEX” in SQL Data Definition Language.
using join indexes to enhance the performance of your databases