Chapter 13 - Temporary Tables
“Those who would give up essential liberty to purchase a little temporary safety, deserve neither liberty nor safety.”
There are three types of Temporary Tables
Derived Table
- Exists only within a query
- Materialized by a SELECT Statement inside a query
- Space comes from the User's Spool space
- Deleted when the query ends
Volatile Table
- Created by the User and materialized with an INSERT/SELECT
- Space comes from the User's Spool space
- Table and Data are deleted only after a User Logs off the session
Global Temporary Table
- Table definition is created by a User and the table definition is permanent
- Materialized with an INSERT/SELECT
- Space comes from the User's TEMP Space
- When User logs off the session the data is deleted, but the table definition stays
- Many Users can populate the same Global table, but each has their own copy
The three types of Temporary tables are Derived, Volatile, and Global Temporary Tables.
CREATING A Derived Table
The SELECT Statement that creates and populates the Derived table is always inside Parentheses.
Naming the Derived Table
In the example above, TeraTom is the name we gave the Derived Table. It is mandatory that you always name the table or it errors.
Aliasing the Column Names in the Derived Table
In the example above, AVGSAL is the name we gave the column in the Derived Table. It is mandatory that you always name the table or it errors. But, you have multiple options in naming the columns which we will show in our upcoming examples.
Most Derived Tables Are Used To Join To Other Tables
| SELECT | E.*, AVGSAL |
| FROM | Employee_Table as E |
| INNER JOIN | |
| (SELECT Dept_No, AVG(salary) FROM Employee_Table | |
| GROUP BY Dept_No) AS TeraTom(Dept_No, AVGSAL) | |
| ON | E.Dept_No = TeraTom.Dept_No |
| ORDER BY | E.Dept_No ; |
The above example shows how derived tables are usually used. They are great for combining aggregates with detail data. Above our derived table, TeraTom held the averages for each Dept_No, and we then joined TeraTom to our Employee_Table.
Multiple Ways to Alias the Columns in a Derived Table
The queries above produce the same result as they are equivalent. The only difference is that we specifically named our column AVGSAL in a different place.
Our Join Example with a Different Column Aliasing Style
| SELECT | E.*, AVGSAL |
| FROM | Employee_Table as E |
| INNER JOIN | |
| (SELECT Dept_No as Dept_No, AVG(salary) as AVGSAL | |
| FROM Employee_Table | |
| GROUP BY Dept_No) AS TeraTom | |
| ON | E.Dept_No = TeraTom.Dept_No |
| ORDER BY | E.Dept_No ; |
We have named the columns in our TeraTom derived table right inside the derived query. This is exactly the same query we ran a couple of pages ago.
Column Aliasing Can Default for Normal Columns
| SELECT | E.*, AVGSAL |
| FROM | Employee_Table as E |
| INNER JOIN | |
| (SELECT Dept_No, AVG(salary) as AVGSAL | |
| FROM Employee_Table | |
| GROUP BY Dept_No) AS TeraTom | |
| ON | E.Dept_No = TeraTom.Dept_No |
| ORDER BY | E.Dept_No ; |
Notice that we didn't alias the column Dept_No inside TeraTom. The column therefore defaulted to Dept_No. We had to name the aggregate, but not the normal column.
CREATING A Derived Table using the WITH Command
When using the WITH Command, we can CREATE our Derived table before running the main query. The only issue here is that you can only have 1 WITH statement, and you don't have any options for aliasing the column other than what you see above.
Our Join Example With the WITH Syntax
WITH TeraTom (Dept_No, AVGSAL) AS
(SELECT Dept_No, AVG(Salary) FROM Employee_Table
GROUP BY Dept_No)
| SELECT | E.*, AVGSAL |
| FROM | Employee_Table as E |
| INNER JOIN | |
| TeraTom | |
| ON | E.Dept_No = TeraTom.Dept_No |
| ORDER BY | E.Dept_No ; |
The WITH syntax is nice because you build the derived table right away.
The Same Derived Query shown Three Different Ways
All three examples above are the same query but with a different derived style. The Derived table name must always be named.
Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROM Employee_Table
GROUP BY Dept_No) as TeraTom (Depty, AVGSAL)
ON Dept_No = Depty ;
What is the name of the derived table? __________
How many columns are in the derived table? __________
What is the name of the derived table columns? __________
Is there more than one row in the derived table? __________
What common keys join the Employee and Derived? __________
Why were the join keys named differently? ______________
Answer to Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROM Employee_Table
GROUP BY Dept_No) as TeraTom (Depty, AVGSAL)
ON Dept_No = Depty ;
What is the name of the derived table? TeraTom
How many columns are in the derived table? 2
What's the name of the derived columns? Depty and AVGSAL
Is there more than one row in the derived table? Yes
What keys join the tables? Dept_No and Depty
Why were the join keys named differently? If both were named Dept_No, we would error unless we fully qualified.
Clever Tricks on Aliasing Columns in a Derived Table
In example 2, why did we only give AVGSAL an alias? Dept_No defaulted implicitly to Dept_No. You must name the aggregate specifically, but normal columns can default to their current name. That's why Dept_No is fully qualified!
A Derived Table lives only for the lifetime of a single query
The second query fails in this Multi-Statement transaction because the derived table is only good for the life of a single query. T no longer exists!
An Example of Two Derived Tables in a Single Query
WITH T(Dept_No, AVGSAL) AS
(SELECT Dept_No, AVG(Salary) FROM Employee_Table
GROUP BY Dept_No)
SELECT T.Dept_No, First_Name, Last_Name,
AVGSAL, Counter
FROM Employee_Table as E
INNER JOIN
T
ON E.Dept_No = T.Dept_No
INNER JOIN
(SELECT Employee_No, SUM(1) OVER(PARTITION BY Dept_No
ORDER BY Dept_No, Last_Name Rows Unbounded Preceding)
FROM Employee_Table) as S (Employee_No, Counter)
ON E.Employee_No = S.Employee_No
Creating a Volatile Table
CREATE VOLATILE TABLE Dept_Agg_Vol , NO LOG
( Dept_no Integer
,Sum_Salary Decimal(10,2)
)
ON COMMIT PRESERVE ROWS ;
NO Log is the default and it means don't use the Transient Journal which gives you Rollback capabilities and better data integrity. But since this is a Volatile Table, who cares? No Log is faster when doing Maintenance.
ON COMMIT PRESERVE ROWS is NOT the default. You must us these Keywords if you want your data to stay in the Volatile Table after you populate it. Otherwise, after the load transaction, the data is deleted. That is referred to as ON COMMIT DELETE ROWS.
This statement creates a Volatile Table!
You Populate a Volatile Table with an INSERT/SELECT
CREATE VOLATILE TABLE Dept_Agg_Vol, NO LOG
| ( Dept_no | Integer |
| ,Sum_Salary | Decimal(10,2) |
| ) |
ON COMMIT PRESERVE ROWS ;
INSERT INTO Dept_Agg_Vol
SELECT Dept_no
,SUM(Salary)
FROM Employee_Table
GROUP BY Dept_no ;
1) A USER Creates a Volatile Table and then 2) populates the Volatile Table with an INSERT/SELECT Statement. The space to materialize this table comes from the User's Spool space. Now, you can query this table all session long. When the session is logged off, the table and the data are automatically deleted.
The Three Steps to Use a Volatile Table
CREATE VOLATILE TABLE Dept_Agg_Vol , NO LOG
( Dept_no Integer
,Sum_Salary Decimal(10,2)
)
ON COMMIT PRESERVE ROWS ;
INSERT INTO Dept_Agg_Vol
SELECT Dept_no
,SUM(Salary)
FROM Employee_Table
GROUP BY Dept_no ;
Only you can see this data because your session number is associated with your Volatile Tables. You can't even see this table if you login and query it from another session!
SELECT * FROM Dept_Agg_Vol
ORDER BY 1;
1) A USER Creates a Volatile Table and then 2) populates the Volatile Table with an INSERT/SELECT Statement, and then 3) Queries it until he/she logs off.
Why Would You Use the ON COMMIT DELETE ROWS?
CREATE VOLATILE TABLE Dept_Agg_Vol , NO LOG
( Dept_no Integer
,Sum_Salary Decimal(10,2)
) ON COMMIT DELETE ROWS ;
BEGIN TRANSACTION ;
INSERT INTO Dept_Agg_Vol
SELECT Dept_no ,SUM(Salary)
FROM Employee_Table GROUP BY Dept_No ;
SELECT * FROM Dept_Agg_Vol
ORDER BY 1 ;
END TRANSACTION ;
These queries run as one Transaction, and when it completes, the table and the data are deleted immediately.
If you want to populate a Volatile Table, and then only run one query, then why not have it go away when you are done? That is what will happen in the above example.
The HELP Volatile Table Command Shows your Volatiles
CREATE VOLATILE TABLE Dept_Agg_Vol , NO LOG
( Dept_no Integer
,Sum_Salary Decimal(10,2)
)
ON COMMIT PRESERVE ROWS ;
INSERT INTO Dept_Agg_Vol
SELECT Dept_no
,SUM(Salary)
FROM Employee_Table
GROUP BY Dept_no ;
The HELP Volatile Table command above is exactly what you type in. This shows you all the Volatile tables you have materialized in your current session.
A Volatile Table with a Primary Index
It is a great idea to give your Volatile Table a Primary Index so you can control how it is distributed and the best way you want to query it. In the above example, we knew we would be querying on the column CustNo so we made it the Primary Index. Check out the next page, and see how clever our Primary Index of CustNo is for joining tables.
The Joining of Two Tables Using a Volatile Table
SELECT C.CustNo,
,C.CustName
,OV.Order_Total
FROM Customer_Table as C
INNER JOIN Order_Vol as OV
ON C.CustNo = OV.CustNo ;
When Teradata does a join, the matching rows need to be on the same AMP. We gave our Volatile Table a great Primary Index, fully knowing we were going to populate it with September orders and then join it to the Customer_Table on the join condition of CustNo. Now, no data movement is required. Brilliant!
You Can Collect Statistics on Volatile Tables
You can Collect Statistics on Volatile Tables. This can help improve query performance. You should consider collecting on:
- All Non-Unique Primary Indexes (NUPI)
- The Unique Primary Index of small tables (less than 1,000 rows per AMP)
- Columns that frequently appear in WHERE search conditions
- Non-indexed columns used in joins
- Partitioning column of a PPI Table
You don't have to collect statistics on Volatile tables, but sometimes you will find if you are having performance problems that collecting statistics on a volatile table can greatly enhance performance. Above are some great guidelines for collecting statistics on volatile tables.
The New Teradata V14 Way to Collect Statistics
In previous versions, Teradata required that you had to Collect Statistics for each column separately thus always performing a full table scan each time. Those days are over!
| Old Way | New Teradata V14 Way |
| COLLECT STATISTICS COLUMN (OrderNo, CustNo) ON Order_Vol ; COLLECT STATISTICS COLUMN (CustNo) ON Order_Vol; COLLECT STATISTICS ON Order_Vol Column (Order_Date) ; |
COLLECT STATISTICS COLUMN (OrderNo, CustNo) , COLUMN (CustNo) , COLUMN (Order_Date) ON Order_Vol; |
The new way to collect statistics in Teradata V14 is to do it all at the same time. This is a much better strategy. Only a single table scan is required instead of 3 table scans using the old approach. This is an incredible improvement.
Four Examples of Creating a Volatile Table Quickly
| This example creates a Volatile Table from the Order_Table, but gives it a new Primary Index. | This example creates a Volatile Table from the Order_Table, but makes the Volatile a Multiset table. |
 |
 |
