January 3

0 comments

All Teradata Tuning Tips In One Place!

Author Image

By Roland Wenzlofsky

January 3, 2020

Share0
Tweet0
Share0
Tuning Topics
The Teradata Physical Data Model
The Teradata Statistics
Teradata SQL

This blog post is intended to be a collection of all things that need to be considered when building a Teradata Data Warehouse, the ETL process, as well as SQL queries.

This list is just a start and I hope to get valuable input from my readers to enrich to it over time. I started with just a few ideas and will add additional categories and topics in the next days and weeks.

At the end of this blog post you have the possibility to enter your own ideas. I will add them continuously to this article (I can also add your name if you want).

The Teradata Physical Data Model

  • Are all Primary Key columns defined as NOT NULL?
  • Does the Primary Index distribute the rows equally across all AMPs?
  • Are the Primary Index column values stable or do they change frequently?
  • Do columns with the same content have the same data type across all tables?
  • Are SET tables only used for exceptional cases, otherwise always MULTISET tables?
  • Are tables from which a range of values is often queried row-level partitioned?
  • Do tables that are often joined together have the same primary index?
  • Is the primary index of a table often used in a WHERE condition?
  • Was Multi-Value compression performed on all tables?
  • Are all row-partitions maintained (to cover new values)?
  • Is the correct CHARACTER SET used (LATIN vs. UNICODE)?
  • Is the DATABLOCKSIZE appropriate for the workload (Tactical vs. Strategic)?
  • Are tables created without the FALLBACK option? (Raluca Topor)
  • Was a 1-AMP design chosen for tiny tables (1-AMP Hashmap)? (Peter Nagy)
  • Was columnar considered for wide datamarts with narrow queries? (Peter Nagy)
  • Was NOT NULL unique key applied on parent tables to enable join elimination? (alternative to soft RI) (Peter Nagy)
  • Was primary AMP design considered for tables that are frequently reloaded and yet still joined? (Peter Nagy)
  • Is adequate sample percentage used during collect stats for big tables? (Peter Nagy)
  • Are statistics copied from table to table whenever its a replica instead of recollected? (Peter Nagy)
  • Is a value ordered NUSI considered for range query columns on tables where row partitioning is not an option? (Peter Nagy)

The Teradata Statistics

  • Are full statistics collected on all small tables (fewer table rows than AMPs)?
  • Are statistics collected on all non-indexed columns that are used in WHERE conditions?
  • Are full statistics collected on all skewed columns?
  • Do you use DELETE instead of DROP/CREATE to preserve statistics histories?
  • Have you collected statistics on all join columns?
  • Have you collected statistics on all Primary Indexes?
  • Have you collected statistics on all NUSI?
  • Have you collected statistics on all partition columns (individually and in combination)?
  • Are you using Create table Table1 as Table2 with data and stats in order to keep the stats? (Atharva Joshi)

Teradata SQL

teradata union
  • Do you use UNION only if you need to remove duplicates, otherwise UNION ALL?
  • Do you only select the columns you really need?
  • Do you use outer joins only when it is really necessary?
  • If possible, do you use WHERE conditions to enable partition elimination?
  • Do you use multi-statement requests where possible?
  • Do you use CLOB instead of large VARCHAR for rarely requested columns?
  • Are you careful not to use functions on join columns?
  • Are you using a Fast Path INSERT/SELECT?

Please Send Me Your Own Tuning Ideas

* Your email address will not be used for any marketing purposes and it will not be stored.

Share0
Tweet0
Share0
Author Image

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

Tuning

A Better Alternative For The Teradata NUSI

Tuning

How You Can Reduce Teradata Hash Collisions

Tuning

Let’s Talk About Teradata Roles And Their Advantages!

Tuning

Teradata Cancel Rollback As An Emergency Measure

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>