Sap Hana - Table Column - Column Store

Sap Hana Architecture


Table column.


Data Structure Access Pattern Compression
Main Storage Optimized for read access Advanced
Delta Storage Optimized for write access Basic

Hana Column Store Write Read Operations

Read operations are performed on both structures, while write operations only affect the delta.

Main Storage

The main storage contains the main part of the data (data compression).

Delta storage

Write operations on compressed data in the main storage would however be costly. Therefore, write operations do not directly modify compressed data in the main storage. Instead, all changes are written to a separate data structure called the delta storage.

The delta storage structure itself exists only in memory and is not persisted to disk.

data that is in the delta storage can only be fully loaded or unloaded.

Delta merge

The purpose of the delta merge operation is to move changes collected in the delta storage to the read-optimized main storage.

Delta Log

The column store creates its logical redo log entries for all operations executed on the delta_storage. This log is called the delta log. The delta merge operation truncates the delta log (ie redo operations)

You can also adjust the logging settings for single column tables


After enabling, you have to perform a savepoint to be certain that all data is persisted. Also you have to perform a data backup, otherwise it will not be possible to recover this data.

If logging is disabled, log entries will not be persisted for this table. Changes to this table will only be written to the data store when a savepoint is carried out. This can cause loss of committed transaction should the indexserver terminate. In the case of a termination, you have to truncate this table and insert all data again.

For a column store table, the logging setting can be seen in the table: public.m_cs_tables.IS_LOG_DELTA

Load Status

Standard row tables are loaded into memory when the database is started and remain there as long as it is running. They are not unloaded. Column tables, on the other hand, are loaded on demand, column by column when they are first accessed. This is sometimes called lazy loading. This means that columns that are never used are not loaded and memory waste is avoided.

A table can have one of the following load statuses:

  • Unloaded, that is, none of the data in the table is loaded to main memory
  • Partly loaded, that is, some of the data in the table is loaded to main memory, for example, a few columns recently used in a query data that is in the delta storage can only be fully loaded or unloaded.
  • Fully loaded, that is, all the data in the table is loaded into main memory

When the SAP HANA database runs out of allocatable memory, it will try to free up some memory by unloading unimportant data. Even column store tables are unloaded, column by column, based on a least-recently-used order, up to a preset lower limit. See Memory pool limit


See SAP Hana - Compression (In Column Store)


Whole Column or page loadable

Load column in memory into the main_storage:

  • one page at a time
  • or the whole column

To enable this feature, specify column description clauses PAGE LOADABLE or COLUMN LOADABLE in the

of a CREATE TABLE or ALTER TABLE statement.
  • M_TABLES: TABLE_SIZE: Allocated memory size

SAP HANA loads column tables into memory column-by-column only upon use. You must ensure that the table is fully loaded load TABLE_NAME all

  • amount of memory used for column tables
select round(sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) as "Column Tables MB Used" from M_CS_TABLES
  • per schema
select SCHEMA_NAME as "Schema", round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024) as "MB Used" from M_CS_TABLES GROUP by SCHEMA_NAME order by "MB Used" desc
  • per column
select COLUMN_NAME as "Column", LOADED, round(UNCOMPRESSED_SIZE/1024/1024) as "Uncompressed MB", round(MEMORY_SIZE_IN_MAIN/1024/1024) as "Main MB", round(MEMORY_SIZE_IN_DELTA/1024/1024) as "Delta MB", round(MEMORY_SIZE_IN_TOTAL/1024/1024) as "Total Used MB", round(COMPRESSION_RATIO_IN_PERCENTAGE/100, 2) as "Compr. Ratio" from M_CS_COLUMNS where TABLE_NAME = 'LineItem'
Data Dictionary

CS for column store !

Documentation / Reference

Discover More
Sap Hana Architecture
SAP Hana - Compression (In Column Store)

Compression algorithms (dictionary, RLE, sparse, and so on) applied to a table store column. The compression are only on the main storage Default is the Dictionary compression applied to all...
Sap Hana Used Memory
SAP Hana - Memory Pool (global_allocation_limit)

memory pool in Hana. SAP HANA preallocates and manages its own memory pool, used for storing: in-memory data and system table , code and thread stacks (~=6 Gb), temporary (results|computation),...
Sap Hana Used Memory
SAP Hana - Used memory

The total amount of memory used by SAP HANA is referred to as used memory. At any given point, only parts of the memory pool are really in use. SAP refers to the total amount of memory actually in use...
Sap Hana Architecture
Sap Hana - (Redo) Log

Redo log entries (in-memory) are written to the log volumes consistently for all changes to persistent data. While logging is disabled no log entries will be persisted, only the data area will...
Sap Hana Delta Merge Operation
Sap Hana - Delta Merge Operation

The Delta Merge Operation is an operation on a table column store data structure. The purpose of the saphelp_hanaplatform/helpdata/en/bd/9ac728bb57101482b2ebfe243dcd7a/content.htm?frameset=/en/bd/9ac728bb57101482b2ebfe243dcd7a/frameset.htmdelta...
Sap Hana Architecture
Sap Hana - Row tables

Standard row tables are loaded into memory when the database is started and remain there as long as it is running. Partitioning is available only for column tables. Global For a schema RS...
Sap Hana Architecture
Sap Hana - Table

UNLOAD saphelp_hanaplatform/helpdata/en/21/0197377519101481cfb213f0b84848/content.htmTables saphelp_hanaplatform/helpdata/en/20/c7689a75191014ad52c1beb40ce2d2/content.htmM_TABLES - Information on...

Share this page:
Follow us:
Task Runner