Understanding Teradata Statistics Histograms: How the Optimizer Estimates Cardinality for WHERE Conditions

Illustration of a stopwatch dial on an orange paint-splash background

Teradata Statistics Histograms – A Short Introduction Many are familiar with the Optimizer’s statistical confidence levels. I was recently surprised to discover that a “high confidence” rating does not guarantee a fully accurate estimation (provided the statistics collected are not stale). While I remain hopeful that my observations may be attributed to a bug, I wanted …

Read more

Teradata Join Strategies: Merge, Hash, Nested and Product Joins Most read

Illustration of a stopwatch dial on an orange paint-splash background

Introduction Teradata offers several methods for conducting joins, but all necessitate one prerequisite. 🎓 Prefer video? Lectures 8 and 10 of my Udemy course Teradata Performance Tuning in Practice cover this topic: “Merge, hash, nested and product joins” and “Sliding-window merge join on partitioned tables”. See the course on Udemy → The paired table rows …

Read more

Teradata Performance Tuning: A Practical Guide

Illustration of a stopwatch dial on an orange paint-splash background

The Art of Teradata Performance Tuning As a Teradata Performance Tuner, technical expertise and experience are essential, occasionally accompanied by fortuitous circumstances. I’ll demonstrate the remarkable outcomes that can be attained by rephrasing a query using this example. Assuming this scenario: One table has a minimal number of rows, while the other is partitioned and …

Read more

Understanding Deadlocks in Teradata: Prevention and Handling Strategies Widely read

Illustration of a person in a suit standing beside a database cylinder

What are Deadlocks in Teradata? Deadlocks arise when two transactions hold locks on database objects required by the other transaction. Here is an example of a deadlock: Transaction 1 locks some rows in Table 1, and transaction 2 locks some rows in Table 2. The next step of transaction 1 is to lock rows in table 2, and …

Read more

Teradata MERGE INTO vs. UPDATE: Performance Comparison and Limitations Widely read

Illustration of a glossy orange database cylinder with data fragments across its surface

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. The displayed historical MERGE plan avoids the update spool shown in the comparison. MERGE can use …

Read more

How Join Indexes Can Optimize Performance in a Normalized Data Model

Illustration of a stopwatch dial on an orange paint-splash background

A normalized data model can increase the complexity of creating performant queries due to the higher number of tables that must be linked compared to a denormalized data model. It is essential to select a primary index precisely to optimize queries and joins, enabling them to have a direct access path. However, relationship tables often …

Read more

High-Performance Calculations with Teradata Ordered Analytical Functions Popular

Illustration of a glossy orange database cylinder with data fragments across its surface

For a true calendar-day window, aggregate to one row per department/day and supply missing dates, or use a date-bounded query. With duplicate dates, add a meaningful tie-breaker for a ROWS frame. Adding an ORDER BY or PARTITION BY column can change the result and must preserve the intended business semantics. Teradata employs two distinct approaches …

Read more

Designing Small Reference Tables for Teradata: Storing All Rows on One AMP for More Efficient Queries Popular

Illustration of a stopwatch dial on an orange paint-splash background

When designing tables for Teradata, it is important to distribute the rows across all AMPs in the system evenly. For instance, on a 100-AMP system with 100,000 rows, the objective would be to allocate roughly 1,000 rows per AMP. I agree with the design guideline for many tables in a Teradata system. Nevertheless, a specific …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam