Indexes in Teradata

  • Share this blog:



Primary Index

  • Mandatory index
  • Only one PI for table
  • We can take a P.I till 64 columns[API is composite till 64 columns]
  • It is a physical mechanism to assign a row to amp
  • It affects a data distribution
  • OPI takes only unique values
  • NUPI takes duplicate and null values
  • PPI for range base query
  • If don’t specify any query system creates PI by default
Want to acquire industry skills and gain complete knowledge of Teradata? Enroll in Instructor-Led live Teradata Training to become Job Ready!


Real-Time Advantages

  • For better distribution (or) uniform distribution
  • For better access plan
  • For better retrieval
  • For better join plan
  • As it is a single Amp operation performs it is operating faster etc.  

Creating Alternating Dropping

  • We cannot drop P. I [it we want to drop PI. The table first and then change the PI]
  • The table is empty we can alter the PI
  • The table is having data we cannot alter PI  

PI Usage

  • Generally, we take in the below situation
  • Frequently used column
  • Frequently used no- indexed column in the where clause 
Read these latest Teradata Interview Questions that help you grab high-paying jobs

No primary index in where clause

i) If you are not able to decide and which columns

ii) We go for PI than create No PI, So that no distribution happens

iii) Whenever we fell comfortable then we convert NOPI

IV) Available NOPI from tera data 13 onwards.  

No PI real-time usage

a) Loading data into stage tables

b)Using FL and T pump Array insert at the time FL and T pump array insert etc.  

Hash Map

It is created at the time of tera data soft installation It’s provides the relationship b/w Amp’s address [basic number] And row host values it the no. of the same in the two system hash map it also be same

For Indepth knowledge on Teradata click on:

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.