Chapter 11 – Temporary Tables
“I cannot imagine any condition which would cause this ship to founder. Modern shipbuilding has gone beyond that.”
- E. I. Smith, Captain of the Titanic
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
Local Temporary Table
•Created by the User and materialized with an INSERT/SELECT
•Table and Data are deleted only after a User Logs off the session
•Can be session specific or seen across different sessions
Global Temporary Table
•Table definition is created by a User and the table definition is permanent
•Materialized with an INSERT/SELECT
•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
•Global temporary tables are created in the public schema, with the data contents private to the transaction or session through which data is inserted.
The three types of Temporary tables are Derived, Local Temporary and Global Temporary Tables.
CREATING A 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
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
AVGSAL is the name we gave to the column in our Derived Table that we call TeraTom. Our SELECT (which builds the columns) shows we are only going to have one column in our derived table, and we have named that column AVGSAL.
Multiple Ways to Alias the Columns in a Derived Table
You can alias the column name within the SQL query that materializes the derived table.
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.
The Same Derived Query shown Three Different Ways
Most Derived Tables Are Used To Join To Other Tables
The first five columns in the Answer Set came from the Employee_Table. AVGSAL came from the derived table named TeraTom.
The Three Components of a Derived Table
|
A derived table will always have a SELECT query to materialize the derived table with data. The SELECT query always starts with an open parenthesis and ends with a close parenthesis. |
|
The derived table must be given a name. Above we called our derived table TeraTom. |
|
You will need to define (alias) the columns in the derived table. Above we allowed Dept_No to default to Dept_No, but we had to specifically alias AVG(Salary) as AVGSAL. |
Every derived table must have the three components listed above.
Visualize This Derived Table
Our example above shows the data in the derived table named TeraTom. This query allows us to see each employee and the plus or minus avg of their salary compared to the other workers in their department.
Our Join Example with a Different Column Aliasing Style
Our example above aliases the column Dept_No, but it doesn’t need an alias. It will default to Dept_No, but the aggregate must be aliased..
Column Aliasing Can Default for Normal Columns
In a derived table, you will always have a SELECT query in parenthesis, and you will always name the table. You have options when aliasing the columns. As in the example above, you can let normal columns default to their current name.
A Derived example Using the WITH Syntax
Most derived tables involve calculations, aggregations or ordered analytics. This allows tables and derived columns to mix well on the final report. Above, we are finding all employees who make a salary that is greater than the average salary within their own department. We created a derived table that holds all departments and the average salary within the department. We then join the derived table (named TeraTom) to the employee_table where we can check the salary vs. the avg (salary).
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 ;
1) What is the name of the derived table? __________
2) How many columns are in the derived table? _______
3) What is the name of the derived table columns? ______
4) Is there more than one row in the derived table? _______
5) What common keys join the Employee and Derived? _______
6) Why were the join keys named differently? ______________
Answer the questions above an you will fully understand the components of a derived table.
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 ;
1) What is the name of the derived table? TeraTom
2) How many columns are in the derived table? 2
3) What’s the name of the derived columns? Depty and AVGSAL
4) Is their more than one row in the derived table? Yes
5) What keys join the tables? Dept_No and Depty
6) Why were the join keys named differently? If both were named Dept_No, we would error unless we full qualified.
Great job!
Clever Tricks on Aliasing Columns in a Derived Table
Check out a few clever tricks to help you with derived tables.
A Derived Table lives only for the lifetime of a single query
We tried everything to see if the derived table would live past the current query. Notice above, we started with a BEGIN TRANSACTION statement. Then we ran our query that materialized our derived table name T. Then, we attempted to run another query (within the same transaction) that did a SELECT * FROM T and the query failed.
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
ORDER BY T.Dept_No;
Above we have built two different derived tables. The first is named T and the second is named S. Notice that we materialized T using a WITH statement and we build S right after the INNER JOIN keywords.
Example of Two Derived Tables in a Single WITH Statement
Above we have built two different derived tables within a single WITH statement. The first is named E and the second is named D. There is only one WITH statement, but the tables and definitions are separated with a comma.
Finding the First Occurrence of a Row using WITH
Using the Row_Number ordered analytic and by partitioning of Product_ID and the sorting by Sale_Date ASC we are bringing back only the first occurrence of a row based on the earliest Sale_Date. This can be done because we are placing our query in a derived table and then selecting from that derived table using a WHERE clause.
Finding the Last Occurrence of a Row using WITH
Using the Row_Number ordered analytic and by partitioning of Product_ID and the sorting by Sale_Date DESC we are bringing back only the last occurrence of a row based on the latest Sale_Date. This can be done because we are placing our query in a derived table and then selecting from that derived table using a WHERE clause.
Syntax for Temporary Tables
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ]
TABLE [schema-name].table-name {
( column-definition [ , ... ] )
| [ column-name-list ] }
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ AS [ AT EPOCH LATEST ] | [ AT TIME 'timestamp' ] query ]
[ [ ORDER BY table-column [ , ... ] ]
[ ENCODED BY column-definition [ , ... ]
[ hash-segmentation-clause | range-segmentation-clause
| UNSEGMENTED { NODE node | ALL NODES } ]
[ KSAFE [ k-num ] ]
| [ NO PROJECTION ] ]
The syntax above is for creating temporary tables. Global tables can be seen outside the session and persist until the end of the session. Global is the default. Local tables can only be seen inside the session and persist until the end of session.
Temporary Tables Explained
Global Temporary Tables - The definition of a global temporary table is permanent in the database catalogs until explicitly removed by using the DROP TABLE command.
Global temporary tables are created in the public schema, and they are visible to all users and sessions. But, the contents (data) of a global table are private to the transaction or session in which the data was inserted. Data is automatically removed when the transaction commits, rolls back, or the session ends. This allows two users to use the same temporary table, but each only sees the data specific to his or her own transactions for the duration of those transactions or sessions.
Local Temporary Tables - A local temporary table is created in the V_TEMP_SCHEMA namespace and is inserted into the user’s search path automatically. It can only be seen by the user who created the table, and it lasts for only the duration of the session in which it is created. When the session ends, the table definition is automatically dropped from the database catalogs. Local Temporary Tables can be dropped explicitly.
Above are the major differences between Global and Local Temporary tables.
Key Temporary Table Terms
Global - [Optional] means that the table definition is visible to all sessions. Temporary table data is visible only to the session that materializes (inserts) the data into the table. Temporary tables in default to global.
Local - [Optional] Means that the table definition is visible only to the session in which it is created. Temporary tables always default to global.
On Commit Preserve|Delete rows – Preserve will preserve the rows until session end and then Truncate the table and Delete will Truncate the rows after each COMMIT.
AT EPOCH LATEST | AT TIME - Used with AS query to query historical data. You can specify AT EPOCH LATEST to include data from the latest committed transaction or specify a specific epoch based on a specific time stamp.
Above are the key terms you will want to know when creating a temporary table.
Creating and Populating a Local Temporary Table
1) A USER Creates a Local Temporary Table and then 2) populates the Temporary Table with an INSERT/SELECT Statement. Now, the user can query this table all session long. When the session is logged off, the table and the data are automatically deleted (Truncated).
Using a Local Temporary Table
CREATE LOCAL Temporary TABLE Dept_Agg_Local2
( Dept_noInteger
,AVG_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
INSERT INTO Dept_Agg_Local2
SELECTDept_no
,AVG(Salary)
FROM Employee_Table GROUP BY Dept_no ;
SELECT E.*, AVG_Salary
FROM Employee_Table as E
INNER JOIN
Dept_Agg_Local2
ON E.Dept_No = Dept_Agg_Local2.Dept_No
AND Salary > AVG_Salary
We created the Local Temporary Table, materialized it and then used it in a join. The above query finds all employees making a greater salary then the AVG (Salary) within their own dept_no.
Creating and Populating a Global Temporary Table
1) A USER Creates a Global Temporary Table once and the table definition will persist permanently, until it is dropped. Users can then 2) populates the Global Temporary Table with an INSERT/SELECT Statement. Now, the user can query this table all session long. When the session is logged off the table definition stays, but the data is automatically deleted (Truncated). Many different users can populate the table, but each only sees the table they materialized.
Creating and Populating a Global Temporary Table
CREATE Global Temporary TABLE Dept_Agg_Global
( Dept_noInteger
,AVG_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
Both users above can only see
the data they populated
Two users above have materialized the same Global Temporary table, but each only sees their table. Users can not share a Global Temporary table, but only the definition.
Some Great Examples of Creating a Temporary Table Quickly
This table is created from the Sales_Table
CREATE TEMP TABLE Sales_Agg
ON COMMIT PRESERVE ROWS
AS
SELECT Product_ID
,SUM(Daily_Sales)
FROM Sales_Table
Group by Product_ID;
This table is materialized from a join
CREATE TEMP TABLE Emp_Dept
ON COMMIT PRESERVE ROWS
AS
SELECT E.*, Department_Name, Budget
FROM Employee_Table as E
INNER JOIN
Department_Table as D
ON E.Dept_No = D.Dept_No;
Above are two great examples to quickly CREATE a temporary Table from another table.
Creating a Temporary Table That is sorted
This table is sorted by Sale_Date
CREATE GLOBAL TEMP TABLE Temp_Orders
(
Order_Number INTEGER
,Customer_Number INTEGER
,Order_Date Date
,Order_Total Decimal(8,2))
ON COMMIT PRESERVE ROWS
ORDER BY Order_Date, Customer_Number;
INSERT INTO Temp_Orders
SELECT * FROM Order_Table;
SELECT *
FROM Temp_Orders;
A great reason to create a temporary table is to have it sorted.
A Temp Table That Populates some of the Rows
Create a Temporary Table with orders from September
CREATE Temp TABLE Order_Vol
ON COMMIT PRESERVE ROWS
AS (SELECT * FROM Order_Table
WHERE Extract(Month from Order_Date) = 9);
Above is an example of creating a temporary table that is not an exact copy. It is only populating the table with orders from the month of September.
A Temporary Table with Some of the Columns
This creates a table with only three columns
CREATE Temporary TABLE Order_Vol5
ON COMMIT PRESERVE ROWS
AS (SELECT Customer_Number
,Order_Date, Order_Total
FROM Order_Table) ;
Above is an example of creating a Temporary table with three columns. The original table had four columns.