What are the different types of partitioning supported by SAP HANA ?
There are two types of partitioning supported by SAP HANA
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 partitioning is used to equally distribute rows to partitions for load balancing and overcoming the 2 billion rows limitation.
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 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 SyntaxCREATE 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,
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
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: