DWHPro Insights

Negative Impact of Applying Functions to Join Columns in Teradata Joins: Performance Implications and Solutions

By Roland Wenzlofsky ·

Negative Impact of Applying Functions to Join Columns in Teradata Joins: Performance Implications and Solutions

Functions on Join Columns and Their Impact on Teradata Performance

In many Teradata systems, developers apply functions directly in join conditions to work around data-model inconsistencies.
While this approach might seem harmless, it can dramatically affect optimizer decisions and query performance — and often reveals deeper data-model issues.


Example of a Problematic Join

Applying functions to join keys can change available access paths and cardinality estimates; inspect the complete query rather than assuming that every statistic is disabled.

SELECT A.column1, A.column2, B.column1, B.column2
FROM tableA A
JOIN tableB B
ON SUBSTRING(A.join_column, 1, 3) = B.join_column;

This expression can limit direct access on the transformed column, but it does not force scans of both inputs. In the audited fixture, an additional UPI predicate enabled single-AMP nested access to both primary indexes.


What Is the Effect of Functions on Join Performance?

Functions and patterns affect estimates differently. Base-column statistics may remain useful for other predicates, and suitable expression statistics or access structures can help in supported cases.

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

Compare estimated and actual join cardinalities before assuming a transformation hides the distribution completely.


What If I Cannot Avoid Functions?

Ideally, fix the data model so that columns are comparable without transformation.
If that’s not immediately possible, consider these mitigations:

  1. Derived Column:
    Add a stored column containing the transformed value (LEFT(join_column,3)), and join on that instead.
  2. Expression join index: consider materializing the expression and collecting relevant statistics. Creation and statistics collection succeeded in the audit, but optimizer use of that index was not established.
  3. Volatile Table Normalization:
    As a last resort, preprocess data into a volatile table with normalized join keys before joining.

These are workarounds — not substitutes for a clean model.


Other Disadvantages of Functions on Join Columns

Additional predicates, indexes and optimizer transformations can preserve efficient access despite an expression join. Measure CPU, I/O and spool for the actual plan.


Functions With Minimal Impact

Some functions allow partial optimization.
For example:

SELECT * FROM Customer WHERE LastName LIKE 'Wen%';

If the pattern starts with a literal and ends with %, Teradata can still use histogram boundaries for filtering.
In contrast, using:

WHERE SUBSTR(LastName,1,3) = 'Wen'

can produce a different plan, but neither the loss of every histogram nor a full scan is inevitable.


Conclusion

The underlying problem is almost always a flawed data model.
Using functions on join columns hides normalization issues, confuses the optimizer, and inflates resource consumption.
Investing in proper model design yields far greater long-term performance than any SQL-level workaround.


The Snowflake Perspective

Snowflake behaves differently but faces a similar principle:
Expression-based joins also limit optimization.

  • In Snowflake, the effect of expression predicates on micro-partition pruning depends on the expression, data and available optimization features. Inspect Query Profile and assess supported search optimization for the workload.
  • Neither architecture guarantees a smaller expression-join penalty. Measure scanned data, compute consumption and elapsed time on each workload.
  • The solution is identical in spirit: materialize normalized keys or preprocess data before joining.

In both systems: fixing the data model removes the need for function-based joins.


Migration Guidelines: Teradata → Snowflake

ConceptTeradataSnowflakeMigration Tip
Function on Join ColumnDisables statistics & row-hash accessBreaks micro-partition pruningPre-compute normalized join key before load
Optimizer BehaviorFalls back to full-table scanScans all partitions touched by expressionMaterialize join key or use staging view
WorkaroundsDerived column, Join Index, volatile tableDerived column, materialized viewApply transformation during ELT
Root CauseDenormalized / inconsistent modelSame issue; compute hides itCorrect the model in source layer

Use compatible join keys where their business semantics permit it, and compare actual plans and resource usage before adopting a normalization workaround.

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.

2 thoughts on “Negative Impact of Applying Functions to Join Columns in Teradata Joins: Performance Implications and Solutions”

  1. Hi Roland,

    Functions on join columns should be avoided only in the join statement? Or throughout the entire SQL statement?
    I’m asking from a statistics usage perspective.
    If my join is clean but I’m doing a function on join column while projecting it in the SELECT part, does the optimizer still not use column’s statistics?

    Regards,
    Virendra

    Reply
  2. Only in the join part. The select list doesn’t matter.

    Still, if you can avoid functions on your WHERE conditions it’s an advantage.

    Since Teradata 14.10 you can apply statistics on functions, still, they are most likely only useful for exactly the one SQL you designed them for. On the other hand, simple column statistics are normally useful in many queries.

    If no statistics can be used, heuristics are applied, which can be completely wrong as they assume a fixed amount of rows being returned.

    Reply

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