Decomposable Columns: Definition, Impact on Performance, and Workarounds

What are Decomposable Columns?

Decomposable columns are those that can be divided. They indicate a lack of normalization in the physical data model.

Using a decomposable column for data retrieval negatively impacts performance.

If the column is being utilized for data access, it should be broken down into multiple columns. Each column can then be used independently for indexed access. This approach will lead to improved performance as there will be a reduction in full table scans.

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 COLUMN(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%'
;

The Product table serves as a lookup for all unique ProductCodes. The Optimizer will likely execute a full table scan on the Product table and then perform a merge join with the Transaction table. Since the Product table is of small size, the cost of a full table scan is insignificant.

Avoiding decomposable columns should always be the first option.