About
Table column.
Articles Related
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 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
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
of a CREATE TABLE or ALTER TABLE statement.- 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'
CS for column store !
- M_CS_COLUMNS - Runtime information from all columns of column tables, including internal ones
- SYS.M_TABLES WHERE IS_COLUMN_TABLE = 'TRUE' (TABLE_SIZE, …)