2

Give Me 5 Minutes, I’ll Give You The Truth About Teradata Sessions And Performance!

Teradata Sessions explained in a few Sentences

Example 1: Multiple Sessions which do not improve Performance

As shown in the graph above, all 4 transactions (T1, T2, T3, T4) are active on all AMPs simultaneously. Even if only one row is required, all of these transactions involve all AMPs to look this row up. None of the transactions makes access via a single rowhash. Therefore, there is no performance gain.

Example 2: Multiple Sessions which do improve Performance

In example 2, each transaction is only active on exactly one AMP. Therefore the 4 AMPs can work on different tasks at the same time.

In summary, the use of multiple sessions is only useful if:

• Each transaction uses less than all AMPs
• Ideally, there are enough sessions available to keep all AMPs equally busy with transactions.

0

Teradata Fast Path INSERT/SELECT

Fast Path INSERT/SELECT is designed to load a single table as efficiently as possible.

Requirements for Teradata Fast Path INSERT /SELECT

• All tables involved have the same primary index.
• The target table is empty

How does the Fast Path INSERT/SELECT work?

The idea behind this is to minimize access to the mass memory (Hard Disk, Solid State Disk).

This is done by creating whole data blocks on each AMP and then writing them to the mass storage with a single IO.

Under the conditions mentioned above (target table is empty, all tables involved have the same primary index), the following is ensured at the same time:

The rows of the target and source tables are already sorted on the same AMP and also in ascending order according to the same rowhash within the data blocks.

Therefore, each AMP can prepare the data blocks to be written locally and write them to the mass memory with a single IO.

How does BTEQ handle the Fast Path INSERT/SELECT?

BTEQ uses a special syntax that must be adhered to when several tables are to be loaded into the empty target table at the same time using Fast Path:

The semicolon must be at the beginning (from the 2nd table) of the line (not at the end of the lines!):

INSERT INTO Customer SELECT * FROM Customer1
;INSERT INTO Customer SELECT * FROM Customer2
;INSERT INTO Customer SELECT * FROM Customer3
;INSERT INTO Customer SELECT * FROM Customer4

Advantages of the Fast Path INSERT/SELECT

• Only one row is stored in the transient journal for a possible ROLLBACK
• ROLLBACK is done immediately without delay
• Rows are packed into data blocks and the whole blocks are written with one IO
• Since the tables all have the same primary index, the processing is AMP-local and BYNET is not loaded.

9

Teradata SQL Tuning – 6 Golden Rules you must never forget!

2

Teradata Volatile Tables – Expectation vs. Reality

What is a Teradata Volatile Table?

Volatile tables are very similar to derived tables. In contrast to these, their lifetime is not limited to the duration of a query, but they exist until the end of the session in which they were created.

Volatile tables are created with the following syntax:

CREATE VOLATILE TABLE Customer
(
   CustomerId INTEGER NOT NULL,
   LastName VARCHAR(255)
) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;

A comparison between Volatile Tables, Global Temporary Tables, and Derived Tables can be found here:

Comparison of temporary table types

Teradata Volatile Table Features

Teradata Volatile Table Restrictions

• No referential integrity can be defined
• No CHECK constraints are allowed
• DEFAULT values are not allowed
• No TITLE can be defined for columns
• No Join Index or Hash Index is allowed
• Indexes can't be named
• Permanent Journals can't be used
• Column Partitioning is not allowed
• No privileges can be set with a GRANT or REVOKE. That wouldn't make any sense either

0

Master the Differences between Teradata Primary and Secondary Index

The Teradata USI compendium

The Teradata Primary Index Choice

0

Teradata Temporary Tables

Teradata temporary tables come in different types and for different purposes. This article shows you the properties and uses of each one.

1. Derived Tables

• Are materialized by a select statement within a query
• Only exist within the time the query is executed
• Use the spool space of the executing user
• Disappear at the moment the query is finished

Derived tables must always be named with an alias, otherwise, there will be a syntax error!

Here is an example of a derived table (inside the parentheses):

SELECT * FROM (SELECT MAX(Revenue) FROM Invoices) AS InvoiceRevenue (MaxRevenue);

The table is named InvoiceRevenue and the column from the derived table is called MaxRevenue.

However, there are further possibilities to name the column of the derived table:

SELECT * FROM (SELECT MAX(Revenue) AS MaxRevenue FROM Invoices) AS InvoiceRevenue;

In the above example, we name the column already within the derived table.

Which method you use is a matter of taste, the result of the query is the same.

Derived Tables using the WITH Syntax

Here is another method how you can name a derived table and its columns in advance, and reference it later:

WITH InvoiceRevenue(MaxRevenue) AS  
(SELECT MAX(Revenue) AS MaxRevenue FROM Invoices)
SELECT * FROM  InvoiceRevenue; 

