Indexes in Teradata

 

Indexes

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

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

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 Droping

• 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 

Teradata Interview Questions

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:

• BTEQ Features in Teradata
• Secondary Index in TeraData
• Memory Management in TeraData
• Types of OLAP’s in TeraData
• TPUMP Structure and Process In TeraData