SAP HANA Admin - Data Compression

Using SAP Column store tables, you can perform data compression up to 11 times, which results in a cost-saving solution to store more data in HANA database. Column store tables also provide faster data access, search, and complex calculations.

The ratio of uncompressed data size to compressed data size is known as Compression Factor. The compressed table size is the size occupied by the table in the main memory of SAP HANA database.

Check Compression of a Column Table

Using SAP HANA Studio, you can find out the compression status of a column store table and also the compression factor. To find compression details, you need to first load the table into memory.

To load a table into SAP HANA memory, you should have −

• System privilege − TABLE ADMIN

• Object privilege − UPDATE for the table or the schema in which the table is located

You can also load a table using SQL command. Open the SQL console and execute the following statement −

LOAD <table_name>  
UNLOAD <table_name>

Note that when you load a table, it loads the complete data and also delta storage into the main memory of SAP HANA system.

To perform data compression, run the following SQL command to check data compression properties.

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, COMPRESSION_TYPE, LOADED from  
PUBLIC.M_CS_COLUMNS where SCHEMA_NAME = '<your_schema>' and TABLE_NAME = '<your_table>' 

You can check the output in Result tab.

To check the Compression ratio for a table, you can navigate to Table Definition. Go to Runtime Information.

To see Compression Ratio, go to Columns tab. You can see the compression ratio in the Main Size Compression Ratio [%] column.

Compress a Table Manually in SAP HANA

It is also possible to compress a table in SAP HANA system manually by executing the following SQL statement.

UPDATE "table_name" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'YES') 

This results in deciding whether a compression is required or an existing compression can be optimized. In this scenario, HANA system uses most suitable compression algorithm.

When you run the above SQL command, compression status remains the same. You can also force the database to reevaluate compression using the following SQL status −

UPDATE "AA_HANA11"."SHOP_FACTS" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE')