DWHPro Insights

full table scan

Understanding Teradata’s Primary AMP Index for Improved Performance Popular

Illustration of a stopwatch dial on an orange paint-splash background

Experienced Teradata users are familiar with the concept of a primary index. However, a new term has surfaced with the introduction of Teradata Release 15: Primary AMP Index. This blog post will demonstrate a Primary AMP Index’s benefits and optimal usage for improving load and query performance. First, let us examine Teradata’s approaches over the …

Read more

Optimize Teradata UNION ALL with a Single Table Scan Trick

Illustration of a stopwatch dial on an orange paint-splash background

The Idea Behind This Trick for Teradata UNION ALL What if you need to apply a UNION ALL operation to distinct columns within a single table? Typically, the process would involve: The rewrite may permit one base-table scan, but scan count and I/O benefit depend on the optimizer and data. Verify the plan rather than …

Read more

Teradata Access Paths: 8 Essential Ones to Know

Illustration of a stopwatch dial on an orange paint-splash background

Hash indexes remain supported: creation executed in the isolated fixture. Single-table join indexes offer overlapping and broader capabilities, but have not made hash indexes nonexistent. 🎓 Prefer video? Lecture 6 of my Udemy course Teradata Performance Tuning in Practice, “The access paths the optimizer can choose”, covers this topic. See the course on Udemy → …

Read more

Maximizing Performance with Multiple Teradata Sessions Popular

Illustration of a stopwatch dial on an orange paint-splash background

Teradata Sessions explained in a few Sentences Example 1: Multiple Sessions which do not improve performance Multiple sessions can improve throughput for compatible all-AMP scans or independent tables as well as rowhash access. Benefit depends on workload concurrency and resource contention, not a universal fewer-than-all-AMP rule. Example 2: Multiple Sessions which do improve performance In …

Read more

Optimizing Teradata Requests with Group-AMP Operations for Improved Performance

Illustration of a stopwatch dial on an orange paint-splash background

All-AMP describes participating AMPs, not a mandatory full-table or subtable scan. NUSI access can be narrowed by other predicates and maps; inspect the actual access steps. Group-AMP operations optimize requests by minimizing the number of active AMPs. These operations fall between single-AMP and all-AMP operations. The Optimizer employs group-AMP for data retrieval and joins. A …

Read more

Teradata ASCII: Built-in Function and Legacy CHAR2HEXINT Example

Illustration of a stopwatch dial on an orange paint-splash background

Introduction

The tested Teradata release supports SELECT ASCII('B');, which returned 66. The CHAR2HEXINT expression below is a legacy encoding-dependent example, not a necessary replacement for the built-in function.

The solution is as follows:

We use the Teradata SQL function CHAR2HEXINT to obtain a hexadecimal value for a character. Our objective is to convert this hexadecimal value to its decimal counterpart, which signifies the character’s ASCII code.

The following legacy expression demonstrates the calculation for this specific character and encoding.

SELECT
CASE SUBSTRING(CHAR2HEXINT('B') FROM 3 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END * 16 +
CASE SUBSTRING(CHAR2HEXINT('B') FROM 4 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END

Using a CASE statement, the query extracts two hexadecimal digits with the SUBSTRING function and converts them to decimal values. It then calculates the character’s ASCII code by multiplying the first digit’s decimal value by 16 and adding the second digit’s decimal value.

Conclusion

Prefer the documented ASCII function when its semantics match the task. The legacy workaround returned 66 for B with CHAR2HEXINT yielding 0042 in the tested context. Its fixed substring positions are not a general solution for arbitrary character sets or Unicode characters.

Is Teradata Compression Worth Your Time and Effort?

Illustration of a stopwatch dial on an orange paint-splash background

Teradata DBMS offers various compression options to save disk space and improve performance. But, the cost of implementing the compression takes man-time. This article discusses the cost vs. benefit of compression and suggests carefully considering the compression setting when designing new or modifying existing tables.

Teradata SQL Tuning: How to Optimize Queries for Stable Runtimes Popular

Illustration of a glossy orange database cylinder with data fragments across its surface

Learn how to optimize Teradata SQL Tuning with comprehensive analysis of available tools in this article. Discover the optimal approach for query optimization for both tactical and strategic workloads, as well as methods for eliminating full table scans, reducing skew, and examining the execution plan. Plus, find out how to address common issues like concurrency and misestimation.

Maximizing Teradata Table Performance with BLC, Row-Level Partitioning, and Data Block Size Tuning Popular

Illustration of a stopwatch dial on an orange paint-splash background

BLC, Row Level Partitioning, and Datablock Size Tuning The sizes and diagrams below are historical observations: the original data, allocation and effective BLC policy are unavailable, so they have not been reproduced. In this article, we will learn how block-level compression, row-level partitioning, and selection of the appropriate data block size can be used to …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam