The compression factor refers to the ratio of the uncompressed data size to the compressed data size in SAP HANA.
The uncompressed data volume is a database-independent value that is defined as follows: the nominal record size multiplied by the number of records in the table. The nominal record size is the sum of the sizes of the data types of all columns.
The compressed data volume in SAP HANA is the total size that the table occupies in the main memory of SAP HANA.
Example
You can retrieve this information for a fully-loaded column table from the monitoring view M_CS_TABLES by executing the statement: select SCHEMA_NAME, TABLE_NAME, MEMORY_SIZE_IN_TOTAL from PUBLIC.M_CS_TABLES where SCHEMA_NAME='<schema>' and TABLE_NAME='<table>'
The compression factor achieved by the database depends on your SAP HANA implementation and the data involved.
Related Information
SAP Note 1514966 SAP Note 1637145
2.6.4.1 Checking Compression of Column Tables
For column-store tables, you can check the type of compression applied to table columns, as well as the compression ratio.
Prerequisites
To check the compression status of a table accurately, ensure that it is first fully loaded into main memory.
Context
What How
Checking the type of compression applied to table columns
1. In the Administration editor, open the SQL console.
2. Execute the following SQL statement:
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>'
What How
The columns of the selected table are listed with the type of compression applied.
The following values are possible:
● DEFAULT
● SPARSE
● PREFIXED
● CLUSTERED
● INDIRECT
● RLE
Note
Even if the column is not loaded into memory, the compression type is indicated as DEFAULT. This is because there will always be some level of dictionary compression. However, unless the column is loaded, the database cannot determine the type of compression actually applied. The LOADED column indicates whether or not the column is loaded into memory.
Checking the compression ratio of table columns, that is, the ratio of the column's uncompressed data size to its
compressed data size in memory
1. In the Administration editor, open the table definition in the table editor.
2. Choose the Runtime Information tab.
3. In the Details for Table area, choose the Columns tab.
The compression ratio is listed in the Main Size Compression Ratio [%] column.
Related Information
Loading and Unloading Column Tables into and from Memory [page 109]
Under normal circumstances, the SAP HANA database manages the loading and unloading of tables into and from memory independently, the aim being to keep all relevant data in memory. However, you can manually load and unload individual tables and table columns if necessary.
2.6.4.2 Compressing Column Tables Manually
The SAP HANA database decides which columns in a column table to compress and which compression algorithm to apply for each column. It does this as part of the delta merge operation. It is normally not necessary or
recommended that you interfere with this process. However, you can trigger compression manually.
Prerequisites
To compress a table manually, you must have the UPDATE privilege for the table.
Context
We do not recommend that you interfere with the way in which the SAP HANA database applies compression.
However, if a table is not compressed and you think it should be, you can request the database to reevaluate the situation.
Before you do this, consider the reasons why the table may not be compressed, for example:
● The table is very small.
● The table's delta storage has never been merged with its main storage.
● The table was created and filled using an old version of the SAP HANA database that did not compress data automatically. No further data loads, and consequently no delta merge operations, have taken place.
Procedure
1. In the Administration editor, open the SQL console.
2. Request the database to reevaluate compression by executing the SQL statement UPDATE '<your_table>' WITH PARAMETERS ('OPTIMIZE_COMPRESSION'='YES').
The database checks all of the table's columns and determines whether or not they need to be compressed, or whether or not existing compression can be optimized. If this is the case, it compresses the data using the most appropriate compression algorithm. However, note the following:
○ The database will only reevaluate compression if the contents of the table have changed significantly since the last time compression was evaluated.
○ Even if the database does reevaluate the situation, it may determine that compression is not necessary or cannot be optimized and so changes nothing.
3. Check the compression status of the table.
4. Optional: If compression has not changed, force the database to reevaluate compression by executing the following SQL statement UPDATE '<your_table>' WITH PARAMETERS
('OPTIMIZE_COMPRESSION'='FORCED').
The database checks all of the table's columns and determines whether or not they need to be compressed, or whether or not existing compression can be optimized. If this is the case, it compresses the data using the most appropriate compression algorithm. Note that the database may still determine that compression is not necessary or cannot be optimized and so changes nothing.
5. Check the compression status of the table.
Related Information
The Delta Merge Operation [page 111]
Write operations are only performed on the delta storage. In order to transform the data into a format that is optimized in terms of memory consumption and read performance, it must be transferred to the main storage.
This is accomplished by the delta merge operation.
2.6.5 Opening Tables
Some monitoring and problem analysis may require you to examine individual tables, for example, the many system views provided by the SAP HANA database. You can open tables and views in different ways. Several viewing options are available depending on what you want to do.
Procedure
Open the table or view in one of the following ways:
Option Description
Navigate 1. In the Systems view, navigate to the table or view you want to open.
2. From the context menu, choose which view you want to open:
○ Table definition
○ Table content
○ Data preview
Note
By default, double-clicking the table in the Systems view opens its definition. You can configure this setting in the preferences of the SAP HANA studio.
Search
1. From the Systems view toolbar, choose the (Find Table) button.
2. Enter a search string (at least two characters).
3. Select the required table.
Matching tables are displayed immediately.
4. Choose whether you want to display the table content and/or the table definition.
Results
The table is displayed in the selected view.
2.6.5.1 Table Definition
The table definition view provides you with information about the table's structure and properties (for example, schema, type, column properties, and indexes).
Detailed information relating to the table's memory usage and size is available on the Runtime Information sub-tab. This information can be useful in the following cases, for example:
● You want to examine the memory usage of an individual table in detail as part of performance analysis or optimization.
● You want to review the partitioning of a table.
Due to the different memory management concepts for row store and column store tables, the information displayed varies according to table type.