Example of Computing a Payback Factor for an Aggregate Join Index - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Example of Computing a Payback Factor for an Aggregate Join Index

In the scenario supporting this example, a 30 day period is used for the analysis. You do not regularly create and drop join indexes in your production environment, but you want to calculate the cost of creating the index.

The query to be supported by the join index joins two tables and uses aggregation. The join is made on the primary index of both tables. This is the definition of an in-place aggregate join index.

The 2‑node production system used to generate these numbers had the following general configuration.

  • Two 550 MHz 4-processor nodes with 1 MB cache and 2 GB memory per node
  • RAID1 (mirrored) disk arrays with a segment size of 968
  • BYNET 2
  • 20 AMPs, each with 2 LUNs (4 physical disks)
  • 4 network-attached PEs
  • The following table indicates the demographics of your data.


                                       Data Characteristic

            Table A

            Table B

    Row length (bytes)



    450 million rows

    600 million rows

    Number of rows for each value in the aggregating column


    Number of rows updated weekly


    Not applicable

    Data block size

    64 KB

    The join indexes were created on base tables, A and B with the following characteristics.

  • Row size of 100 bytes (except where row size is manipulated as a variable)
  • Initial block size of 65,024 bytes
  • Base and join index table cardinalities

       Type of Join          Index

     Cardinality of Join Index            Table (Rows)

    Cardinality of Base Table            100A (Rows)

    Cardinality of Base Table            100B (Rows)


    45 x 106

    45 x 106

    60 x 106


    45 x 102

    The following table lists the tunable parameter settings used on the test system throughout the test cycle.





    ReadAhead flag setting


    Disk I/O

    DBS Don't Cache flag setting


    Use DBC Cache Threshold setting

    DBC Cache Threshold


    Default setting

    FSG Cache Percent


    Default setting

    RSS logging settings

  • SPMA
  • IPMA
  • SVPR
  • 15 second logging interval

    Datablock size

    64 KB


    Free Space Percent



    Mini Cyl Pack

    10 cylinders

    Low Cylinder

    How many times must the query be performed to recover the cost in the first month? Here is the procedure.

    1 Estimate the benefit.

    The benefit should be approximately one order of magnitude greater than the figure generated by this study because there are that many more rows in the base tables, while all other factors are identical.

    Examine the table in the topic “Creation and Elapsed Query Times for Different Join Indexes” on page 474. The measured benefit is 106 seconds. Multiply this by 10 to produce the estimated benefit for your configuration, which is 1060 seconds.

    2 Estimate the cost of creation.

    Examine the table in the topic “Creation and Elapsed Query Times for Different Join Indexes” on page 474. The cost of creation for an in-place aggregate join index is seen to be 135 seconds. Scale this value up by an order of magnitude for the same reason as Step 1.

    The estimated cost of creation is 1350 seconds.

    3 Estimate the approximate number of hits per block.

    You update 900,000,100-byte rows in a 450 million row table weekly.

    Calculate the number of blocks these 450 million 100-byte rows occupy. The equation is as follows.

    Determine the approximate hit rate. With approximately 700,000 blocks and 900,000 rows to be updated weekly, the hit rate is determined as follows.

    4 Determine the update cost.

    In a 30 day period, the table will be updated four times.

    Suppose that the cost of this update is 151 seconds for the test system.

    Scale this number up by an order of magnitude and multiply it by four weeks to obtain the approximate update cost.

    5 Calculate the payback factor.

    If the query for which this join index was designed is run twice weekly, it recovers the time spent creating and maintaining it.