Chapter 17 – 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 Two 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
Temporary Table
Created by the User and materialized with an INSERT/SELECT
Space comes from the User’s space
Table and Data are deleted only after a User Logs off the session
The two types of Temporary tables are derived and temporary. You might be asking, "Where are the Global and Local table options for temporary tables"? Global and local temporary tables do not exist in Greenplum. The Greenplum Database will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they have no effect. They are used for compatibility reasons.
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
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. We have aliased the column AVGSAL, but we did so inside the SELECT statement.
CREATING a Derived Table using the WITH Command
When using the WITH Command, we can CREATE our Derived table before running the main query. There will always be two SELECT statements. The first SELECT materializes the derived table and the second SELECT is required to query the derived table.
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. This is how derived tables are normally used. They join with other tables.
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.
A Derived Table and CAST Statements
SELECT E.Dept_No, E.Last_Name, E.Salary
,CAST(AVGSAL as Decimal (8,2))
,CAST(Salary - AVGSAL as Decimal (8,2)) as PlusMinAvg
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 ;
Our example above shows the data in the derived table named TeraTom. We have used CAST statements to better format the data in our SELECT list. This query shows employees, the AVG (Salary) by dept_no and compares salary to the average.
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)
FROMEmployee_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 to Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROMEmployee_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.
Clever Tricks on Aliasing Columns in a Derived Table
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;
We have two derived tables in our example. One is used in a WITH statement and the other is a derived table within the query itself.
MULTIPLE Derived Tables using the WITH Command
Using the WITH Command, we can CREATE multiple Derived tables that can be referenced elsewhere in the query.
Finding the First Occurrence
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
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 first 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.
Three Steps to Creating a Temporary Table
Query the table all session long
When you use the phrase ON COMMIT PRESERVE ROWS the data will stay in the table all session long. The normal ANSI default is ON COMMIT DELETE ROWS, which will delete the rows after a single transaction. However, Greenplum defaults to ON COMMIT PRESERVE ROWS.
Three Versions of Creating a Temporary Table
CREATE Temporary TABLE Dept_Agg1
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
CREATE Temporary TABLE Dept_Agg2
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DELETE ROWS ;
CREATE Temporary TABLE Dept_Agg3
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DROP ;
I will explain how to use these different techniques in the next few pages.
ON COMMIT PRESERVE ROWS is the Greenplum Default
CREATE Temporary TABLE Dept_Agg5
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
ANSI defaults to ON COMMIT DELETE ROWS, but Greenplum has cleverly made their default ON COMMIT PRESERVE ROWS. Both examples above accomplish the same thing.
ON COMMIT DELETE ROWS
CREATE Temporary TABLE Dept_Agg8
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DELETE ROWS ;
INSERT INTO Dept_Agg8
SELECT Dept_No, SUM(Salary)
FROM Employee_Table
GROUP BY 1;
SELECT *
FROM Dept_Agg8
Order by 1;
ON COMMIT DELETE ROWS allows the user a single transaction after creating the table before it deletes the contents. After the INSERT/SELECT the table's rows were deleted. This seems stupid at first, but it is actually smart. The next page will show you how to take advantage of this and why it is used.
How to Use the ON COMMIT DELETE ROWS Option
CREATE Temporary TABLE Dept_Agg7
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DELETE ROWS ;
The ON COMMIT DELETE ROWS option allow you only one transaction after creating the temporary table, but you can embed the INSERT/SELECT and the SELECT to get the report you need inside a Begin Transaction/End Transaction statement. This option should be used if you only need the temporary table to produce a single report.
ON COMMIT DROP
CREATE Temporary TABLE Dept_Aggb
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DROP ;
INSERT INTO Dept_Aggb
SELECT Dept_No, SUM(Salary)
FROM Employee_Table
GROUP BY 1;
ON COMMIT DROP will drop the temporary table after a single transaction. That is why the error above occurred. After the INSERT/SELECT, the temporary table rows was dropped. This seems stupid at first, but it is actually smart. The next page will show you how to take advantage of this and why it is used.
How to Use the ON COMMIT DROP Option
The ON COMMIT DROP option will drop the temporary table after a single transaction, which includes the CREATE statement. However, you can embed the CREATE Statement, the INSERT/SELECT and the SELECT to get the report you need inside a Begin Transaction/End Transaction statement. The great news is that the table no longer exists!
Create Table AS
This table is exactly like the Order_Table
CREATE TABLE New_Order
AS SELECT *
FROM Order_Table
This table uses only some columns
CREATE TABLE New_Employee
AS SELECT First_Name
,Last_Name
,Salary
FROM Employee_Table
This table is a temporary table
CREATE Temporary TABLE temp_order
AS SELECT *
FROM Order_Table
Above are some great example to quickly CREATE a Table from another table.
Creating a Temporary Table Using a CTAS that Joins Multiple Tables
You can create a temporary table using a CTAS (Create Table AS) statement, as in the above example.
Create Table LIKE
This example uses an INSERT/SELECT
CREATE TABLE Sales3 (LIKE Sales_Table);
INSERT INTO Sales3
SELECT *
FROM Sales_Table;
SELECT *
FROM Sales3;
This example creates a temporary table
CREATE Temporary TABLE Sales4 (LIKE Sales_Table);
INSERT INTO Sales4
SELECT *
FROM Sales_Table;
SELECT *
FROM Sales4;
The example above creates at table using the LIKE statement. It then loads the data with an INSERT/SELECT. You are now ready to query the new table. Notice that you can do the same technique to create a temporary table.
Creating a Clustered Index on a Temporary Table
Above we have sorted the temporary table Dept_Agg_Vol on each segment by Dept_No. You can have one clustered index on a table because you can only sort the rows one specific way. Having a Clustered Index on a Dept_No column will help with range queries, because the data on each segment is sorted by Dept_No. CLUSTER is not supported with append-only tables or column-oriented tables.