Global Temp Tables with Tactical Applications?
I know of a Teradata site that chose to use global temporary tables (GTTs) with their tactical queries in an attempt to get good performance against their fact table. However, there are some known limitations in doing that which I want to share with you. Read this posting to find out why mixing GTTs and tactical queries should not be your first choice.
What is a Global Temporary Table?
A GTT is a structure that is defined once in the data dictionary. Any number of sessions can insert into and reference their own local copy of the global table structure. Volatile temp tables (VTT), by way of contrast, are created and used only within the context of a single session, and are never written to the dictionary.
Be careful of using GTTs with tactical queries because the query plans that they produce will not be able to be re-used by queries from other sessions. In other words, you’ll never get cached plans. This is true even when the SQL that references them is in a macro and you have parameterized the SQL correctly.
Cached plans are a key performance enabler for tactical applications, as they allow short, time-sensitive requests to side-step most parsing engine activity. Parsing and optimizing CPU for very short queries is sometimes larger than the AMP CPU to execute the request. Tactical queries perform more consistently when their plans have been cached.
Why GTT Plans Are Not Cached
You won’t get cached plans when using GTTs because each session will have a different table ID for the GTT, and that table ID is not known until the query is parsed. One prerequisite of getting a re-usable, cacheable query plan, is that all table IDs in the query must be consistent across all executions. This will never be true of tables that reference global temporary tables.
In addition, there is slightly more overhead when GTTs execute than would be the case with VTTs or regular permanent tables. The DBC.Next table must be processed by the first query in a session that uses a GTT, in order to get the table ID that that session will utilize. Since DBC.Next has only one row, access from multiple sessions will be single-threaded. VTTs, however, have no need to go to DBC.Next table to get a table ID, as they use Spool ID instead, and Spool ID is available locally.