All Forums General
v_gabrielli 33 posts Joined 04/10
22 Dec 2010
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

Vince
WAQ 158 posts Joined 02/10
24 Dec 2010

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 23 posts Joined 03/10
31 Dec 2010

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,

jana.teradata 11 posts Joined 02/10
06 Jan 2011

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

jana.teradata 11 posts Joined 02/10
06 Jan 2011

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...

ztpruit 4 posts Joined 10/08
02 Jun 2011

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).

ztpruit 4 posts Joined 10/08
02 Jun 2011

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

tdstar.rams 11 posts Joined 05/11
14 Jun 2011

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

A.raghuram

abhi_insignia 13 posts Joined 06/11
19 Jun 2011

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?

mnylin 118 posts Joined 12/09
21 Jun 2011

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.

You must sign in to leave a comment.