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.
