DWHPro Insights

Improve Performance with Teradata Partition Elimination: The Stored Procedure Approach

By Roland Wenzlofsky ·

Improve Performance with Teradata Partition Elimination: The Stored Procedure Approach

Teradata Partition Elimination – the Stored Procedure Approach

Occasionally, we may want to select rows in a table’s character column that begin with a specific prefix, yet we lack prior knowledge of all the values contained in that column. This could include newly introduced codes in the future.

If we are lucky, there might be a convention for these codes, such as: “All code values have to start with an ‘A’ character”.

With SQL, this problem can be resolved using the following approach:

SELECT * FROM The_Table WHERE CODE_COLUMN LIKE 'A%';

or

SELECT * FROM The_Table WHERE SUBSTR(CODE_COLUMN,1,1) = 'A';

While the SQL statement above resolves our issue, accessing a table with numerous rows could potentially result in performance problems.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

LIKE or SUBSTR may limit partition elimination, but not universally. Inspect the actual partition expression, predicate and EXPLAIN plan.

CREATE TABLE The_Table
(PK INTEGER NOT NULL, Code_Column CHAR(100))
PRIMARY INDEX (PK)
PARTITION BY CASE_N(Code_Column < 'B', Code_Column < 'C', NO CASE, UNKNOWN);

The former stored procedure is withdrawn because it did not compile and its generated code list was unsafe and bounded to 3,200 characters. Enumerating every code also has a cost; it is not a demonstrated optimization.

  1. The adapted CASE_N fixture accepted A1 and B1 and the prefix predicate returned A1. This validates the small functional example, not a speedup or partition-elimination benchmark.
  2. A production dynamic implementation would need declared variables, complete statement terminators, safe literal escaping, length checks, and cleanup/reentrancy handling. Prefer a direct predicate unless a measured, semantically equivalent rewrite is justified.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam