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.
Part of the Teradata SQL Tuning and Query Rewriting guide.
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.
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 bookCompare 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:
- Derived Column:
Add a stored column containing the transformed value (LEFT(join_column,3)), and join on that instead. - 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.
- 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
| Concept | Teradata | Snowflake | Migration Tip |
|---|---|---|---|
| Function on Join Column | Disables statistics & row-hash access | Breaks micro-partition pruning | Pre-compute normalized join key before load |
| Optimizer Behavior | Falls back to full-table scan | Scans all partitions touched by expression | Materialize join key or use staging view |
| Workarounds | Derived column, Join Index, volatile table | Derived column, materialized view | Apply transformation during ELT |
| Root Cause | Denormalized / inconsistent model | Same issue; compute hides it | Correct 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.
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
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
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.