Create Volatile Table in Teradata

Definition:

CREATE [SET | MULTISET] VOLATILE TABLE table_name

(

column_name data_type [NULL | NOT NULL]

[, ...]

)

[ ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]

The default value is ON COMMIT DELETE ROWS. It specifies that at the end of a transaction, the table rows should be deleted. The ON COMMIT PRESERVE ROWS option provides the more normal situation where the table rows are kept after the end of the transaction. If the rows are going to be needed for other queries in other transactions.

Example:

CREATE MULTISET VOLATILE TABLE performance_test_tmp (

col1 integer NOT NULL ,

col2 integer NOT NULL ,

col3 integer NOT NULL)

ON COMMIT PRESERVE ROWS;

INSERT INTO performance_test_tmp (col1, col2, col3) VALUES (1,2,3);

SELECT * FROM performance_test_tmp;

Note: If the table created with “ON COMMIT DELETE ROWS” then it will not return any row in the select statement as after the insert the table rows would be deleted.

More about Volatile Table:

· Any volatile tables you create are dropped automatically as soon as your session logs off.

· Volatile tables are always created in the login user space, regardless of the current default database setting. That is, the database name for the table is the login user name. Space usage is charged to login user spool space.

· Each user session can materialize as many as 1000 volatile tables at a time.

· To create a volatile table, you do not need any privileges.

Limitations:

The following CREATE TABLE options are not permitted for volatile tables:

· Permanent journaling

· Referential integrity constraints

This means that a volatile table cannot be the referencing or referenced table in a referential integrity constraint.

· Check constraints

· Compressed columns

· DEFAULT clause

· TITLE clause

· Named indexes

· PARTITION BY clause

References to volatile tables are not permitted in FastLoad or MultiLoad.