DWHPro Insights

Decomposable Columns: Definition, Impact on Performance, and Workarounds

By Roland Wenzlofsky ·

Decomposable Columns: Definition, Impact on Performance, and Workarounds

What are Decomposable Columns?

A decomposable string is not automatically a normalization violation. Decide from its domain, dependencies and query requirements whether separate attributes are appropriate.

Using a decomposable column for data retrieval negatively impacts performance.

Substring and LIKE access depend on predicate shape and available indexes/partitions. A prefix LIKE does not universally force a base-table scan. For the lookup rewrite, ProductCode must be unique and the lookup must remain synchronized, or join multiplicities/results can change.

If a decomposable column generates NUSI columns, the options for NUSI bit mapping are expanded.

Here is an example:

— Select all Private Gold Products being sold by our company

SELECT * FROM Transaction WHERE ProductCode LIKE 'PRIVGOLD%';

— Select all Private Products being sold by our company

SELECT * FROM Transaction WHERE ProductCode LIKE 'PRIV%';

In either scenario, accessing the data requires scanning the entire table.

Decomposing the ProductCode column allows the Optimizer to utilize existing NUSIs.

CREATE INDEX (ProductSegment) ON Transaction;
CREATE INDEX COLUMN(ProductLevel) ON Transaction;
SELECT * FROM Transaction WHERE ProductSegment = 'PRIV' AND ProductLevel = 'Gold';

Sometimes altering the physical data model is not feasible, and alternatives are needed to work around the drawbacks of decomposable columns. A straightforward yet effective approach is as follows:


SELECT p.ProductDescription,t.ProductCode
FROM
Transaction t
INNER JOIN
Product p
ON
t.ProductCode = p.ProductCode
WHERE
p.ProductCode LIKE '%Gold%'
;

Avoiding decomposable columns should always be the first option.

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

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

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

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam