Global Temporary Tables Teradata

Global Temporary Tables were also introduced in release V2R3 of Teradata. Their table and column definition is stored in the DD, unlike volatile tables. The first SQL DML statement to access a global temporary table, typically an INSERT/SELECT, materializes the table. They are often called global tables.

Like volatile tables, global tables are local to a session. The materialized instance of the table is not shareable with other sessions. Also like volatile tables, the global table instance may be dropped explicitly at any time or it is dropped automatically at the end of the session. However, the definition remains in the dictionary for future materialized instances of the same table. At the same time, the materialized instance or base definition may be dropped with an explicit DROP command, like any table.

The only privilege required to use a global table is the DML privilege necessary to materialize the table, usually an INSERT/SELECT. Once it is materialized, no other privileges are checked.

A special type of space called "Temporary space" is used for global temporary tables. Like Permanent space, Temporary space is preserved during a system restart and thus, global temporary tables are able to survive a system restart.

These global tables are created using the CREATE GLOBAL TEMPORARY TABLE command. Unlike the volatile table, this CREATE stores the base definition of the table in the DD and is only executed once per database. Like volatile tables, the table defaults are to LOG transactions and ON COMMIT DELETE ROWS. Up to 32 materialized instances of a global temporary table may exist for a single user.

Once the table is accessed by a DML command, such as the INSERT/SELECT, the table is considered materialized and a row is entered into a DD table called DBC.Temptables. An administrator may SELECT from this table to determine the users with global tables materialized and how many global tables exist.

Deleting all rows from a global table does not de-materialize the table. The instance of the table must be dropped or the session must be ended for the materialized table to be discarded.

The syntax to create a global temporary table follows:

This series of commands show how to create, insert, and select from a global temporary table:

The next INSERT will create one data row per department that has at least one employee in it:

Now that the global temporary table exists in the DD and it contains data rows, it is ready for use in a variety of SQL statements like the following:

SELECT * FROM Dept_Aggreg_gt ORDER BY 1; 6 Rows Returnedvariety of SQL statements
variety of SQL statements

It can immediately be used by other SELECT operations:

2 Rows Returnedimmediately be used by other SELECT operations

At this point, it is probably obvious that these examples are the same as those used for the volatile table except for the fact that the table name ends with "gt" instead of "vt." Volatile tables and global temporary tables are very much interchangeable from the user perspective. The biggest advantage to using the global temporary table lies in the fact that the table never needs to be created a second time. All the user needs to do is reference it with an INSERT/SELECT and it is automatically materialized with rows.

Therefore, when multiple users need the same definition, it is better to store it one time and give all users the INSERT privilege on it. It is the standard definition available to all users without requiring each user to run a CREATE statement and overcomes the main disadvantage of a volatile table. However, no user can access or disturb rows belonging to another user. They can only access their own rows due to each user session owning a different instance of the table.

Since the global temporary table's definition is stored in the DD, it may be altered using the ALTER command. It can change any attributes of the table, like real tables. Additionally, for extra flexibility, a materialized instance of the table may be altered without affecting the base definition or other user's materialized instance. Talk about flexibility.

This advantage means that a user is not restricted to having an identical definition as all other users. By using the ALTER TEMPORARY TABLE statement, the user can fine-tune the table for their specific needs, session by session.

Since a global temporary table can be altered and is not in spool space, this means that within an instance, it can take advantage of the following operations:

  • Add / Drop columns
  • Add / Drop attributes
  • Create / Drop indices
  • Collect Statistics

As an example, if someone did not wish to use the LOG option for his or her instance, the next ALTER could be used:

ALTER TEMPORARY TABLE Dept_Aggreg_gt NO LOG;

Therefore, care should be taken to insure that not all users have ALTER privileges on the base table definition in the DD. Otherwise, accidentally omitting the word "temporary" alters the base definition and no one has the LOG option as seen below:

ALTER TABLE Dept_Aggreg_gt NO LOG;

Likewise, the same consideration should be used when defining and collecting Statistics on the stored definition versus the materialized instance. The following defines which statistics to collect on the table definition:

COLLECT STATISTICS ON Dept_Aggreg_gt index (Dept_no);

However, when this is executed there are no rows in the table and therefore no rows to evaluate and no statistics to store. So, why bother? The reason is that once an instance is materialized all a user needs to do is collect statistics at the table level after inserting their rows into their temporary instance of the table.

The following COLLECT specifies the importance of the word TEMPORARY to denote the instance and not the base definition:

COLLECT TEMPORARY STATISTICS on Dept_Aggreg_gt;

The above statement collects all statistics for rows in the volatile table, as defined by the base table. However, a user might wish to collect statistics on a column not originally defined for the table, such as Max_Salary. To accomplish this collection operation, the user could execute the next statement:

COLLECT TEMPORARY STATISTICS on Dept_Aggreg_gt COLUMN Max_Salary;

As a reminder, each instance can only be accessed by a single user and furthermore, only within a single session for that user. Like the volatile table, the same user cannot access rows from their own temporary table from a different session.

Also like a volatile table, a global table releases its temporary space and the instance when the user logs off. If the user wishes to manually drop the instance, use the following command:

DROP TEMPORARY TABLE Dept_Aggreg_gt ;

Again, the word TEMPORARY is very important because without it:

DROP TABLE Dept_Aggreg_gt ;

Will drop the base definition and cause problems for other users. Privileges should be established to prevent a user from accidentally dropping a global table definition.

With that being said, there might come a time when it is desired to drop the base definition. If the above DROP TABLE is executed, it will work unless a user has a materialized instance. One materialized instance is enough to cause the statement to fail. As an alternative, an ALL option can be added, as seen in the next statement, in an attempt to drop the definition:

DROP TABLE Dept_Aggreg_gt ALL ;

This works as long as a user is not in the middle of a transaction. Otherwise, the only option is to wait until the user's transaction completes and then execute the DROP again.

The above format for a Global table indicates the ability to define a primary index as either unique or non-unique. Additionally, since the definition is in the data dictionary, placing a UNIQUE constraint on one or more columns would also make the first unique column a UPI. This logic is the same for a real table.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics