Could anyone explain me how PPI can affect performance
How does it affect the performance of joins? does it improve it
Is it more useful for in case of retrival than joining ?
Does it have to be part of the PI defined ?
Its a very generic question and it totally matters on the demographics of the data and the way it is queried. The queries performance increases if the query is doing partition elimination, but if you are not selecting any target partitions then the each partition is checked against the index value which could be exhaustive...
You can read through the design consideration from following teradata design document. There are plenty of options well explained for different tyes of partining schemes. Here is the link...
PPI affects Performance by restricting the amount of data on which the Join is performed. Lesser the data, quicker is the Join operation. If your PI is unique, then PPI must be implemented on the same columns, but there is no such restriction with NUPI. If both PPI and PI are used in Join operation, performance will be very good. If either of them is specified, then it depends upon the uderlying table. For only PPI specification, Joining will be slower than the first case, but data retrieval will be good and faster. For only PI specification, the whole data retrieval process will take time, thereby leading to the worst case of all the 3 scenarios considered. Hence, PPI (if implemented, must be used more often as possible).