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 )

Give the sizes of SMALLINT, BYTEINT and INTEGER.

1210

---

How do you define Teradata?

1087

---

Difference between inner join and outer join?

1081

---

Difference between star and snowflake schemas?

1239

---

Where is teradata rdbms used?

1137

---

What are some commonly used bteq scripts?

1058

---

What do you mean by tpt in teradata?

1063

---

Name the five phases that come under MultiLoad Utility.

1027

---

What is teradata and why it is used?

1180

---

Explain teradata utilities?

1093

---

What type of indexing mechanism do we need to use for a typical data warehouse?

1226

---

Difference between multiload and tpump?

1157

---

What is a sparse index?

1119

---

What is meant by a Highest Cost Plan?

1112

---

What is node? How many nodes and amps used in your previous project?

1152

---