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