How does indexing improve query performance?
Answers were Sorted based on User's Feedback
Answer / praveen
By creating index on single or multiple columns, query
takes index access path instead of full table scan. Full
table scan is costilier operation where it has to visit all
rows in the table whereas index operation just accesses the
index sub table with required hash value and joins with the
basetable using rowid. This improves response time and
performance of the query.
|Is This Answer Correct ?||28 Yes||3 No|
Answer / sri krishna
Teradata automatically Creates the Primary index , if not
specified by the DDl for Table .Some of the Other Types of
Partitioned Primary 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
Rowhash of the Pi is known --> Amp of the Column along
with rowid of the Column . (1 Amp Ops)
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 .
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
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.
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|
Answer / yuvaevergreen
PI is very important in TD, because the data distribution
is basically dependent on PI.
2.Fastest way to retrieve rows
3.Important for joins
|Is This Answer Correct ?||3 Yes||1 No|
Answer / guru
ONE LINE ANSWER: INdexing acts as SET option (Where clause)
while an query is being executed provided the query
contains these indexes.
Advanced Teradata Certified Professional
|Is This Answer Correct ?||1 Yes||5 No|
why varchar takes 2 extra bytes?
what is use of PI(primary index) AND SI(secondary index)
IBM QUESTION As all we know PE takes the SQL Request and it will give responce for that query. what is the internal process happening.how it is taking that sql from the system????? thanks in Advance
any one answer me how they can analyzing the project using data ware housing?
Hi All, I have a table with 3 fields like id,mark1,mark2 and I would like to update a mark3 field that would calculate the max for each record (so the max value of the 2 fields) in Teradata ID Mark1 Mark2 Mark3 1 10 20 2 20 30 3 40 10 4 50 50 I Have to write a update statement Mark3 with max value of mark1,mark2 fields…like bellow ID Mark1 Mark2 Mark3 1 10 20 20 2 20 30 30 3 40 10 40 4 50 50 50 Please any one help me ....Thq
A Query was run fine earlier. It is not running properly now. what are proactive steps you can take as a dba ?
Difference between Qualify and group
Aborted in Phase 2 data acquistion completed in fastload?
how to load or extract the error table data ?
can we have an unconnected lkp to lookup a DB2 record against a Teradata record?
How do you load Multiple files to a table by using fastload scripts?
what is the query for retrieve the unique values(not display the duplicates values) from single table?