15.00 - Using Global Temporary and Volatile Tables to Enhance Performance - Teradata Database

Teradata Database Design

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

Using Global Temporary and Volatile Tables to Enhance Performance

You can use global temporary tables to enhance performance in the following ways:

  • Simplify application code
  • Reduce spool usage
  • Eliminate large numbers of joins
  • This final point is important as an alternative for applications that do not require persistent storage of prejoin results as offered, for example, by join indexes.

    Example : Simple Denormalization for Batch Processing

    The following global temporary table serves 500 different transactions that create the output it defines. These transactions collectively run over one million times per year, but 95% of them run only on a monthly batch schedule.

    With the following table definition stored in the dictionary, the table itself, which violates 2NF, is materialized only when one of those batch transactions accesses it for the first time in a session:

     

    Example : Aggregate Summary Table

    The following global temporary table definition, if used infrequently and is not shared, might be an alternative to using an aggregate join index to define the equivalent summary table:

     

    Example : Prejoin

    Prejoins are a form of derived relationship among tables. The following table definition, if used infrequently, might be an alternative to using a join index to define the equivalent prejoin table.

    This particular table saves the cost of having to join the Order, Location, and Customer tables: