April 18


The Costs Of Decomposable Columns

By Roland Wenzlofsky

April 18, 2017

data type, physical datamodel

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.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!