Volatile Tables in Teradata - How and when to use volatile tables

Volatile Tables:

There are temporary session specific tables in Teradata. They are created in the spool-space of the user.

Advantages:

1. In query tuning, we use volatile tables to hold session-specific data
2. Large joins can be broken down into temporary volatile tables. But, if the join is an AMP-local join, no need to create a Volatile table


Key considerations:  Consider the performance benefits before creating volatile table.

Collect Statistics on Volatile table may help the optimiser understand the Bias and the data volume.

Example Query:

CREATE MULTISET VOLATILE TABLE WRK_FACT_SHC_DLY_OPR_INS
AS
(
SELECT
A.VEND_PACK_ID,
A.LOCN_NBR,
(A.DAY_NBR + 1) AS DAY_NBR,
A.MDS_STS,
A.RPL_TYP_CD,
A.INS_TYP_CD,
A.INS_SUB_TYP_CD,
A.TTL_UN_QT,
A.TTL_CST_DLR,
A.TTL_SLL_DLR,
A.PVE_UN_QT,
A.PVE_CST_DLR,
A.PVE_SLL_DLR
FROM
  $TD_TBLS_DB.FACT_SHC_DLY_OPR_INS A
WHERE  A.DAY_NBR = (SELECT DAY_NBR FROM MAX_DAY_NBR_CURR_DT)
group by VEND_PACK_ID,LOCN_NBR,DAY_NBR,MDS_STS,RPL_TYP_CD,INS_TYP_CD,INS_SUB_TYP_CD,TTL_UN_QT,TTL_CST_DLR,TTL_SLL_DLR,PVE_UN_QT,PVE_CST_DLR,PVE_SLL_DLR
) WITH DATA
PRIMARY INDEX (VEND_PACK_ID,DAY_NBR )
ON COMMIT PRESERVE ROWS;