Volatile tables

General
Enthusiast

Volatile tables

Hello fellow members quick question... if I create a volatile table
1. Can I create a Primary Index?
2. Can I create a secondary Index?
3. Can I capture Stats on the table?
if yes, should I capture stats on Volatile table?

thks
9 REPLIES
WAQ
Enthusiast

Re: Volatile tables

I am not exactly sure about this but I can share my thaught on this. May be this helps me in cleafying my concept as well.
You can NOT define indexes on VTT (Volatile Temp tables). However you can define PI/UPI and Primary key as well.

If primary key is different from primary index in a table then Teradata apply USI on primary key column, to ensure uniquesness. But you can not define secondary index explicitly on VTTs.
mjj
Teradata Employee

Re: Volatile tables

Hi,
Following information could help you.

We can define primary index on volatile tables.
Secondary index creation is not allowed on VTT.
And we can collect stats on VTT. We can collect stats for both index and columns.

Regards,
Enthusiast

Re: Volatile tables

Hello fellow members quick question... if I create a volatile table
1. Can I create a Primary Index?
yes you can.. in fact its a must in TD for any table
2. Can I create a secondary Index?
yes you can create a secondary index as well
3. Can I capture Stats on the table?
NO you can not collect stats on Volatile tables. although you can collect the stats on Global temporary table. its an other form of temporary table
...........

hope this helps you
Enthusiast

Re: Volatile tables

and an other point to add to the above explanation.... if you have a primary key on a column other than primary index.. that column would be unique secondary index by default...

Re: Volatile tables

Let me say that Jana is 100% correct for Teradata versions through TD12. In TD13 and above we have a little more freedom.

Primary Indexes are no longer required in Teradata 13 or above. However, you will only be able to insert and delete from the table (no update, upsert, or merge). The main benefit to NoPI is faster INSERTs. IMHO, this is really only a benefit when creating staging tables for fastloads.

Teradata 13 and above will allow you to Collect Stats on Volatile Tables (as well as partitioning columns and Join and Hash Indexes).

Re: Volatile tables

and I capture stats on all my volatile tables...
Enthusiast

Re: Volatile tables

1.) We can create PI
2.) We can create SI
3.)We can capture Stats,if it is TD13 0r above
for Volatile tables
Enthusiast

Re: Volatile tables

Hi. As explained in one of the above post, a table with NOPI will help in faster insert and delete from the table since the need for redistributing to hash-owning amps will be obviated. But, won't this impact the fetching of rows on certain filter condition?
Enthusiast

Re: Volatile tables

A select that filters from a NOPI table that results in a full table scan shouldn't perform any worse than the same query on a table with a PI. In fact, it might perform better if the PI for the traditional table has poor distribution. A full table scan isn't impacted by the PI selection unless distribution is bad. If, however, you're filtering on a field that could be a PI choice (selective, good distribution, commonly accessed, etc.), then you would see a decrease in performance on the NOPI table compared to a table with those fields defined as the PI.