15.00 - Computing the Benefits of Join Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Computing the Benefits of Join Indexes

This topic describes a measure you can use to calculate the benefit of a join index.

Begin the calculation by computing some preliminary measures, as described in the following set of equations.

Define the benefit of a join index as follows:

where:

 

Syntax element …

Specifies elapsed query time …

ETwo

without a join index defined for the query.

ETw

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 just a normalized form of the raw benefit. It provides 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.