The SAP HANA database aims to keep all relevant data in memory. 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.
Note
This is the default behavior of column tables. In the metadata of the table, it is possible to specify that individual columns or the entire table are loaded into memory when the database is started.
The database may actively unload tables or individual columns from memory, for example, if a query or other processes in the database require more memory than is currently available. It does this based on a least recently used algorithm.
You can also configure columns to allow access to the main storage one page at a time instead of requiring the whole column to be in memory. This enables you to save memory and query a single value in the main store when certain individual columns or the entire table reside on disk. 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.
Related Information
CREATE TABLE
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.2.1 Loading and Unloading Column Tables into and from Memory
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.
Prerequisites
You have the SQL object privilege UPDATE for the table.
Context
As the SAP HANA database automatically manages the loading and unloading of tables, you should normally not have to interfere with this process. However, you can manually load and unload individual tables and table columns if necessary. For example:
● To precisely measure the total or “worst case” amount of memory used by a particular table (load)
● To actively free up memory (unload)
Note
You can see detailed information about a table's current memory usage and load status by viewing its table definition (Runtime Information tab).
1. In the Systems view, navigate to the table.
2. In the context menu of the table, choose Load into Memory or Unload from Memory as required.
3. Choose OK.
Loading and unloading tables using SQL
Open the SQL console and execute the required statement:
○ LOAD <table_name>
○ UNLOAD <table_name>
Loading and unloading individual columns using SQL
Open the SQL console and execute the required statement:
○ LOAD <table_name> (<column_name>, ...)
○ UNLOAD <table_name> (<column_name>, ...)
Results
If you loaded a table, the complete data of the table, including the data in its delta storage, is loaded into main memory. Depending on the size of the table, this may take some time. The table's load status is FULL.
If you unloaded a table, the complete data of the table, including the data in its delta storage, is unloaded from main memory. Subsequent access to this table will be slower as the data has to be reloaded into memory. The table's load status is NO.
If you loaded or unloaded a column, the entire column is loaded or unloaded into or from main memory. Its load status is TRUE or FALSE. The table's load status is PARTIALLY.
You can verify the load status of a table and its columns by opening the table in the table editor and referring to the Runtime Information tab.
Related Information
Memory Sizing [page 92]
Memory sizing is the process of estimating in advance the amount of memory that will be required to run a certain workload on an SAP HANA database. To understand memory sizing, several questions need to be answered.
Memory Management in the Column Store [page 107]
The column store is the part of the SAP HANA database that manages data organized in columns in memory.
Tables created as column tables are stored here.
Table Definition [page 126]
The table definition view provides you with information about the table's structure and properties (for example, schema, type, column properties, and indexes).
2.6.3 The Delta Merge Operation
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.
The following figure shows the different steps in the merge process, which objects are involved, and how they are accessed:
Figure 14: The Delta Merge Process
1. Before the merge operation, all write operations go to Delta 1 storage and all read operations read from Main 1 and Delta 1 storages.
2. While the merge operation is running, the following happens:
a. All write operations go to the second delta storage, Delta 2.
b. Read operations read from the original main storage, Main 1, and from both delta storages, Delta 1 and Delta 2.
c. Uncommitted changes in Delta1 are copied to Delta2.
d. The content of Main 1 and the committed entries in Delta 1 are merged into the new main storage, Main 2.
3. After the merge operation has completed, the following happens:
a. Main1 and Delta1 storages are deleted.
b. The compression of the new main storage (Main 2) is reevaluated and optimized. If necessary, this operation reorders rows and adjust compression parameters. If compression has changed, columns are immediately reloaded into memory.
c. The content of the complete main storage is persisted to disk.
Note
With this double buffer concept, the table only needs to be locked for a short time: at the beginning of the process when open transactions are moved to Delta2, and at the end of the process when the storages are
“switched”.
Caution
The minimum memory requirement for the delta merge operation includes the current size of main storage + future size of main storage + current size of delta storage + some additional memory. It is important to understand that even if a column store table is unloaded or partly loaded, the whole table is loaded into memory to perform the delta merge.
The delta merge operation can therefore be expensive for the following main reasons:
● The complete main storages of all columns of the table are re-written in memory. This consumes some CPU resources and at least temporarily duplicates the memory needed for the main storages (while Main 1 and Main 2 exist in parallel).
● The complete main storages are persisted to disk, even if only a relatively small number of records were changed. This creates disk I/O load.
This potentially negative impact on performance can be mitigated by the following strategies:
● Executing memory-only merges
A memory-only merge affects only the in-memory structures and does not persist any data.
● Splitting tables
The performance of the delta merge depends on the size of the main storage. This size can be reduced by splitting the table into multiple partitions, each with its own main and delta storages. The delta merge operation is performed at partition level and only for partitions that actually require it. This means that less data needs to merged and persisted. Note that there are disadvantages to partitioning tables that should also be considered.
The progress of delta merge operations currently running in the Administration editor on the Performance Job Progress tab.
Related Information
Table Partitioning in the SAP HANA Database [page 133]
The partitioning feature of the SAP HANA database makes it possible to split column-store tables horizontally into disjunctive sub-tables or partitions. In this way, very large tables can be broken down into smaller, more
manageable parts. Partitioning is typically used in distributed systems, but it may also be beneficial for single-host systems.
2.6.3.1 Merge Motivations
The request to merge the delta storage of a table into its main storage can be triggered in several ways. These are called merge motivations.