What are Decomposable Columns?
Decomposable columns can be split. Decomposable columns mean that the physical data model is not normalized.
If we don’t use a decomposable column for data retrieval, there is no negative impact on performance.
Still, if the column is used for data access, it should be decomposed into several columns, which can be utilized independently for indexed access. You will get increased performance because there will be fewer full table scans.
Additionally, if the columns created from a decomposable column are NUSI columns, NUSI bit mapping possibilities are increased:
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 both cases, typically, a full table scan is required to access the data.
By decomposing the column ProductCode, we give the Optimizer a chance to use existing NUSIs:
CREATE INDEX COLUMN(ProductSegment) ON Transaction;
CREATE INDEX COLUMN(ProductLevel) ON Transaction;
SELECT * FROM Transaction WHERE ProductSegment = ‘PRIV’ AND ProductLevel = ‘Gold’;
Often we can’t change the physical data model, and we have to find workarounds to avoid the negative performance impact of decomposable columns. Here is a simple but efficient solution:
SELECT p.ProductDescription,t.ProductCode
FROM
Transaction t
INNER JOIN
Product p
ON
t.ProductCode = p.ProductCode
WHERE
p.ProductCode LIKE ‘%Gold%’
;
Table Product is just a lookup containing all distinct ProductCodes. The Optimizer will probably do a full table scan on the Product Table with a subsequent merge join to the Transaction table. As the Product table is small, the cost of a full table scan is negligible.
Still, the first choice is to avoid the implementation of decomposable columns whenever possible.