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
When secondary index (USI or NUSI) is created it creates a sub table which contains
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
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.