How does indexing improve query performance?

Answers were Sorted based on User's Feedback

How does indexing improve query performance? ..

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

How does indexing improve query performance? ..

Answer / 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)

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

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

How does indexing improve query performance? ..

Answer / yuvaevergreen

PI is very important in TD, because the data distribution
is basically dependent on PI.
1.Data distribution
2.Fastest way to retrieve rows
3.Important for joins

Is This Answer Correct ?    3 Yes 1 No

How does indexing improve query performance? ..

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

Post New Answer

More Teradata Interview Questions

IBM QUESTION As all we know PE takes the SQL Request and it will give responce for that query. what is the internal process it is taking that sql from the system????? thanks in Advance

2 Answers   Infosys, Cap Gemini, IBM,

how do we write scripts in unix how to execute scripts in real time anybody please needfull or give me number i will cal u

0 Answers  

How can we pass Variable in Bteq with out using shell script Is it possible or not If possible let me know how can we pass varaibale

1 Answers  

which option is used to restart the fastexport script ?

2 Answers   TCS,

Hi friends This is suneel and i am new to teradata. In both situvations teradata administrator and teradata sql assitant 12.0 1)How to create database in teradata and under database how to create user. 2)How to create table under that user and giving privilizes to that user. Thanks alot.

1 Answers  

Hi frinds pls help me regarding this question WHY usi is two amp operation in teradata? THANKS IN ADVANCE....

3 Answers  

There is a load to the Table every one hour and 24/7.Morning trafic is high, afternoon trafiic is less, Night trafiic is high.According to this situation which Utility you use and how do you load,which utility used?

4 Answers   IBM,

Fast Load Script is failed and error tables are available then how do you restart?

3 Answers   TCS,

Does SDLC changes when you use Teradata instead of Oracle?

2 Answers  

if a error occured in FASTLOAD is the fastload job stops?

1 Answers   CTS, Deloitte,

what is identity column in TD?

2 Answers   Wipro,

Can some one tell me the ressolution for the error. I was not able to answer this question in wipro interview. "The transaction exceeded the maximum number of rowhash locks allowed"

1 Answers   Wipro,