What is Teradata Query Rewriting? Teradata query rewriting is part of the Teradata optimization process. The optimizer replaces your query with a better version, which is assumed to be less resource-intense and, hopefully, faster. Of course, both queries have to deliver the same result set.  To achieve better performance, Teradata utilizes several optimization strategies. We will

Read More

It is time to publish another case study about Teradata SQL tuning,  which impressively demonstrates the terrific impact query rewriting can have on performance. Our object of study is the query below, which initially ran about 40 minutes. As usual, I added all missing statistics and ensured that no statistic is stale: Something a SQL tuning

Read More

The Idea Behind This Trick for UNION ALL What if you want to perform a UNION ALL on two different columns of the same table? Normally you would do this: SELECT CloseDate FROM Customer UNION ALL SELECT OpenDate FROM Customer ; However, this method has a disadvantage: The table Customer must be queried with two

Read More

What is the Teradata Sample Function?The SAMPLE function returns a number of rows that were randomly selected. These are the features:Ask for a sample with an absolute number of rowsAsk for a sample which contains a certain percentage of the table rows Ask for more than one sample at the same timeInclude the column SAMPLEID to

Read More

Teradata SUBSTR Or SUBSTRING? The Teradata SUBSTRING or SUBSTR function is one of the Teradata string functions and used to cut a substring from a string based on its position. We can use ANSI syntax (Teradata SUBSTRING) or Teradata syntax (Teradata SUBSTR). The ANSI syntax is recommended to remain compatible with other database systems. ANSI

Read More

UNION and UNION ALL The Teradata UNION statement combines the result set of at least two queries; in addition, UNION removes row duplicates. Each of the select statements must pick the same number of columns, the columns must be selected in the same order, and the datatypes of the column of all select statements must

Read More

The Teradata TO_CHAR Function The Teradata TO_CHAR function can be used to: Convert numeric expressions to character strings Convert dates, times, interval data types, or timestamps to character strings Here is an example of a numeric expression argument. It can be any of these data types: BYTEINT, SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, FLOAT/REAL/DOUBLE PRECISION, or NUMBER.

Read More

Teradata Ordered Analytic Functions use two different methods to redistribute the data, depending on the data demographics. Teradata Ordered Analytical Functions Teradata Ordered Analytical Functions are useful if the result of a row is dependent on the values of previous or subsequent rows. Here is an example: The running sum over the last 3 days

Read More

1. Avoid multiple Joins to the same Table Each join means either a full table scan or index access. In all your Teradata SQL queries, avoid multiple joins to the same table if possible. Often multiple joins can be bypassed by subqueries, volatile tables, or ordered analytic functions. Here is an example of using a

Read More