Table partition in HANA

TABLE partition in HANA: – for handling large data in a physical table.

HANA support maximum number of rows up to 2 billion so if we have more data then we may use table partition to overcome this by distributing the rows of a column table on multiple partitions and hence the size limit is increase to 2 billion for each partition. We can chive Table partition concept using SQL. We need to select partition option wisely by understanding table contents in depth.

After partition, we never know which value will go under which partition all partition size will be same. Always make the partition on fact table and avoid partition on dimension tables to improve the performance of a join between fact table and dimension table.

Based on collective experience, a generic guideline to follow is to have about 200 million to 800 million rows per partition

Advantage:- Partitioning a table has several advantages. When the data of a table is distributed to multiple nodes over the landscape, a query against the table will be processed on all these nodes, enabling parallel processing for queries on all nodes.

There are different types partitioning available in SAP HANA system −

• Hash Partitioning
• Round Robin Partitioning
• Range Partitioning

• Hash Partitioning

Hash & Round-robin partitioning do not require an in-depth knowledge of the actual content of the table and primary key need to define.

Note:- If we create partition on more than one column then need to use both column in where clause during data read form this table otherwise full table scan will be applied and pruning will be lost. The same will goes for missing one column or having a range condition.

We need to specify at least one column and here partition will be column a & b into 4 partitions.

CREATE COLUMN TABLE TABLENAME (x INT, y INT, z INT, PRIMARY KEY(x,y))

PARTITION BY HASH(x,y) PARTITIONS 4

ALTER TABLE MY_TABLE PARTITION BY HASH (a) PARTITIONS 10;

• Round Robin Partitioning

Round-robin partitioning, new rows are assigned to partitions on a rotation basis to achieve an equal distribution.

CREATE COLUMN TABLE TABLENAME (x INT, y INT, z INT)

PARTITION BY ROUNDROBIN PARTITIONS 4

No Primary key is required to define. 

• Range Partitioning

Dedicated partition on certain values Range partitioning is not well suited for load distribution. Range partitioning – require an in-depth knowledge of the actual content of the table For example, a range partitioning scheme can be chosen to create one partition for each calendar month. SAP HANA will only scan the partition where the data of that day resides. This technique is called partition pruning. This data base optimization feature scan only partitions that contain matching values instead of scanning all partitions, which will improve the performance of result sets.

CREATE COLUMN TABLE TABLE_NAME (x INT, y INT, z INT, PRIMARY KEY (x,y)) PARTITION BY RANGE (x) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20, PARTITION VALUE = 50, PARTITION  OTHERS)

Changing a Table to Add/Drop Rest Partition

ALTER TABLE MY_TABLE ADD PARTITION OTHERS

ALTER TABLE MY_TABLE DROP PARTITION OTHERS

ALTER TABLE MY_TABLE ADD PARTITION (a) 5 <= VALUES < 10

ALTER TABLE MY_TABLE DROP PARTITION (b) 500 <= VALUES < 1000