How can I force Teradata to create a table in memory?

Question

Teradata has an "intelligent" in-memory engine that dynamically loads some data into memory based on usage.

How can I force it to create or store a table in memory all the time?

Answer 1

The answer to your question and other information about Teradata Intelligent Memory (TIM) can be found in the Orange Book about Teradata Intelligent Memory. Also in the appendixes are some SQL samples to query Teradata to verify what the temperature of a specific table is.

Please be advised that there is a general advice to NOT load data directly as "very hot", but to "hot" and let TVS then handle the temperature characteristics. Otherwise cooling down could be a problem.

To directly answer your question, there are 3 options:

1) Wait and access the table until TVM sees that the data is "very hot". If normal query characteristics access the table often there is a high chance the table will get loaded in memory and stay there.

2) Use a queryband to force it to "very hot"

SET QUERY_BAND='TVSTEMPERATURE_PRIMARY=VERYHOT;' FOR SESSION;

However you need to have access to the macro DBC.VHCTRL(). Otherwise TD will silently set your data to "hot".

3) Use FERRET:

> force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
FORCE command changed the temperature of table ADW_DB.INVOICE
to VERY-HOT.
The temperature of 638 cylinders have been changed to VERY-HOT.

Please also note that a DBA is not amused if you suddenly load a huge table in TIM in production, forcing other data to not reside in TIM. There is a reason why the queryband is restricted...