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:
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:
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:
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.