- SAP HANA Admin Tutorial
- SAP HANA Admin - Home
- SAP HANA Admin - Overview
- Architecture Overview
- Multitenant Database
- Multiple Host Systems
- SAP HANA Admin - System Mngmt
- Multitenant DB Container Mgmt
- Starting a HANA System
- Stopping a HANA System
- License Keys
- Monitoring the HANA System
- SAP HANA Admin - Table Mngmt
- SAP HANA Admin - Table Partition
- SAP HANA Admin - Table Replication
- Data Compression
- Solman Integration
- SAP HANA Admin - Lifecycle Mngmt
- Securing HANA System
- User Provisioning
- Authentication Methods
- Auditing Activities
- Backing Up HANA System
- Recovery HANA System
- HANA XS Application Service
- Data Provisioning
- Smart Data Access
- New Remote System Connection
- Integration with Hadoop
- Key Commands
- Job Responsibilities
- SAP HANA Admin Useful Resources
- SAP HANA Admin - Quick Guide
- SAP HANA Admin - Useful Resources
- SAP HANA Admin - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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')