I have an interview question ..
What will happen internally in teradata when we try to join 2 tables to find the matched rows on EMP_Name column where EMP_ID column is index but not EMP_Name ?
Is there anything will happend at AMP's side in processing anything like rearrange data ? and what is rearrange in teradata ?
based on primary index we will identify the location of row in the disk. whenever we join two tables then join will be performed in spool based on PI column. if non PI column was choosed for join then within spool join column was treated as primary index and rows are redidtributed to all amps based on hash value. same happens to second table also. after distribution join will be performed locally within spool space of each AMP.