For a single-table join index, the maintenance cost is roughly double the cost of maintaining the base table.
When you design a schema, there are often some tables that are queried in a way that, for some frequently run workloads, the table is joined on one column, but for another important query, the table is joined on another column. The usual design solution is to distribute the rows of this table on the column that is most frequently used in a join. If there is more than one column, then a join index might be a good design choice. A join index can be used to redistribute the table on the secondary join attribute so that joins can be done without a redistribution step.
Join indexes can also be used to evaluate parameterized queries. For the Optimizer to use a join index in this situation, the query must also contain a non-parameterized condition in its WHERE clause that the join index covers.
For example, suppose you create the following base table and single-table join index:
CREATE TABLE tp1 ( pid INTEGER, name VARCHAR(32), address VARCHAR(32), zipcode INTEGER); CREATE JOIN INDEX tp1_ji AS SELECT pid, name, zipcode FROM tp1 WHERE zipcode >50000 AND zipcode < 55000;