How does indexing improve query performance?
- 4 Answers
- 19823 Views
- Satyam, I also Faced
- E-Mail Answers
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 )
| Is This Answer Correct ? | 19 Yes | 5 No |
Post New Answer View All Answers
What do you mean by parsing?
What does sleep function does in fast load?
What is spool space? Why do you get spool space errors? How do trouble-shoot them?
How does hashing happens in teradata?
Can we have two time dimensions in a schema(either star or snow flake)? For ex if we want joining date of employee and if we want today's sales with time whether can we have two time dimensions for accommodating above tasks?
Explain fallback in teradata?
Explain and compare pros and cons of snow flake schemas?
What is teradata?
What is smp and mpp platforms?
How to eliminate product joins in a teradata sql query?
If a Node is busy what are the steps you can take to avoid ?
i learn teradata,it is so intersting,now i want teradata certifications ,so any 1 help me to get teradata certifications TD 12 ? email:kdcrazyy@gmail.com
Explain vproc in teradata?
What is multi insert?
What is the multi-insert?
