Interview Questions   Tutorials   Discussions   Programs   

Teradata - How does indexing improve query performance?

asked mar September 20, 2014 06:38 AM  

How does indexing improve query performance?


1 Answers

answered By vishnoiprem   0  
Teradata automatically Creates the Primary index , if not specified by the DDl for Table .
Teradata index are

  • PI
  • Secondary Index
  • PPI
  • Join Index
  • HASH 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 operation 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 Retrial
2. less Cost per Retrial

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 )

HASH index and join indexes are also used for performance improvement
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!