Hello Teradata Gurus/Enthusiasts,
Primary Index is esentially how TD will decide how rows are distributed across Amp's. Under Ideal conditions PI (UPI,NUPI) are one Amp operations. Since all unique row Id records are put in an unique Amp and when we submit a sql ("Select * from Employee where Emp_Id=1;" PI is on Emp_id) it just points to the AMP where the record is stored.
But what if I have a query that says "Select * from Employee where Emp_Id=1 And Emp_Name= 'Sid';" (NUPI on Emp_id and no other indexes) How many Amp operation is it? Is it just one Amp? Or How does PE/Amp's deal with Inequality conditions, conditional operations (<,>) ??
Can anyone explain me the PE/Amp level functioning of the query above?
-Thanks in Advance
PI query is always an 1 AMP operation. If it is UPI, it will return 1 row and if it a NUPI, it will return more than 1 rows. So, even if you add a condition - name not like '%SID%' and emp_no=1 - in the 2nd condition, it will do an 1 amp operation and search all rows, and return you more than 1 row. You can check this in the explain plan. it will say 1 amp operation.