Temp Tables - Part 3 - Volatile tables

Volatile temporary tables:

• Uses Spool space.

• No data dictionary access needed.
   

• Table definition is kept in cache.
   

• Table is local to session and not the query.
   

• Table can be used multiple times with in the session.
   

• Volatile tables can be dropped any with within the session using DROP TABLE. However if we don’t the table will get dropped automatically at the end of the session.
   

• The volatile table must be explicitly created using the CREATE VOLATILE TABLE syntax.
   

• Volatile tables don’t survive system restart.

Example of creating volatile table.

CREATE VOLATILE TABLE V_TEMP, NO FALLBACK

(

Empid integer,

Salary decimal(10,2)

Deptno integer

) ON COMMIT PRESERVE ROWS;

Note the highlighted 'ON COMMIT PRESERVE ROWS'  allows us to use the same volatile table again and again within the session.

• BY default its 'ON COMMIT DELETE ROWS' , which means the data will be deleted after the query is committed.

• For volatile tables we can also request a NO LOG option which means the transaction journal will not be used.

Example

CREATE VOLATILE TABLE V_TEMP, NO FALLBACK , NO LOG

(

Empid integer,

Salary decimal(10,2)

Deptno integer

) ON COMMIT PRESERVE ROWS;

LOG is default. Which means transaction journal will be maintained.

• Irrespective of whether we explicitly specify or not the volatile tables are created under userid logged in.

CREATE VOLATILE TABLE username.table1 -->      (Explicit)
CREATE VOLATILE TABLE table1              -->  (Implicit)
CREATE VOLATILE TABLE databasename.table1 -->  This will give is an error message if the databasename specified is not actually the username.

• Different sessions can use the same volatile table name. But a volatile table cannot use a name that is used by any of the following objects under the user id.

Permanent tables.

Temporary tables.

Views.

Macros.

• We can create volatile tables with FALLBACK, however as these tables don’t survive system restart having fallback does not add much value. On the contrary they would take twice the spool space.

• We cannot use following while creating Volatile tables

1. Permanent Journaling

2. Referential integrity. Referential integrity means relation between tables, which is stored in DBC. As volatile table don’t need data dictionary we cannot have referential integrity

3. Check constraints

4. Column compression.

CREATE VOLATILE TABLE         TEST1         ( salary integer compress 0 ) on commit preserve rows;

Above query would fail with below message:

CREATE TABLE Failed. 3706:  Syntax error: COMPRESS option not allowed for a volatile table.

5. Default values for columns

CREATE VOLATILE TABLE         TEST1         ( salary integer default 0 ) on commit preserve rows;

Above query would fail with below error message:

CREATE TABLE Failed. 3706:  Syntax error: DEFAULT option not allowed for a volatile table. 

6. Column titles

7. Named indexes.

• We cannot use HELP DATABASE command to find all the volatile tables under a userid. Reason being volatile tables are not stored in data dictionary

To do that we have to use the command HELP VOLATILE TABLE ;

This will show all the volatile tables under a particular user id.

HELP VOLATILE TABLE;

Table Name	Table Id
TEST1	06C4AA600000

• Following are the commands that we cannot run on VT's

1. HELP and COLLECT STATS: This is no longer true with TD13. With TD13 we can collect stats on volatile table.

2. CREATE/DROP INDEX : we cannot have indexes on volatile table.

3. ALTER TABLE.

ALTER TABLE TEST1 ADD  salary decimal(10,2)

Following is the error we would get

ALTER TABLE Failed. 5341:  Volatile table 'TEST1' not allowed in statement. 

4. GRANT and REVOKE privileges

• Volatile tables cannot be renamed.

• Volatile tables cannot be loaded using multiload.

Temp Tables- Part 1 - Introduction

There are 3 types of temp tables:

1. Global temp tables

2. Volatile tables

3. Derived tables

What are the disadvantages of using permanent tables for temporary purposes:

1. We need to have separate steps to create and populate tables

2. It takes perm space to create these tables.

3. We need to drop the table explicitly after we are done using the tables.

4. Data dictionary access is needed for creating and dropping the tables.

Derived Tables	Volatile temp tables	Global temporary tables
These tables are local to the query. The tables exists for the duration of the query Makes use of spool space. No data dictionary involvement needed Tables are created using SQL incorporated within the query.	These tables are local to the session and not just the query. The table is discarded at the end of the session. Makes use of spool space. No Data dictionary involvement needed Table must be explicitly created using the CREATE VOLATILE TABLE syntax.	This table is also local to the session and not just the query. The Table instance is discarded at the end of the session. The way Global temp tables are different from the volatile table is that global table has a definition in the Data dictionary . This data definition can be shared by multiple users. Makes use of temp space. Data dictionary involvement necessary as the definition is stored in data dictionary. The table definition in the data dictionary is created using the CREATE GLOBAL TEMPORARY TABLE syntax. The instances of the tables are materialized using many other ways explained later.