Sap Hana - Table Column - Column Store

About

Table column.

Structure

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

_

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

ALTER TABLE table_name {ENABLE | DISABLE}  DELTA LOG;

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 <note important>data that is in the delta storage can only be fully loaded or unloaded.</note>
  • 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

Compression

Configuration

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 <column_desc> of a CREATE TABLE or ALTER TABLE statement.

Size

  • M_TABLES: TABLE_SIZE: Allocated memory size
  • M_TABLE_PERSISTENCE_STATISTICS: Size on disk

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


Powered by ComboStrap