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 is the difference between global temporary tables and volatile temporary tables?

1181

---

What is the basic syntax for teradata select statement?

1040

---

Is it necessary to add? Quit statement after a bteq query when I am calling it in a unix environment?

1240

---

What happens in a conflict? How do you handle that?

1047

---

Explain some differences between mpp and smp?

1098

---

While creating table my dba has fallback or no fallback in his ddl. What is that?

1184

---

Discuss the advantages of using partitioned primary index in a query?

952

---

What is the primary index in teradata?

1199

---

What are the various indexes in teradata?

1046

---

Highlight the limitations of TPUMP Utility.

1111

---

Why are oltp database designs not generally a good idea for a data warehouse?

1056

---

What are the different softwares used with their functions in teradata?

1015

---

what are the uses of fact table and dimension table in banking project?

4556

---

What is a node in teradata?

997

---

What is dimensional modeling?

1085

---