Chapter 7 – 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
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 two types of Temporary tables are Derived 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 space
•Deleted when the query ends
The SELECT Statement that creates and populates the Derived table is always inside Parentheses.
Creating Multiple Derived Tables in the WITH Command
When using the WITH Command, we can CREATE our Derived table before running the main query. After the keyword WITH is the derived table's name. Inside the following parenthesis are the derived table's column aliases. Then, there is the keyword AS followed by the query (in parenthesis to materialize the derived table. You then include it in the next SELECT.
Creating Multiple Derived Tables in the WITH Command
The above query creates and uses multiple tables from a single WITH command. The tables are E and D.
The Same Derived Query shown Two Different Ways
Both examples are equivalent and offer the same performance. The first example builds the derived table after the FROM clause within the query. The second example builds the derived table first and then does a SELECT of the table inside the query. If you use the WITH statement you must then use the table with an additional SELECT statement.
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 query shows the average salary per dept_no. The two tables were joined by dept_no.
The Three Components of a Derived Table
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 as well as 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
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.
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!
The WITH statement is easy to use once you understand it. A WITH statement builds and materializes the derived table first by defining the derived table name, the aliases of the column(s) and then by materializing it with a SELECT statement. Then, you must refer to the derived table by including it in an additional SELECT statement.
Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No as DEPTY, AVG(Salary) AS AVGSAL
FROM Employee_Table
GROUP BY Dept_No) TeraTom
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 and you will have mastered derived tables. Check your answers on the next page.
Answer to Quiz - Answer the Questions
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No as DEPTY, AVG(Salary) AS AVGSAL
FROM Employee_Table
GROUP BY Dept_No) TeraTom
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
Answer the questions above and you will have mastered derived tables. Check your answers on the next page.
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 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)
as Counter
FROM Employee_Table) S
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 WellPaid and the second is named DeptMgr. This query will show all managers who make a salary > the average salary.
WITH RECURSIVE Derived Table Hierarchy
Above is a company hierarchy and this is what we will use to perform our WITH Recursive query.
WITH RECURSIVE Derived Table Query
WITH TeraTom
(Emp, Mgr, LastN, Pos_Name, DEPTH) AS
(SELECT Employee_No, Mgr_Employee_No,
Last_Name, Position_Name, 0
FROM Hierarchy_Table
WHERE Mgr_Employee_No IS NULL
UNION ALL
SELECT Employee_No, Mgr_Employee_No,
Last_Name, Position_Name, DEPTH+1
FROM TeraTom
INNER JOIN
Hierarchy_Table
ON Emp= Mgr_Employee_No
) SELECT *
FROM TeraTom
ORDER BY 5,2,1 ;
Above is a WITH Recursive query.
WITH RECURSIVE Derived Table Definition
Above is a WITH Recursive query and the highlighted part explains the recursive derived table definition itself.
WITH RECURSIVE Derived Table Seeding
Above is a WITH Recursive query. The highlighted part explains how the first row is placed inside the derived table. The only employee with no manager is the CEO, Tom Coffing. His Mgr_Employee_No is NULL. The table is now seeded!
WITH RECURSIVE Derived Table Looping
Above is a WITH Recursive query . The highlighted part explains how the derived table is joined to the Hierarchy_Table in a looping fashion. The highlighted part keeps looping and adding rows until it loops and adds no rows. Then it is done.
WITH RECURSIVE Derived Table Looping in Slow Motion
Above is a WITH Recursive query . The highlighted part explains how the derived table is joined to the Hierarchy_Table in a looping fashion. The highlighted part keeps looping and adding rows until it loops and adds no rows. Then it is done. This is the first loop and as you can see two rows were added. That is because our join condition is Emp=Mgr_Employee_No. Both Stevens and Gonzales report to a manager with an emp = 1.
WITH RECURSIVE Derived Table Looping Continued
Above is a WITH Recursive query. The highlighted part explains how the derived table is joined to the Hierarchy_Table in a looping fashion. The highlighted part keeps looping and adding rows until it loops and adds no rows. Then it is done. This is the second loop and as you can see two rows were added. That is because our join condition is Emp=Mgr_Employee_No. Both Patel and Mumba report to a manager inside our recursive derived table.
WITH RECURSIVE Derived Table Looping Continued
Six rows are added in the third loop.
WITH RECURSIVE Derived Table Ends the Looping
No rows were added in the fourth loop. This loop is done!
WITH RECURSIVE Derived Table Definition
Above is a WITH Recursive query. The highlighted part is now run so the final answer set can be delivered.
WITH RECURSIVE Final Answer Set
The answer set is delivered. Notice the Depth column. There were depths of 0, 1, 2, 3. This looped four times. It actually looped a fifth time and when it did not add to the recursive table it left the loop.
Creating and Populating a Global Temporary Table
A USER Creates a Global Temporary Table once and the table definition will persist permanently, until it is dropped. Users can then 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.
Global Temporary Table Definitions Persist
You create a global temporary table just like a real table except:
•You include the keywords GLOBAL TEMPORARY.
•If you use the ON COMMIT PRESERVE ROWS clause the data
inserted will stay the entire session.
•If you use the ON COMMIT DELETE ROWS clause the data
inserted will be bound the a single transaction.
•After the session or the transaction the data is deleted but the table definition is stored permanently.
Many users can simultaneously perform an INSERT/SELECT on the same Global Temporary Table, but each user gets their own copy with only the rows they inserted. Nobody can see each other's data.
Vital Information about Global Temporary Tables
Temporary tables have all of the features that ordinary tables such as:
•A user can create views and synonyms on temporary tables and they
won’t disappear after the end of the transaction or session.
•A user can create indexes on a temporary tables and they won’t disappear
after the end of the transaction or session.
•Both triggers and statistics about table access cost and join cardinality
can be utilized.
•information about rows and blocks can also be utilized.
•On restraint is that foreign keys related to other temporary/permanent
tables are NOT allowed.
Temporary tables have almost all of the features of ordinary tables. They are listed above.
ON COMMIT DELETE ROWS Example
CREATE GLOBAL TEMPORARY TABLE Emp_Names
( Employee_No number(6) NOT NULL,
First_Name varchar(12) NOT NULL,
Last_Name varchar2(20) NOT NULL )
ON COMMIT DELETE ROWS;
INSERT INTO Emp_Names VALUES (1, 'Hitesh', 'Patel');
INSERT INTO Emp_Names VALUES (2, 'Annie', 'Gonzales');
INSERT INTO Emp_Names VALUES (3, 'Sven', 'Jorgenson');
SELECT count(*) FROM Emp_Names;
-- RESULT: 3
COMMIT;
SELECT count(*) FROM Emp_Names;
-- RESULT: 0
The ON COMMIT DELETE ROWS clause means that the data will only stay inside the table for a single transaction. After the COMMIT the data is deleted, but the table definition stays.
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.
Creating a Global Temporary Table Using a CTAS
This table is created from the Sales_Table
CREATE Global Temporary TABLE Sales_Agg
ON COMMIT PRESERVE ROWS
AS
SELECT Product_ID
,SUM(Daily_Sales) AS SumSales
FROM Sales_Table
Group by Product_ID;
You can create a global temporary table from a CTAS (Create Table AS) statement as in the above example.
Creating a Global Temporary Table Using a CTAS Join
This table is materialized from a join
CREATE Global Temporary Table Emp_Dept
ON COMMIT PRESERVE ROWS
AS
SELECT E.*, Department_Name, Budget
FROM Employee_Table E
INNER JOIN
Department_Table D
ON E.Dept_No = D.Dept_No;
You can create a global temporary table from a CTAS (Create Table AS) join statement as in the above example.
Creating a Temporary Table from Another's Space
We will first CREATE Temporary Tablespace
CREATE TEMPORARY TABLESPACE tbspace
TEMPFILE 'tbspace_t1.f' SIZE 50m REUSE
AUTOEXTEND ON
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 64K;
We will first CREATE Temporary Tablespace
CREATE GLOBAL TEMPORARY TABLE Emp_Names
( Employee_No number(6) NOT NULL,
First_Name varchar(12) NOT NULL,
Last_Name varchar2(20) NOT NULL )
ON COMMIT DELETE ROWS
TABLESPACE tbspace ;
The Rows materialized with created temporary tables are normally stored in temporary tablespace. Starting with Oracle 11g, users have the option to specify the temp tablespace for a Global Temporary Table. Within a TABLESPACE clause, you can allocate the temporary tablespace with a specified extent size. This is shown in the examples above.
A Global Temp Table That Populates Some of the Rows
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
Above is an example of creating a Temporary table with three columns. The original table had four columns.