Global temporary tables have a persistent stored definition just like any base table. The difference is that a global temporary table is materialized only when it is accessed by a DML request for the first time in a session and then remains materialized for the duration of the session unless explicitly dropped. At the close of the session, all rows in the table are dropped. Keep in mind that the containing database or user for a global temporary table uses PERM space to contain the table header on each AMP.
Analogously, volatile tables can have a persistent stored definition if that definition is contained within a macro. When used in this manner, the properties of global temporary and volatile tables are largely identical in regard to persistence of the definition (see “CREATE TABLE” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for other distinctions and differences).
Global temporary tables, like join and hash indexes, are not part of the logical model. Because of this, they can be denormalized to any degree desired, enhancing the performance of targeted applications without affecting the physically implemented normalization of the underlying database schema. The logical model is not affected, but all the benefits of physical schema denormalization are accrued.
It is important to remember that a materialized instance of a global temporary table and a volatile table are local to the session from which they are materialized or created, and only that session can access its materialized instance.
This also means that multiple sessions can simultaneously materialize instances of a global temporary table definition (or volatile tables) that are private to those sessions.
Using Global Temporary Tables and Volatile Tables to Avoid Denormalization
- Prejoins
- Summary tables and other derived data
This final point is important as an alternative for applications that do not require persistent storage of summary results as offered, for example, by aggregate join indexes.
Using Global Temporary and Volatile Tables to Enhance Performance
- 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:
| DeptNum | EmpNum | DeptName | LastName | FirstName |
|---|---|---|---|---|
| PK | ||||
| FK | FK | |||
| … | … | … | … | … |
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:
| DeptNum | Period | SumSalary | AvgSalary | EmpCount |
|---|---|---|---|---|
| PK | ||||
| FK | ||||
| NUPI | ||||
| ... | … | … | … | … |
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:
| OrdNum | CustNum | OrdCost |
|---|---|---|
| PK | FK | |
| FK | NUPI | |
| ... | … | … |