The Costs of Decomposable Columns


What are Decomposable Columns?

Decomposable columns can be split into smaller units. 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, the possibilities for NUSI bitmapping 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
Transaction t
Product p
t.ProductCode = p.ProductCode
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 full table scan is negligible.

Still, the first choice is to avoid the implementation of decomposable columns whenever possible.

If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.


Please enter your comment!
Please enter your name here