Temporary Tables in Teradata Teradata

There may be times when an existing production database table does not provide precisely what you need. Sometimes, a particular query might need summarized or aggregated data. At other times, a small number of rows, from a very large table or data for a specific organization, are required to find an answer.

In a data warehouse with millions of rows, it might take too long to locate, derive or mathematically calculate the data needed. This is especially true when it is needed more than once per day. So, a view might not be the best solution or a view does not exist and you do not have the privilege to create one and both a view and derived table take too long. Any of these conditions prevent the ability to complete the request.

In the past, temporary tables have been created and used to help SQL run faster or be more efficient. They are extremely useful for solving problems that require stored "temporary"' results or which require multiple SQL steps. They are also great for holding aggregated or summarized data.

Most databases lose speed when they have to:

  • Read every row in a very large table (full table scan)
  • Perform several aggregations
  • Perform several data type conversions
  • Join rows together from multiple tables
  • Sort data

Temporary tables are often useful in a de-normalization effort. This might be done to make certain queries execute faster. Other times it is done to make the SQL easier to write, especially when using tools that generate SQL. However, these temporary tables are real tables and require manual operations to create, populate, and maintain them.

As a result, better name for these temporary tables might be interim or temporal tables. They exist for a specific period of time and when no longer needed, they are dropped to free up the disk space. During the interim time, they provide a valuable service. However, if the data in the original tables changes, the interim tables must be repopulated to reflect that change. This adds a level of difficulty or complexity regarding their use.

Creating Interim or Temporal Tables

The following series of commands provide an example of creating, inserting, and then entering SQL queries on an interim or temporal table (real table for short-term use):

Creating Interim or Temporal Tables

This table breaks First Normal Form (1NF) in that it contains a repeating group for twelve monthly sales columns in every row. Normally, each row in a table represents an hour, a day, a week, or a month worth of sales. However with billions of rows, it requires extra time to read all the rows and consolidate them as calculated subtotals.

Therefore, the above table will make summary processing faster. In some cases, it will also make the SQL easier to write. Both of these become important considerations as the frequency of requests and the number of users needing this data, increases.

The Employee, Department and Sales tables below are used to demonstrate temporary tables:

Employee, Department and Sales tables below are used to demonstrate temporary tables

Employee, Department and Sales tables below are used to demonstrate temporary tables

Employee, Department and Sales tables below are used to demonstrate temporary tables

To populate the interim table with data for September through December of the year 2000, the next INSERT / SELECT might be used:

Then, to display the sales for the last quarter of the year 2000, all that is required is a SELECT like the following:

3 Rows Returnedrequired is a SELECT like the following

The previous SQL shows how to create, insert, and access an interim table. Aside from the positive aspects of using this technique, there are other considerations. First, to provide current data, the rows in the table might need to be updated periodically. Otherwise, using active data warehouse tables and the summary table result in different answers to the same question.

Another issue regarding this technique is that a "real table" must be created and later it will be dropped manually. Both of these actions require updates in the Data Dictionary (DD). Additionally, you must have the privileges to CREATE and DROP a table as well as available Permanent space. Most data warehouse sites do not allow for this type of loose security and space management. It is not normally an acceptable practice to allow all users to create these temporal tables for this purpose.

Potential gains using an Interim table:

  • Make the SQL easier to write and run faster
  • Avoid repeated aggregation on the real data rows
  • Provide Primary Index access
  • Data is available to multiple users

The associated costs of this table:

  • It requires separate steps to create and populate the table originally
  • It must be kept updated with new sales data
  • Requires extra PERM Space for the interim table
  • Requires the table to be manually dropped when no longer needed
  • Requires privileges to execute a CREATE and DROP TABLE
  • Requires DD locking to create and drop table

The main problem is that a permanent table had to be created. This action is manual and is done within the DD because it is a real table. Another problem is that the contents of the table may quickly become out of date the moment a row changes in the original table.

The biggest single stumbling block is probably the inability to create a table, due to a lack of the proper privileges or Permanent space. The only solution is to submit a change request to the Database Administrator (DBA). Therefore, the process might take a couple of days to complete. This can be devastating when the data is needed immediately. So instead of using interim tables, this may be a perfect opportunity to use temporary tables.

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

Teradata Topics