The cost of creating a join index is described in terms of time:
- How much time is needed to create the index?
- How often must a query for which a join index was designed be performed to offset the creation time by recovering response time?
Different types of join index require a longer or shorter time to create.
If your processing environment requires that join indexes be created and dropped regularly, the cost of creating the index is a critical factor in determining its worth. If you must drop and create your join indexes frequently, evaluate their benefit with respect to their creation cost. You may not want to create join indexes whose effect is negative with respect to overall system performance.
Unfortunately, join index creation times cannot be accurately predicted, because of the number of variables involved. This multivariate complexity also prevents the derivation of a useful predictive model for maintenance overhead or even for query time reductions.