The following are working points that will help you understand performance optimization from a Teradata database perspective, and how bad PI and bad join affects the performance of a database. Many a time, it only takes a single/few bad query(s) to bring database performance down:
- Check primary index (PI): What is a bad PI? When the table is skewed, mostly because of a large number of duplicate values on the column(s) that make the PI, a bad PI. PI is directly responsible for:
- Row distribution
- Access path
- Join performance
- Check statistics: Statistics can break and make a query. The Optimizer is more accurate when it has collected statistics. It is more conservative when it must rely on dynamic AMP sampling. Statistics ...