However, you must take into account that only one WITH statement is allowed per query.

The great thing about this syntax is that the table is pre-defined, and then can be easily inserted into the rest of the query.

Here is an example:

WITH InvoiceRevenue(MaxRevenue) AS  
(SELECT MAX(Revenue) AS MaxRevenue FROM Invoices)
SELECT * 
FROM 
  InvoiceRevenue t01 
INNER JOIN 
  Customer t02 
ON 
  t01.CustomerId = t02.CustomerId; 

It is also important to understand that the derived table exists only during the execution of a query, and not for the entire session.

Here is an example of two queries that are executed in one transaction. The second query will cause an error message:

BT;
WITH InvoiceRevenue(MaxRevenue) AS  
(SELECT MAX(Revenue) AS MaxRevenue FROM Invoices)
SELECT * 
FROM 
  InvoiceRevenue t01 
INNER JOIN 
  Customer t02 
ON 
  t01.CustomerId = t02.CustomerId; 

SELECT * FROM  InvoiceRevenue; --> causes an error

ET;

2. Volatile Tables

• Are created and afterward materialized by adding data with and INSERT/SELECT statement or
• Are created and materialized at the same time with a CREATE VOLATILE TABLE TheTable AS (<QUERY>) WITH DATA PRIMARY INDEX (PI) ON COMMIT PRESERVE ROWS statement
• Use the spool space of the executing user
• Disappear at the moment the session is logged of
• The table definition is stored in each AMP's cache

CREATE VOLATILE TABLE Revenue, NO LOG
(
   CustomerId BIGINT NOT NULL,
   RevenueAmount DECIMAL(18,3)
) ON COMMIT PRESERVE ROWS;

NO LOG means that no Transient Journal is used.

This will usually not be necessary, because it is a temporary table and you will not need this functionality of a ROLLBACK.

NO LOG is the default setting and should usually not be changed.

ON COMMIT PRESERVE ROWS is not a default setting, and must therefore usually always be specified. Otherwise, the contents of the table are deleted immediately at the end of the transaction.

The default setting is ON COMMIT DELETE ROWS!

After the volatile table is created, it can be loaded like any other table:

INSERT INTO Revenue
SELECT CustomerId, SUM(Revenue) FROM Transaction;

Why do I need ON COMMIT DELETE ROWS?

Here is a practical example of how to use ON COMMIT DELETE ROWS.

As already mentioned, ON DELETE COMMIT ROWS deletes the data as soon as the corresponding transaction is completed.

We can take advantage of this to, for example, delete a volatile table immediately after it is no longer needed.

BT;
CREATE VOLATILE TABLE Revenue, NO LOG
(
   CustomerId BIGINT NOT NULL,
   RevenueAmount DECIMAL(18,3)
) ON COMMIT DELETE ROWS;

INSERT INTO Revenue SELECT CustomerId,AVG(Revenue) FROM Transaction;

SELECT * FROM Revenue; 
--> The volatile table still exists as the transaction is not ended!

ET; --> Now the volatile table is empty

--> Further Queries...

Additional possibilities to materialize volatile tables

–> Create the table with content CREATE VOLATILE TABLE Revenue AS (SELECT SUM(Amount) AS AMT FROM Revenue_All) WITH DATA ON COMMIT PRESERVE ROWS;	–> Create the table empty CREATE VOLATILE TABLE Revenue AS (SELECT SUM(Amount) ASAMT FROM Revenue_All ) WITH NO DATA ON COMMIT PRESERVE ROWS;
–> This volatile table contains only column AMT and ceratin table attributes from the table Transaction CREATE VOLATILE TABLE Revenue AS (SELECT SUM(Amount) AS AMT FROM Revenue_All ) WITH DATA ON COMMIT PRESERVE ROWS;	–> This volatile table is a 1:1 copy of the table transaction CREATE VOLATILE TABLE Revenue AS Revenue_All ) WITH DATA ON COMMIT PRESERVE ROWS;

How can I copy Statistics to the Volatile Table?

CREATE VOLATILE TABLE Revenue AS Revenue_All WITH DATA AND STATISTICS ON COMMIT PRESERVE ROWS;

How do I find all available volatile tables in my session?

All you have to do is execute this command:

HELP VOLATILE TABLE;

3. Global Temporary Tables

• Are created with a CREATE TABLE DDL which is stored permanently in DBC tables
• Are materialized with an INSERT/SELECT statement
• Use the temp space of the executing user (not the spool space)
• Disappear at the moment the session is logged of (but the table definition stays stored in the DBC tables!)
• Each user can materialize his own copy a Global Temporary Table

Global Temporary Tables are useful if the definition is to be used by several users.

Like a Volatile Table, the Global Temporary Table only exists in the session where it was materialized. However, unlike the Volatile Table, any number of users can materialize this table (as the definition is stored permanently in DBC tables).