DWHPro Insights

SQL Tuning

You Migrated From Teradata to Spark and Threw Away the One Thing That Made It Fast Popular

Illustration of three stacked database units of differing heights inside a circle

If you have spent any amount of time working with Teradata, you know that the Primary Index is one of the most important design decisions you make. It determines how data is distributed across AMPs and whether your joins are fast or slow. Choosing the wrong Primary Index is one of the most common causes …

Read more

The Pitfalls of Teradata SELECT * Queries Popular

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

Introduction In a row-oriented database engine like Teradata, data is organized and stored in units called data blocks. Each data block features a fixed header and accommodates multiple rows. Every row consists of a record header followed by its corresponding columns. 🎓 Prefer video? Lecture 16 of my Udemy course Teradata Performance Tuning in Practice, …

Read more

Improving SQL Performance with Simple Query Rewrites: Dealing with Duplicates and Business Calendars

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

The Teradata flavor of SQL is still, in principle, a declarative language. Hence, there can be multiple ways to describe an SQL query and achieve the same result. While the answer is the same, Teradata may use a completely different execution plan based on how the query is expressed. One approach is investing in heavy …

Read more

Date ranges and business calendars beyond recursion and Teradata SYS_CALENDAR Popular

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

In a previous article, Roland explained how to use recursion to build a data range without touching a physical table. This time, we explore an alternative approach that avoids recursion using a one-row metadata source whose AMP access depends on the plan. We can use this approach to build both a wide date range and …

Read more

Teradata strtok_split_to_table, CSVLD, Unpack: Column Splitting in Teradata Widely read

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

Splitting column content can be a challenging task. Teradata provides multiple methods to achieve this, each with unique pros and cons. In this article, we will examine these techniques in detail. To begin, we will generate a table containing sufficient random data to assess every option’s efficiency and resource utilization. The Teradata “CSVLD” Function This …

Read more

A Teradata HASHROW Table Difference Screening Test Popular

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

Have you ever experienced extended waiting times for a table comparison to yield results? Have you ever been compelled to halt and defer quality checks on sizable tables owing to excessive resource utilization during a previous attempt? Hash comparison can be a compact preliminary screen, but it can miss real differences. It must not be …

Read more

Teradata – Merging two change history tables

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

Have you encountered a poorly designed physical data model where object columns are distributed randomly across tables, and you wish to unify them in their rightful place? Merging non-historical tables is simple, but the process becomes more complex when historization is applied to at least one of the tables. The fragments below illustrate interval overlap; …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam