The 15-Year Detour: How the Data Industry Spent Billions Reinventing SQL

Illustration of three stacked database storage units of differing heights against abstract chart shapes

Somewhere around 2020, the data world quietly arrived at a conclusion that Teradata engineers could have told you in 1984: SQL on a massively parallel architecture is a pretty good way to process large volumes of data. The path to get there was anything but quiet. It involved billions in capital, an entire generation of …

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

Negative Impact of Applying Functions to Join Columns in Teradata Joins: Performance Implications and Solutions Widely read

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

Functions on Join Columns and Their Impact on Teradata Performance In many Teradata systems, developers apply functions directly in join conditions to work around data-model inconsistencies.While this approach might seem harmless, it can dramatically affect optimizer decisions and query performance — and often reveals deeper data-model issues. 🎓 Prefer video? Lecture 12 of my Udemy …

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 – 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

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

Why Teradata’s LIKE operator behaves differently for CHAR and VARCHAR columns Popular

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

Have you observed the different behavior of the Teradata LIKE operator when applied to a CHAR or VARCHAR data type column? Consider the following table as an illustration: Creating a table: To illustrate, we will add a single row. We will run the SQL statement for the column with the VARCHAR data type. Next, we …

Read more

Advanced GROUPING Methods in Teradata SQL: GROUP BY GROUPING SETS, ROLLUP, and CUBE Popular

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

Teradata SQL and Advanced GROUPING Functions The advanced GROUPING functions will be demonstrated through the following example. The foundation of this demonstration is a table consisting of the flight count for each aircraft and date: CREATE MULTISET TABLE Flights ( PLANE BIGINT NOT NULL, FLIGHTDATE DATE NOT NULL, NR_FLIGHTS INTEGER NOT NULL ) PRIMARY INDEX …

Read more

Learn Teradata Indexing Techniques in this 2-Hour Video Course

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

This course focuses on Teradata indexing techniques. In this two-hour video, you will learn about Teradata’s architecture and Full Table Scans, different types of Primary Index Access such as UPI and NUPI, diverse Secondary Index Access including USI and NUSI, and Join Indexes. This historical video introduction covers indexing topics. The embedded video itself was …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam