Different types of Partitioning supported by SAP HANA

What are the different types of partitioning supported by SAP HANA ? 

There are two types of partitioning supported by SAP HANA

1.Single-Level Partitioning.

Rows can be distributed to partitions using different types of partitioning known as partition specifications.
Hash, range and round-robin are offered as single-level partition.

Hash

Hash partitioning is used to equally distribute rows to partitions for load balancing and overcoming the 2 billion rows limitation.
 

Hash Syntax

CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, b) PARTITIONS 4

creates 4 partitions columns a and b

determines the target partition based on the actual values in columns a and b

at least one column has to be specified

all columns have to be part of the primary key

Number of partitions is determined by the engine at runtime according to its configuration. It is recommended to use this function in scripts etc.
 

Round-robin

Range

• Round-robin is similar to hash partitioning as it is used for an equal distribution of rows to parts. When using this method it is not required to specify partitioning columns.

  Round Robin Syntax.

  CREATE COLUMN TABLE mytab (a INT, b INT, c INT)
  PARTITION BY ROUNDROBIN PARTITIONS 4
  
  The table must not have primary keys
  
  CREATE COLUMN TABLE mytab (a INT, b INT, c INT)
  PARTITION BY ROUNDROBIN PARTITIONS GET_NUM_SERVERS()
  
  The number of partitions is determined by the engine at runtime according to its configuration. It is recommended to use this function in scripts or clients that may operate in various landscapes.


• Range partitioning can be used to create dedicated partitions for certain values or certain value ranges. For example a range partitioning scheme can be chosen to create one partition per month of the year. The range partitioning is not well-suited for load distribution. The range partition specification usually takes ranges of values to determine one partition, e.g. 1 to 10. Range partitioning is similar to hash partitioning in that the partitioning column has to be part of the primary key. Range partitioning also has restrictions on the data types that can be used. Only strings, integers and dates are allowed.

  Range Syntax

  CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b))
  PARTITION BY RANGE (a)
  (PARTITION 1 <= VALUES < 5,
  PARTITION 5 <= VALUES < 20,
  PARTITION VALUE = 44,
  PARTITION OTHERS)
  
  Create partitions for ranges using <= VALUES < semantics
  
  Create partitions for single values using VALUE = semantics
  
  Create a rest partition for all values that do not match the other ranges using PARTITION OTHERS

2.Multi-Level Partitioning.

We can combine the single level partitions with one another to get multi-level partitioning. Multi-level partitioning is the technical implementation of time-based partitioning, this is where a date column is leveraged: