Can anybody tell me what is an all-amp operation from the following:
1- Set Processing
2- Row Processing
Set processing is an all AMP action and uses parallism. Row processing processes one row at a time and has less lock retention. It is not parallel.
One cannot say for certain without more information.
Very often "set processing" will be all-AMPs - but not always.
And it's possible (and usually desirable) for "row processing" to be single-AMP, but it can easily be all-AMPs (if not based on PI values).
I vivdly differentiated between Row- and Set-Processing here (the beginning of a series of blog entries on how to convert cursor-based ["flat"] logic to Set SQL). The AMP is the unit of parallelism in Teradata set processing. When you see an all-AMPs operation, that means Teradata is doing parallel processing, and that is what makes it fast, but if it happens when you are processing one row at a time, it will make the process unbelievably slow.
Row processing is normally associated with transaction-oriented processing. For example, suppose you are a telephone company and there is a Call Center application that looks up the phone number based on the primary account holder's date of birth. It is highly unlikely that Birth_Date is the Primary Index of the Account table, so Teradata will probably have to read the entire table looking for accounts that match. That would be an all-amps operation - each AMP only has to look at its subset of the data, and the search would be as fast as possible, but not fast enough for the person talking to the Call Center. The all-amps operation could be eliminated by adding a single-table join index (STJI) on Birth_Date; then you would need only one AMP to locate a list of phone numbers that match. Good transaction design requires an emphasis on queries that only need one or two AMPs, and that means accessing rows by primary index, STJI or secondary index. On the other hand, good reporting and analytical design requires an emphasis on set processing: using all the AMPs in parallel.