Teradata 16 – UNION ALL Starting with Teradata 16, the optimizer has an additional option to handle sets of rows which are combined with “UNION ALL” in views and derived tables. Up to Version 15.10, the sets of rows which are combined with “UNION ALL” are always moved into one common spool before any other

Read More

Tuning on Teradata is all about minimizing costs and maximizing parallelism. The crucial costs (and the ones we can influence) in SQL tuning are I/Os and CPU seconds. The best way to understand their meaning is to examine at first the basics of a single computer. Single computers are built from storage devices, RAM-based memory,

Read More

TO_NUMBER TRUNC CEILING FLOOR LEAST GREATEST ROUND

Read More

In this article, we will learn why it makes sense for performance reasons to select only those columns that are actually needed. Our example query performs a join over the two tables listed below: CREATE SET TABLE TheDatabase.TheTable1 ( REF_PK BIGINT NOT NULL, COL1 CHAR(20) NOT NULL, COL2 INTEGER, … COL99 CHAR(100) ) PRIMARY INDEX (

Read More

Teradata Tuning with Query Rewriting In this article, I will show you an SQL tuning I did in one of my recent projects.  It demonstrates impressively how query rewriting can be used to meet the dramatic reduction in resource usage. The following SQL statement was identified as being problematic because it had very long execution times: SELECT t01.PK

Read More

There are several performance benefits if you use MERGE INTO requests instead of UPDATE statements: No spool required Fewer CPU seconds consumed Less Disk IOs needed IO reduction arises from the fact that each data block is only touched once and that no spooling is required during the merge operation. The advantage of the MERGE INTO statement over the traditional

Read More

Some General Teradata Tuning Ideas 1. One change at a Time Teradata is a very complex system.  You will often be tempted to change several parameters at the same time to speed up the tuning process. Don’t do it. You might solve the problem but may not know which change fixed the problem. If the

Read More

Before we get into details, I would like to show you two queries (Teradata 14.10): SELECT * FROM TheTable WHERE SUBSTR(TheCol,1,1) IN (‘1′,’2’); SELECT * FROM TheTable WHERE SUBSTR(TheCol,1,1) BETWEEN ‘1’ AND ‘2’; CREATE TABLE TheTable ( PK INTEGER NOT NULL, TheCol CHAR(10) ) PRIMARY INDEX (PK); COLLECT STATISTICS COLUMN (PK) ON TheTable; COLLECT STATISTICS COLUMN

Read More

Long texts like descriptions are often stored in columns of data type VARCHAR(); if the column is defined as a character set LATIN, we can store up to 64000 characters. If specified as character set UNICODE, we can store up to 32000 characters. The more characters are stored in a VARCHAR column, the fewer rows

Read More

Teradata saves the date internally as INTEGER. Dates after 01.01.1900 can be calculated with the following formula: ((year- 1900) * 10000) + (month * 100) + day Calculation using the above method is efficient and requires only a few resources. However, I would avoid it because it is difficult to read. Teradata stores date internally

Read More