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

Method 1, avoiding any helper tables or functions which are only available in new releases of Teradata: Method 2, with helper tables and XMLAGG: Both methods deliver the string “1,2,3,4,5,6,7,8,9,10”. Which one do you think is consuming less Disk IOs and CPU seconds?

Read More

With historically managed tables, it is often necessary to find out whether two time periods overlap. Usually, several comparisons are required. The Teradata OVERLAPS command makes it easier. Here is an example: SELECT ‘Overlapping’ WHERE (DATE ‘2019-01-01’, DATE ‘2019-06-30’) OVERLAPS(DATE ‘2019-05-31’, DATE ‘2019-12-31’); -> Result: ‘Overlapping Caution is advised when the end of an interval

Read More

Have you ever noticed that the Teradata LIKE operator behaves differently depending on whether you apply it to a data type CHAR or VARCHAR column? Let us take the table below as an example: CREATE TABLE TheLikeTest( TheVarchar VARCHAR(20), TheChar CHAR(20)); We’re inserting exactly one row for our demonstration: INSERT INTO TheLikeTest VALUES (‘Anytext’,’Anytext’); First,

Read More

Teradata SQL and Advanced GROUPING Functions We will explain the advanced GROUPING functions using the following example. The basis for this is a table that contains the number of flights per aircraft and date: CREATE MULTISET TABLE Flights ( PLANE AS BIGINT NOT NULL, FLIGHTDATE AS DATE NOT NULL, NR_FLIGHTS INTEGER NOT NULL ) PRIMARY

Read More

Summary Today I will show you how to identify the source problem to tune the query’s performance utilizing the unnecessary amount of IO, what UII indicator is, and how the WHERE clause placed on a VIEW is evaluated. This case shows that the WHERE clause placed on VIEW for 2 Partitioned tables resulted in excessive

Read More

Usually, queries that are using the LIKE operator are causing a full table scan. If the LIKE operator matches from the left, the Teradata Optimizer can quickly search in the statistic histograms to get demographic data metrics such as the number of rows, etc. Unfortunately, the Teradata Optimizer has no way to use the statistics

Read More

Teradata Recursive Queries SQL offers two methods to create a Teradata recursive query. We can either create a query using the WITH RECURSIVE clause or create a view using the CREATE RECURSIVE VIEW statement. Each recursive query consists of 2 or 3 parts, depending on which of the above approaches is used: The seed statement

Read More

Teradata UNION ALL – The New Features of Release 16 Teradata 16 introduces several new features for queries containing UNION ALL. The usage of these new features can improve query performance, but will not always be applied. The optimizer will decide to execute the query without using these features if cost estimations for the traditional

Read More