Chapter 5 - Temporary Tables
“They can conquer who believe they can.”
- Virgil 70 BC
Create Table Syntax
CREATE [ [LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
( { column_name data_type [column_attributes] [ column_constraints ]
| table_constraints
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ] )
[table_attribute]
where column_attributes are:
[ DEFAULT default_expr ]
[ IDENTITY ( seed, step ) ]
[ ENCODE encoding ]
[ DISTKEY ]
[ SORTKEY ]
and column_constraints are:
[ { NOT NULL | NULL } ]
[ { UNIQUE | PRIMARY KEY } ]
[ REFERENCES reftable [ ( refcolumn ) ] ]
and table_constraints are:
[ UNIQUE ( column_name [, ... ] ) ]
[ PRIMARY KEY ( column_name [, ... ] ) ]
[ FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ]
and table_attributes are:
[ DISTSTYLE { EVEN | KEY } ]
[ DISTKEY ( column_name ) ]
[ SORTKEY ( column_name [, ...] ) ]
Above creates a new table in the current database. The owner of this table is the issuer of the CREATE TABLE command.
Basic Temporary Table Examples
When you create a temporary table, it is visible only within the current session. The table is automatically dropped at the end of the session. Above, we use the TEMP keyword in the front of the table name to automatically make the table a temporary table. We then populate the table with an Insert/Select .
More Advanced Temporary Table Examples
When you create a temporary table, it is visible only within the current session. The table is automatically dropped at the end of the session. A derived table only lasts the life of a single query, but a temporary table last the entire session. This allows a user to run hundreds of queries against the temporary table. A temporary table can have the same name as a permanent table, but I don't recommend this. You don't give a temporary table a schema because it is automatically associated with the user’s session. Once the session is over, the table and data are dropped. If the user tries to query the table in another session, the system won't recognize the table. In other words, the table doesn't exist outside of the current session it was created in.
Advanced Temporary Table Examples
When you create a temporary table, it is visible only within the current session. The table is automatically dropped at the end of the session. Above are some examples that allow you to define a different distkey, diststyle and sortkey. Users (by default) are granted permission to create temporary tables by their automatic membership in the PUBLIC group. To remove the privilege for any users to create temporary tables, revoke the TEMP permission from the PUBLIC group and then explicitly grant the permission to create temporary tables to specific users or groups of users.
Table Limits and CTAS
•9,900 permanent tables.
•The maximum number of characters for a table name is 127.
•The maximum number of columns you can define in a single table is 1,600.
CREATE TABLE
Student_Table_Backup
AS
SELECT *
FROM Student_Table;
“To have everything is to possess nothing.”
--Buddha
The resulting table inherits the distribution and sort key from the Student_Table (STUDENT_ID). Today, Buddha might say, "To have 9,900 permanent tables, enables many things."
Performing a Deep Copy
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table. If a table has a large unsorted region, a deep copy is much faster than a vacuum. You can choose one of four methods to create a copy of the original table
1) Use the original table DDL. This is the best method for perfect reproduction.
2) Use CREATE TABLE AS (CTAS). If the original DDL is not available, you can use CREATE TABLE AS to create a copy of current table, then rename the copy. The new table will not inherit the encoding, distkey, sortkey, not null, primary key, and foreign key attributes of the parent table.
3) Use CREATE TABLE LIKE. If the original DDL is not available, you can use CREATE TABLE LIKE to recreate the original table. The new table will not inherit the primary key and foreign key attributes of the parent table. The new table does, though, inherit the encoding, distkey, sortkey, and not null attributes of the parent table.
4) Create a temporary table and truncate the original table. If you need to retain the primary key and foreign key attributes of the parent table, you can use CTAS to create a temporary table, then truncate the original table and populate it from the temporary table. This method is slower than CREATE TABLE LIKE because it requires two insert statements.
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table. If a table has a large unsorted region, a deep copy is much faster than a vacuum. The difference is that you cannot make concurrent updates during a deep copy operation which you can do during a vacuum. The next four slides will show each technique with an example.
Deep Copy Using the Original DDL
1) Use the original table DDL. This is the best method for perfect reproduction.
1. Create a copy of the table using the original CREATE TABLE DDL.
2. Use an INSERT INTO . . . SELECT statement to populate the copy with data from the original table.
3. Drop the original table.
4. Use an ALTER TABLE statement to rename the copy to the original table name.
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
Deep Copy Using A CTAS
2) Use CREATE TABLE AS (CTAS). If the original DDL is not available, you
can use CREATE TABLE AS to create a copy of current table, then rename the
copy. The new table will not inherit the encoding, distkey, sortkey, not null,
primary key, and foreign key attributes of the parent table.
1.Create a copy of the original table by using CREATE TABLE AS to select the rows from the original table.
2.Drop the original table.
3.Use an ALTER TABLE statement to rename the new table to the original table.
The following example performs a deep copy on the Sales_Table using a duplicate of the Sales_Table named Sales_Table_Copy.
CREATE TABLE Sales_Table_Copy as (select * from Sales_Table) ;
DROP TABLE Sales_Table ;
ALTER TABLE Sales_Table_Copy rename to Sales_Table ;
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
Deep Copy Using A Create Table LIKE
2) Use CREATE TABLE LIKE. If the original DDL is not available, you can use CREATE TABLE LIKE to recreate the original table. The new table will not inherit the primary key and foreign key attributes of the parent table. The new table does though inherit the encoding, distkey, sortkey, and not null attributes of the parent table.
1.Create a new table using CREATE TABLE LIKE.
2.Use an INSERT INTO . . . SELECT statement to copy the rows from the current table to the new table.
3.Drop the current table.
4.Use an ALTER TABLE statement to rename the new table to the original table.
The following example performs a deep copy on the Sales_Table using a duplicate of the Sales_Table named Sales_Table_Copy.
CREATE TABLE Sales_Table_Copy (like Sales_Table);
INSERT INTO Sales_Table_Copy (select * from Sales_Table);
DROP TABLE Sales_Table;
ALTER TABLE Sales_Table_Copy RENAME to Sales_Table;
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
Deep Copy by Creating a Temp Table and Truncating Original
Create a temporary table and truncate the original table. If you need to retain the primary key and foreign key attributes of the parent table, you can use CTAS to create a temporary table, then truncate the original table and populate it from the temporary table. This method is slower than CREATE TABLE LIKE because it requires two insert statements.
1.Use CREATE TABLE AS to create a temporary table with the rows
from the original table.
2.Truncate the current table.
3.Use an INSERT INTO . . . SELECT statement to copy the rows from the temporary table to the original table.
4.Drop the temporary table.
The following example performs a deep copy on the Sales_Table using a duplicate of the Sales_Table named Sales_Table_Copy.
CREATE Temp Table Sales_Table_Copy as select * from Sales_Table ;
TRUNCATE Sales_Table ;
Insert Into Sales_Table (select * from Sales_Table_Copy);
DROP Table Sales_Table_Copy;
A deep copy recreates and repopulates a table by using a bulk insert which automatically sorts the table.
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.
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.
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.
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.
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.
Multiple Ways to Alias the Columns in a Derived Table
Our Join Example with a Different Column Aliasing Style
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.
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.
Our Join Example With the WITH Syntax
Now, the lower portion of the
query refers to TeraTom
Almost like it is a permanent
table, but it is not!
WITH Statement That Uses a SELECT *
The following example shows the simplest possible case of a query that contains a WITH clause. The WITH query named TeraTom selects all of the rows from the Student_Table. The main query, in turn, selects all of the rows from TeraTom. The TeraTom table exists only for the life of the query.
A WITH Clause That Produces Two Tables
with Budget_Derived as
(SELECT Max(Budget) as Max_Budget
FROM Department_Table),
Emp_Derived as
(SELECT Dept_No, AVG(Salary) as Avg_Sal
FROM Employee_Table
GROUP BY Dept_No)
select E.*, Max_Budget – Budget as Under_Max, Avg_Sal
FROM Employee_Table as E
INNER JOIN
Emp_Derived
On E.Dept_No = Emp_Derived.Dept_No
INNER JOIN Department_Table as D
ON E.Dept_No = D.Dept_No;
The above example shows two tables created from the With statement.
The Same Derived Query shown Three Different Ways
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 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.
Clever Tricks on Aliasing Columns in a Derived Table
A Derived Table lives only for the lifetime of a single query
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;
Connecting To Matrix Via Nexus
Connecting To Matrix Via Nexus
Connecting To Matrix Via Nexus
Connecting To Matrix Via Nexus
