This topic describes a measure you can use to calculate the benefit of a join index.
Begin the calculation by computing preliminary measures, as described in the following set of equations.
Define the benefit of a join index as follows:
Benefit = ET wo– ET w
- ET wo specifies elapsed query time without a join index defined for the query.
- ET w specifies elapsed query time with a join index defined for the query.
Benefit is a simple measure that measures the advantage of a query in terms of the difference between its elapsed completion time when a join index is not defined and its elapsed completion time when a join index is defined.
Define the benefit percentage as follows:
The benefit percentage for a query is a normalized form of the raw benefit, whichprovides an easily understood measure of the reduction in processing time gained by creating the join index.
Rearrange terms to determine the elapsed time to process a query with the join index defined. This measure is necessary to determine if the cost of creating the join index exceeds its usefulness in reducing response time.
