If the two tables are joined based on PI, no redistribution/duplication will happen as the rows will be in the same AMP and performance will be better. If one table PI is used and Other table PI not used, redistribution/duplication of the table will happen based on the table size.In these cases Secondary Indexes will be helpful.
It helps to avoid full table scans.
Statistics can be collected in both of the tables in TD13 Version. Previously Collecting Stats on Volatile tables are not allowed.
Above query returns the highest paid employee from each department. This is also one of the scenario based questions in teradata.
group by 1;
Split the queries into smaller subsets in case of poor performance.
What does” Pseudo Table” Locks mean in EXPLAIN Plan in Teradata?
Please note - ALTER can be used only to add new columns with compression values to table.