Secondary Index Archives

Optimizing Teradata Performance through Statistics and Primary Index Selection

04/11/202605/03/2023 by Roland Wenzlofsky

1. Statistics In Teradata, understanding and managing statistics is essential for optimizing database performance. Statistics provide the optimizer with precise data about stored information, allowing for well-informed decisions when handling queries. This article will explore the significance of statistics in Teradata, their effect on query performance, and recommended methods for upkeep. The Role of Statistics …

Teradata MERGE INTO vs. UPDATE: Performance Comparison and Limitations

04/11/202605/02/2023 by Roland Wenzlofsky

Teradata MERGE INTO vs. UPDATE This article compares the UPDATE statement to the MERGE INTO statement, analyzing their respective performance differences and limitations. The Teradata MERGE INTO statement positively impacts performance by reducing I/O operations through the following properties. MERGE INTO offers an advantage in lower IOs as Teradata processes each data block only once …

Teradata Tactical Workload

04/11/202605/02/2023 by Roland Wenzlofsky

Introduction This blog post discusses tactical workloads on a Teradata system. Despite Teradata’s implementation of features that support tactical workloads, this workload category remains challenging to manage. Selecting an optimal physical design is essential to meet user expectations for query speed. Designing the Teradata tactical workload on a test environment can be frustrating, especially when …

Designing Teradata Row Partitioning for Optimal Performance in Tactical Workloads

04/11/202605/02/2023 by Roland Wenzlofsky

Teradata is commonly used for tactical workloads and OLTP applications in my projects. However, it is crucial to avoid designing databases carelessly. Teradata excels as a database for strategic data warehousing. High-performance queries can often be achieved without special design techniques. Keeping statistics current and correctly designing row partitioning is typically sufficient, while other indexes …

The Importance of Up-to-Date Statistics for Teradata SQL Tuning

04/11/202605/02/2023 by Roland Wenzlofsky

1. Complete and up-to-date Statistics At the start of Teradata SQL Tuning, statistics are a vital concern. The Teradata Optimizer employs statistics to formulate the optimal execution plan for our query. The adequacy of statistics or dynamic AMP sampling varies according to the data demographics. To initiate optimization, updated statistics must be provided to the …

Maximizing Performance with Teradata Dynamic AMP Sampling: An Introduction

04/11/202605/02/2023 by Roland Wenzlofsky

Introduction to Teradata Dynamic AMP Sampling Teradata calculates dynamic AMP samples for indexed columns (PI, USI, NUSI) at runtime without requiring statistics. These samples provide key information, including table cardinality and distinct values. They are stored in the FSG cache of each AMP’s table header. This process is referred to as dynamic AMP sampling. A …

Teradata Statistics Collection for Improved Query Performance

04/11/202605/02/2023 by Roland Wenzlofsky

Learn how to optimize your Teradata queries with statistics collection. Discover what to collect and what to omit for accurate query estimation.

The Importance of Minimizing Teradata I/O: Understanding Logical vs. Physical IOs and Their Impact on Performance

04/11/202604/28/2023 by Roland Wenzlofsky

Why is Teradata I/O so crucial? Minimizing Teradata I/O is a crucial aspect of performance tuning. IOs involve transferring data from storage to main memory, which is essential for Teradata to process data. Transferring data to the main memory is significantly slower than accessing data in the main memory or CPU cache. Minimizing IOs can …

Comparing the Architecture of Amazon Redshift and Teradata: Similarities and Differences

04/11/202604/28/2023 by Roland Wenzlofsky

This post will contrast the architecture of two widely-used database systems. The similarities between Teradata and Amazon Redshift are notable, as much of your knowledge about Teradata can be applied to Amazon Redshift. Although Amazon Redshift stores data in columns permanently, the similarities remain significant. Teradata can store data in columns, though it was not …

Understanding Teradata Load Isolation

04/11/202604/28/2023 by Roland Wenzlofsky

Isolation Levels and their Impact on Performance & Concurrency Isolation is a crucial factor in determining the visibility of transaction integrity to database users. This property guarantees that concurrently executed transactions produce identical results to those executed sequentially. Nonetheless, relinquishing this requirement can enhance transaction concurrency, improving performance. However, this also implies accepting inconsistent outcomes. …