fbpx

By way of introduction, I would like to mention that Teradata block-level compression is often performed by default nowadays. Therefore, this blog post’s considerations are only useful on Teradata systems, where this is not the case.

Since Teradata Block Level Compression has been around, I often see that multi-value compression is not used.

First, I would like to point out that Teradata MVC and BLC typically have different use cases.

Teradata MVC is a tool to improve your Teradata SQL queries’ performance and save mass storage space.

BLC is there to save space, but it is not intended to be a performance tuning tool because unpacking the data requires CPU seconds. Therefore the cold data is usually compressed with BLC (i.e., the data that is not used frequently).

Here is an example that shows how Teradata Multivalue Compression can be used together with BLC.

Comparison Of Combinations Of BLC and MVC

We will look at all four possible combinations of compression and then see what these results mean.

For Multivalue Compression, we have chosen the Compress list so that all values are “compressed away”:

These are our four test tables:

SET QUERY_BAND = 'BLOCKCOMPRESSION=NO;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_MVC
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100) COMPRESS ('PLATIN','GOLDEN','SILVER','BRONZE‘)
)
PRIMARY INDEX ( CustomerId );
SET QUERY_BAND = 'BLOCKCOMPRESSION=NO;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_NONE
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100)
)
PRIMARY INDEX ( CustomerId );
SET QUERY_BAND = 'BLOCKCOMPRESSION=YES;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_BLC_MVC
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100) COMPRESS ('PLATIN','GOLDEN','SILVER','BRONZE‘) 
)
PRIMARY INDEX ( CustomerId );
SET QUERY_BAND = 'BLOCKCOMPRESSION=YES;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_BLC
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100)
)
PRIMARY INDEX ( CustomerId );

In each of these tables, we add 10 million rows. As mentioned above, it is essential that the column CategoryCd only contains the values ‘PLATIN’, ‘GOLDEN’, ‘SILVER’, ‘BRONZE’:

Comparing the Disk IOs

You can see the Disk IOs required by a full table scan in the next figure:

Teradata BLC
IOs required for each combination of compression

What can be derived from this for our Compression Strategy from a Disk IO point of view (Caution: we only look at the IOs)?

  • Not using compression is always bad.
  • Block-level compression should be combined with MVC

Please note that in our test scenario, the selected MVC was optimized for the existing values. This should be the goal when using MVC.

Comparing the CPU Seconds

Now let’s look at how the different compressing methods influence the number of CPU seconds needed:

teradata MVC
Adding BLC to the optimized MVC has no benefit on IOs but increases the used CPU seconds.
  • Not using compression is always wrong.
  • Block-level compression increases always the CPU seconds needed and should only be used for cold data

Comparing the Compression Rate

Teradata Multivalue And Blocklevel Compression Together? 1
  • Not using compression is always bad.
  • BLC compression, together with MVC compression, is almost the same as BLC alone.
  • The optimized MVC us not as efficient as BLC or a combination of BLC and MVC

That’s what I deduce:

Since only tables rarely queried are block-level compressed, a combination with MVC is not necessarily advantageous as querying them requires additional CPU.

On the other hand, if BLC tables are often queried, one should rather ask if it is not better to compress them only with MVC.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

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

You might also like

>