DWHPro Insights

SQL Tuning

Teradata Set Operators: Understanding UNION vs. UNION ALL for Peak Performance Optimization Most read

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

In this article, we will delve into the world of Teradata set operators, exploring their functionality, applications for attaining peak performance, and the optimization techniques employed by Teradata. 🎓 Prefer video? Lecture 17 of my Udemy course Teradata Performance Tuning in Practice, “GROUP BY vs DISTINCT, UNION vs UNION ALL”, covers this topic. See the …

Read more

Optimizing Teradata Performance through Statistics and Primary Index Selection Widely read

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

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 …

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

Teradata Antiselect: Selecting All Columns Except the Ones You Exclude Popular

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

The comparison section supplies both three-column target definitions; provide the OldMapping and NewMapping source tables. Run the separate demonstrations with unused table names. Two directional MINUS checks compare distinct rows; they do not prove that duplicate multiplicities match. Teradata has incorporated several analytical functions from Teradata Aster in its recent releases. Although many of these …

Read more

The Teradata Recursive Query for Performance Tuning

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

Problem-solving without a Teradata Recursive Query To depict a corporate hierarchy in our instance, we can employ a non-recursive approach, illustrated by the query presented below: The above query has several harmful properties: Problem-solving with a Teradata Recursive Query The above recursive query has several benefits: The path is bounded by VARCHAR(1024), and the query …

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

Enhancing Your Teradata Vantage Design with Teradata Historisation: A Comprehensive Method Popular

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

Situation and Setup of a Historisation in Teradata Vantage This article presents a conceptual classification of history transformations. The original SQL templates are incomplete and are not a validated migration solution. In particular, grouping solely by repeated attributes can merge separate A-B-A intervals and gaps; a correct implementation must identify contiguous islands for each business …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam