Loads and Unloads in SAP HANA

SAP HANA database keep all relevant data in memory and the Standard row tables are loaded into memory when the HANA database is started and remain will be there as long as HANA database is running.

Column tables will be load in memory on demand, column by column when they are first accessed. This is called lazy loading and This means that columns that are never used are not loaded and memory waste is avoided.This is the default behaviour of column tables in SAP HANA .

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 and The database may unload tables or individual columns from memory.

we can also configure columns like allow access to the main storage one page at a time instead of requiring the whole column to be in memory . This will save the memory and the query for a single value in the main storage when certain individual columns or the entire table reside on disk. for enabling this feature,we need to specify column description clauses  PAGE LOADABLE or COLUMN LOADABLE in the (column_desc) of a CREATE TABLE or ALTER TABLE statement.

Prerequisites : following privileges required for loads and unload operation.

1> System privilege TABLE ADMIN
2> SQL object privilege UPDATE for the table or the schema in which the table is located

Example : if a query or other processes in the database require more memory compare to currently available then unload will start based on a least recently used algorithm.

SQL for load table ABC into memory.

 LOAD ABC all;

SQL for Unload table ABC from memory.

 UNLOAD ABC;

SQL for checking the load status of table ABC.

select loaded from m_cs_tables where table_name = 'ABC'

Note :  If you loaded a table then the complete data of the table with including the data in its delta storage, is loaded into main memory. Depending on the size of the table and this may take time and the load status of table's  is  FULL.

If you unloaded a table then the complete data of the table with  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 and the load status of table's  is NO.

A table can have one of the following load status:

Unloaded : None of the data in the table is loaded to main memory.

Partly loaded : 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 : All data of the column store is loaded into main memory.

SQL for checking Unloads Detail:

the following query will provide the number of unloads during a particular one-hour time-slot:

select count(*) from M_CS_UNLOADS where UNLOAD_TIME between '19.08.2015 09:00:00' and '19.08.2015 10:00:00' 

FAQ: In which sequence are columns unloaded?

Usually unloads happen based on a "least recently used" (LRU) approach, so the columns having not being used for the longest time are unloaded first. we can prioritize unloads using the UNLOAD PRIORITY setting:

ALTER TABLE "" UNLOAD PRIORITY

The priority can vary between 0 and 9. Tables with a higher priority are unloaded earlier than tables with a lower priority. SAP HANA considers both the last access time and the unload priority for the proper sequence of unloads, so both factors are important. The unload priority of a table can be checked using below SQL:

SELECT UNLOAD_PRIORITY FROM TABLES WHERE TABLE_NAME = ''

FAQ : How can unloads be monitored?

we can monitor unloads in the following way:

1> Monitoring view M_CS_UNLOADS
2> SAP HANA Studio -> Administration -> Performance -> Load -> Column Unloads