How data retrieval happens with secondary index

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.

How data retrieval happens with secondary index

Hi ,

 

Am new to teradata, Am trying to learn things one by one. I got confused with secondary index.

 

I need to know how data retrieval happens in the case of USI and NUSI. Also I want to know why subtables comes in same amp in the case of NUSI whereas it comes in different amps in USI. Please kindly help me with suitable examples. from query request to data fetch back to parsing engine

2 REPLIES
N/A

Re: How data retrieval happens with secondary index

Have a look at "Chapter 9 Secondary Indexes" in the "Database Design manual"

Re: How data retrieval happens with secondary index

When secondary index (USI or NUSI) is created it creates a sub table which contains

  • Row id of SI.
  • column on which SI is created
  • Row id of primary index

ex:  

sel * from emp where dept = 39

SI sub table

------------------------

SI row id  Dept  PI row id

100,1        39     179,1

 

So when it is included in the where condition

  • PE gets the row hash of the secondary index
  • Using that value AMP finds the corresponding Row id of the primary index in the sub table.
  • Finally it points the base table using the PI row id to get whole row.

Row Distribution:

In case of USI rows in sub table are distributed based on SI Row id i.e they are in different AMP's where as in NUSI row are distributed based on base table row id (PI row id) which is why sub table row and corresponding base table row present in same AMP.