- Derived tables
- Volatile temporary tables
- Global temporary tables
What is the reasons to implement Temporary Tables?
>Might not be possible against a normalized table, or
>May require multiple SQL statements to complete.
They are some very useful for demoralizations such as:
And they may also be used for intermediate results which are needed:
>On an on-going basis.
Below are the characteristics of Derived Tables:
- Derived tables are local to the query that means it exists only for the duration of the query.
- The table is automatically discarded once the query is done.
- Spooled rows, which populate the table, are also discarded when query finishes
- It is incorporated into SQL query syntax
- There is no data dictionary involvement
Below is the example for Derived tables.
SELECT T.PROD_ID, T.SUM_SALES, RANK(T.SUM_SALES)
FROM (SELECT PROD_ID, SUM(SALES) FROM SALES_TBL
GROUP BY 1) AS T(PROD_ID, SUM_SALES)
In the above query T is the Derived Table.PROD_ID and SUM_SALES are the derived columns.
Volatile Temporary Tables:
Below are the characteristics of Volatile Temporary Tables
- Volatile Temporary Tables are exists throughout the entire session, not just a single query
- It must be explicitly created using the CREATE VOLATILE TABLE syntax.
- It is discarded automatically at the end of the session or can be dropped manually anytime.
- There is no data dictionary involvement.
- Can be used with multiple queries in the session.
- Volatile Tables are designed for performance improvement.
- Can do collect statistics.
CREATE VOLATILE TABLE vt_deptsal, LOG
ON COMMIT PRESERVE ROWS;
ON COMMIT DELETE ROWS means the data is deleted when the query is committed.This statement allows us to use the volatile table again for other queries in the session
Limitations on Volatile Tables:
- CREATE/DROP INDEX
- ALTER TABLE
- GRANT/REVOKE privileges
- DELETE DATABASE/USER (does not drop volatile tables)
Global Temporary Tables:
Below are the characteristics of Global Temporary Tables
- By using CREATE GLOBAL TEMPORARY command we used to create.
- The main difference between a global temporary table and a volatile temporary table is that the global table definition stored in data dictionar(DD)
- Each instance of a global temporary table is local to a session
- Materialized tables are dropped automatically at the end of the session
- A user can materialize up to 2,000 global tables per session.
- COLLECTing STATISTICS can be possible on Global Temporary tables
- Secondary indexes can be added to a Global Temporary tables
CREATE SET GLOBAL TEMPORARY TABLE PED.gt_deptsal
,NO FALLBACK, LOG
PRIMARY INDEX (deptno)
ON COMMIT PRESERVE ROWS;
The ON COMMIT DELETE ROWS clause is the default.. If you want to use the command ON COMMIT PRESERVE ROWS, you must specify that in the CREATE TABLE statement