| CREATE VOLATILE TABLE Order_Vol AS (SELECT * FROM Order_Table) WITH DATA PRIMARY INDEX (Customer_Number) ON COMMIT PRESERVE ROWS ; |
CREATE MULTISET VOLATILE TABLE Order_Vol AS (SELECT * FROM Order_Table) WITH DATA AND STATISTICS PRIMARY INDEX(Customer_Number) ON COMMIT PRESERVE ROWS ; |
| This example creates a Volatile Table from the Order_Table and copies the STATISTICS. | This example creates a Volatile Table from the Order_Table and uses a WHERE clause to limit the data |
 |
 |
| CREATE VOLATILE TABLE Order_Vol AS (SELECT * FROM Order_Table) WITH DATA AND STATISTICS PRIMARY INDEX (Customer_Number) ON COMMIT PRESERVE ROWS ; |
CREATE VOLATILE TABLE Order_Vol AS (SELECT * FROM Order_Table WHERE Extract(Month from Order_Date) = 9) WITH DATA AND STATISTICS PRIMARY INDEX (Customer_Number) ON COMMIT PRESERVE ROWS ; |
Above are four great examples to quickly CREATE a Volatile Table from another table.
Four Advanced Examples of Creating a Volatile Table Quickly
| This example creates a Volatile Table from the Order_Table, but defaults the Primary Index to the first column in the table. | This example creates a Volatile Table from the Order_Table, but does NOT put any data in it. You will then need to do an INSERT/SELECT. |
|
|
| CREATE VOLATILE TABLE Order_Vol AS (SELECT * FROM Order_Table) WITH DATA ON COMMIT PRESERVE ROWS ; |
CREATE VOLATILE TABLE Order_Vol AS (SELECT * FROM Order_Table) WITH NO DATA PRIMARY INDEX(Customer_Number) ON COMMIT PRESERVE ROWS ; |
| This example creates a Volatile Table from the Order_Table but doesn't take all the columns. | This example creates a Volatile Table from the Order_Table with the exact same definition, including secondary indexes. |
|
|
| CREATE VOLATILE TABLE Order_Vol AS (SELECT Customer_Number ,Order_Date, Order_Total FROM Order_Table) WITH DATA AND STATISTICS ON COMMIT PRESERVE ROWS ; |
CREATE VOLATILE TABLE Order_Volatile3 AS ORDER_TABLE WITH DATA AND STATISTICS ON COMMIT PRESERVE ROWS ; |
Above are four advance examples to CREATE a Volatile Table from another table.
Creating Partitioned Primary Index (PPI) Volatile Tables
CREATE VOLATILE TABLE Order_Table_PPI
AS ( SELECT * FROM Order_Table
WHERE Order_Date BETWEEN
DATE '2013-01-01' and '2013-06-30')
WITH DATA
PRIMARY INDEX (Order_Number)
PARTITION BY RANGE_N( ORDER_DATE
BETWEEN Date '2013-01-01' and Date '2013-06-30'
EACH INTERVAL '1' DAY)
ON COMMIT PRESERVE ROWS ;
Above, you can see an example of quickly creating a Volatile Partitioned table directly from the actual Order_Table. We only inserted some of the data with our WHERE clause and we partitioned by day.
Using a Volatile Table to Get Rid of Duplicate Rows
Sales_Table
This Multiset table has duplicate rows and we want to get rid of them
CREATE VOLATILE SET TABLE Rid_Of_Dups
AS ( SELECT * FROM Sales_Table)
WITH DATA
ON COMMIT PRESERVE ROWS;
DELETE FROM Sales_Table All;
INSERT INTO Sales_Table
SELECT * from Rid_Of_Dups ;
If you have a Multiset table that accidentally gets unwanted duplicate rows, you can use the technique above to get rid of them. We first create a SET Volatile table, and when the data is copied, the duplicate rows are eliminated. Then, we can delete all the rows from the Multiset table and reinsert the rows from the Volatile, and all is good.
Using a Simple Global Temporary Table
CREATE Global Temporary TABLE Order_Global
| ( OrderNo | Integer NOT NULL |
| ,CustNo | Integer |
| ,Order_Date | Date |
| ,Order_Total | Decimal(10,2)) |
ON COMMIT PRESERVE ROWS ;
INSERT INTO Order_Global
SELECT OrderNo, CustNo,
Order_Date, Order_Total
FROM Order_Table
WHERE
extract(Month from Order_Date) = 9 ;
Many users (TEMP space required) can populate or materialize this table simultaneously with an Insert/Select statement, but a separate copy is made for each user for the purpose of security and integrity.
SELECT * FROM Order_Global
ORDER BY 1;
1) A USER Creates a Volatile Table and then 2) populates the Volatile Table with an INSERT/SELECT Statement, and then 3) Queries it until he/she logs off. All data is deleted when a user logs off, but the table definition stays forever unless dropped.
Two Brilliant Techniques for Global Temporary Tables
CREATE Global Temporary TABLE Order_Global
( OrderNo Integer NOT NULL
,CustNo Integer
,Order_Date Date COMPRESS
,Order_Total Decimal(10,2) COMPRESS
) Primary Index (CustNo)
ON COMMIT PRESERVE ROWS ;
Give your Global tables a Primary Index, and use the COMPRESS Keyword for any column that is Nullable and NOT the Primary Index.
INSERT INTO Order_Global
SELECT OrderNo, Cust_No,
Order_Date, Order_Total
FROM Order_Table
WHERE
extract(Month from Order_Date) = 9 ;
Any user with Temp Space can materialize the table with an Insert/Select statement, and the data won't be deleted until they logoff.
SELECT * FROM Order_Global
ORDER BY 1;
The data is deleted when the user does logoff, but the table structure stays permanently.
Give your Global Temporary Tables a Primary Index, and also compress any Nullable column. If a null is present, then Teradata will compress it and save space.
The Joining of Two Tables Using a Global Temporary Table
SELECT C.CustNo,
,C.CustName
,G.Order_Total
FROM Customer_Table as C
INNER JOIN Order_Global as G
ON C.CustNo = G.CustNo ;
We gave our Global Temporary Table a great Primary Index fully knowing we were going to populate it with September orders, and then join it to the Customer_Table on the join condition of CustNo. Now, no data movement is required. Brilliant!
CREATING A Global Temporary Table
CREATE Global Temporary TABLE Dept_Agg_GLO
( Dept_no Integer
,Sum_Salary Decimal(10,2)
)
ON COMMIT PRESERVE ROWS ;
ON COMMIT PRESERVE ROWS is NOT the default. You must us these Keywords if you want your data to stay in the Volatile Table after you populate it. Otherwise, after the load transaction, the data is deleted. That is referred to as ON COMMIT DELETE ROWS.
The Table Definition stays Permanently. When a user logs off, the data Inside the Global Temporary Table is deleted, but the definition stays around ready to be populated again.
This syntax creates a Global Temporary Table, which is stored in the Data Dictionary of Teradata. A Global Temporary Table survives a Teradata Restart.