Data distribution happens on basis of PI.
After that if I create a NUSI on a column it will create a subtable in each amp to hold Secondary index information.
then Teradata use Secondary index subtable to fetch primary index rowid and inturn fetch table row.
But how does teradata know which amp to check for secondary index value or it checks all the amps.
NUSI access is always described as 'all AMPs', TD checks all AMPs.
It will only be different if you are using TD 16.10+ and the table is stored on a sparse MAP, in which case it is 'all AMPs in the MAP' or if the table is a PPI table and you have a NUSI on the same column(s) as the PI, in which case it will be single AMP.
But the basic case is 'all AMPs'.
ok, then why so much difference in USI and NUSI . if am not wrong USI use hasing and store the value of secondary index in a subtable that might be in different AMP and which makes USI a 2-AMP opration but this is not the case with NUSI why.
Same logic can be applied to store NUSI also and inseted of all AMP scan it will be a 2 amp scan also.
You're correct that USI access is 2-AMP, for exactly the reason that you have described.
NUSI rows are not hash-distributed, they are 'amp local' to the base row. i.e. they are on the same AMP as the data row. That is the way Teradata builds NUSIs.
Also note that if what you really want is a 'hash distributed NUSI' then you could create a 'hash index' or a 'single table join index' - either would be very close to what you want.
But a standard NUSI is not hash distributed.
A NUSI is always AMP-local, i.e. indexing data within the same AMP.
create index NUSI (mydate) on mytable; -- is internally created as a table like this: create table NUSI
( overhead byte(7), -- internal overhead
mydate date, -- indexed column
baseROWID byte(8|10|16) -- no/2-byte/8-byte partitioning
) no primary index -- AMP-local