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 column]
If is physical mechanism to assign a row to amp
It effects 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
Real Time Advantages
For better distribution (or) uniform distribution
For better access plan
For better retrieval
For better join plan
As it is single Amp operation performs it is operation 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 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
Learn more about Teradata Interview Questions in this blog post.
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 an same in the two system hash map it also be same
For Indepth knowledge on Teradata click on: