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.