Implementation of NSE

In HANA 2.0 SP 04, NSE is automatically enabled, requiring no additional steps beyond executing the correct SQL/DDL statements.

In the context of NSE, "Page loadable" signifies data residing in NSE's Warm store, while "Column loadable" refers to data in the primary memory, known as the Hot store.

You have the flexibility to specify "Page loadable" at various levels, including the table, index, partition, and column levels. Furthermore, data can be converted between "page loadable" and "column loadable" formats as needed.

Load units dictate how data is loaded:

• PAGE (Warm store): Data is stored on the NSE storage disk.

• COLUMN (Hot store): Data resides in the primary memory.

• DEFAULT (Depends): If you don't specify a load unit for a specific granularity, the LOAD_UNIT column in system views displays the load unit as DEFAULT.

You can use standard HANA system views to check the load units set:

1. TABLES

2. M_CS_TABLES

3. TABLE_PARTITIONS

4. TABLE_COLUMNS

5. INDEXES - Indicates the index load unit for supported index types.

For example, you can retrieve the load unit for a table named 'VBRK' in a specific schema using the following SQL query:

SELECT LOAD_UNIT FROM M_CS_TABLES 
WHERE TABLE_NAME = 'VBRK' 
AND SCHEMA_NAME = <SchemaName>;

DDL for NSE Implementation:

-- Sets the load unit to page loadable for the entire table
CREATE COLUMN TABLE tab_acloudguruji_emp (emp_id INT, name VARCHAR (100)) PAGE LOADABLE;

-- Sets the load unit to page loadable for the column name
CREATE COLUMN TABLE tab1_acloudguruji_emp (emp_id INT, name VARCHAR (100) PAGE LOADABLE);

-- Sets the load unit for table and Column.
CREATE COLUMN TABLE tab2_acloudguruji_emp 
(emp_id INT COLUMN LOADABLE, name VARCHAR(100)) PAGE LOADABLE;

-- Single-level heterogenous partitioned table. Note that the double parenthesis surrounding the partition declaration is syntactically required by CREATE TABLE.
CREATE COLUMN TABLE tab3_acloudguruji_emp (emp_id INT, name VARCHAR (100) ) 
PARTITION BY RANGE (emp_id) 
((PARTITION 19999 <= VALUES < 600000 PAGE LOADABLE,
 PARTITION OTHERS COLUMN LOADABLE));
 
ALTER TABLE tab_acloudguruji_emp PAGE LOADABLE CASCADE;