Answer Posted / sri krishna

Teradata automatically Creates the Primary index , if not
specified by the DDl for Table .Some of the Other Types of
indexes are
Secondary Index
Partitioned Primary Index
Join Index


Primary Index :- the PI is the way , Teradata Distributes
the Data , Accesses the Data to/from Amps. By Using the
Primary Index with Value or Range of Values in the Where
Clause of the Query , the operation Becomes a Single AMP/
Few amp aperation instead of all amp operation . the Access
path is
Rowhash of the Pi is known --> Amp of the Column along
with rowid of the Column . (1 Amp Ops)

Results
1. Highly Optimised Query Retrival
2. less Cost per Retrival

Secondary Index / Join Indexes :-

Creates a Secondary Index / Joinindex / Hashindex
Subtable , which maps the Column/s of the SI and the PI
Hash /Join(Frequently used Columns in two tables in this
Case ) , The operations , if using these Indexes are
basically 2 or more amp operation .

Results
1. Quicker Query Retrival time than a full table scan .
2. costs less on the Resources than a FTS
3. the Performance is improved if the JI is used by the
Optimizer

cons --

1. Additional Space Req. for the Subtables
2. need to be droped and recreated for Loads to happen
(Multi Load , FastLoad)ect , which is a real PAIN !!!
3.still be a 2 amp operation when compared to PI Retrival
for a SI.


PPI :-

Partitioned Primary Indexes are Created so as to divide the
table onto partitions based on Range or Values as Required .

the data is first Hashed into Amps , then Stored in amps
based on the Partitions !!! which when Retrived for a
single partition / multiple Partitions , will be a all amps
Scan, but not a Full Table Scan !!!! . this is effective
for Larger Tables partitioned on the Date Specially !!!
there is no extra Overhead on the System (no Spl Tables
Created ect )

What do you mean by teradata sql assistant?

995

---

How to find duplicates in a table?

1129

---

Explain amp in teradata?

1001

---

What are the components used in smp and massively parallel processing (mpp) machines?

999

---

What interface is used to connect to windows based applications?

1031

---

How to identify ppi columns?

1097

---

Explain the types of join supports by teradata?

933

---

How can we check the version of Teradata that we are using currently?

1012

---

How do you create tables? Exact syntax, and create profiles, users in teradata?

969

---

What is collect statistics?

946

---

What is the difference between union and union all in teradata?

1043

---

What is TPD?

1306

---

Mention a few of the ETL tools that come under Teradata.

1009

---

If Fast Load Script fails and only the error tables are made available to you, then how will you restart?

1079

---

Which is more efficient group by or distinct to find duplicates?

1027

---