I've a requirement of finding the columns which are best suited candidates for compression. I've around 80 tables in my database. Can somebody suggest if we have any SQL's which give us calculations related to compression so that columns for compressin can be decided.
Thanks in advance
Please refer below link for more information on compression and identifying and evaluating candidate tables for Compression:
Kishore, I believe Shashi is looking for MVC of the columns in a table, but not BLC of the table.
@Shashi, Please let me know if otherwise.
The best columns that would be candidates for MVC are those which are having a high frequency of the same value. MVC is primarily used in a good chunk for space saving and there are additional savings like reduction in I/O.There are many things to be considered while implementing MVC of which the following will help:
1. How many of the values in a column are repeating.
2. How frequently that value exists in the column.
3. How frequently that column is updated. the more the column value is update, it is not ideal for MVC.
4. if we have a column which is char(100) and the length maximum value in that column is not more than 20, can we consider changing the column to char(20) or even Varchar(20)~ This will also help in space redution of the table.
5. The number of values compressed in a column cannot be more than 255(try to use the values for compression in multiples).
6. Also the tableheader could not be crossing a certain limit depending on the version you are in (TD12 supports 128KB of tableheader).
There is no official tool from Teradata to identify the columns that qualify for MVC. An extensive analysis on the tables and the data is the way out. There are other tools in the market which can be looked for
Bteq script can be used to calculate best options for MVC by considering datatypes, frequency of repeating values, distinct value count. This MVC list can then be reviewed for actual perm space saving and used.
Regarding 4 point from Krishaneesh, varchar(100) will be able to support what char(100) was doing and also save spaces on mostly 20 character data.
Try this not sure whether it is useful. Please modify condition as you wish