Single-Level Partitioning

When a table is partitioned, its rows are distributed to partitions according to different criteria known as partitioning specifications.

The SAP HANA database supports the following single-level partitioning specifications:

● Hash

● Range

● Round robin

For advanced use cases, these specifications can be nested using multi-level partitioning.

2.6.8.1.1 Hash Partitioning

Hash partitioning is used to distribute rows to partitions equally for load balancing and to overcome the 2 billion row limitation. The number of the assigned partition is computed by applying a hash function to the value of a specified column. Hash partitioning does not require an in-depth knowledge of the actual content of the table.

For each hash partitioning specification, columns must be specified as partitioning columns. The actual values of these columns are used when the hash value is determined. If the table has a primary key, these partitioning columns must be part of the key. The advantage of this restriction is that a uniqueness check of the key can be performed on the local server. You can use as many partitioning columns as required to achieve a good variety of values for an equal distribution.

For more information about the SQL syntax for partitioning, see SAP HANA SQL and System Views Reference.

Example

Creating a Hash-Partitioned Table Using SQL

SQL Command Result

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

● 4 partitions on columns a and b are created.

● The target partition is determined based on the actual values in columns a and b.

● At least one column has to be specified.

● If a table has a primary key, all partitioning columns must be part of that key.

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

GET_NUM_SERVERS()

The number of partitions is determined by the database at runtime according to its configuration. It is recommended to use this function in scripts, and so on.

Related Information

SAP HANA SQL and System Views Reference

2.6.8.1.2 Round-Robin Partitioning

Round-robin partitioning is used to achieve an equal distribution of rows to partitions. However, unlike hash partitioning, you do not have to specify partitioning columns. With round-robin partitioning, new rows are assigned to partitions on a rotation basis. The table must not have primary keys.

Hash partitioning is usually more beneficial than round-robin partitioning for the following reasons:

● The partitioning columns can be evaluated in a pruning step. Therefore, all partitions are considered in searches and other database operations.

● Depending on the scenario, it is possible that the data within semantically-related tables resides on the same server. Some internal operations may then operate locally instead of retrieving data from a different server.

For more information about the SQL syntax for partitioning, see SAP HANA SQL and System Views Reference.

Example

Creating a Round-Robin Partitioned Table Using SQL

SQL Command Result

CREATE COLUMN TABLE MY_TABLE (a INT, b INT, c INT) PARTITION BY ROUNDROBIN PARTITIONS 4

4 partitions are created.

SQL Command Result CREATE COLUMN TABLE MY_TABLE (a INT, b

INT, c INT) PARTITION BY ROUNDROBIN PARTITIONS GET_NUM_SERVERS()

The number of partitions is determined by the database at runtime according to its configuration. It is recommended to use this function in scripts or clients that may operate in various landscapes.

Related Information

SAP HANA SQL and System Views Reference

2.6.8.1.3 Range Partitioning

Range partitioning can be used to create dedicated partitions for certain values or certain value ranges in a table.

Usually, this requires an in-depth knowledge of the values that are used or are valid for the chosen partitioning column. For example, a range partitioning scheme can be chosen to create one partition for each calendar month.

Applications may choose to use range partitioning to manage the partitioning of a table actively, that is, partitions may be created or dropped as needed. For example, an application may create a partition for an upcoming month so that new data is inserted into that new partition.

Note

Range partitioning is not well suited for load distribution. Multi-level partitioning specifications address this issue.

The range partitioning specification usually takes ranges of values to determine one partition, for example, 1 to 10.

It is also possible to define a partition for a single value. In this way, a list partitioning known in other database systems can be emulated and combined with range partitioning.

When rows are inserted or modified, the target partition is determined by the defined ranges. If a value does not fit into one of these ranges, an error is raised. If this is not wanted, it is possible to define a rest partition where all rows that do not match any of the defined ranges are inserted. Rest partitions can be created or dropped on-the-fly as desired.

Range partitioning is similar to hash partitioning in that the partitioning column must be part of the primary key.

Range partitioning is also restricted in terms of the data types that can be used. Only strings, integers, and dates are allowed.

For more information about the SQL syntax for partitioning, see SAP HANA SQL and System Views Reference.

Example

Creating a Range-Partitioned Table Using SQL

SQL Command Result

CREATE COLUMN TABLE MY_TABLE (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)

Partitions are created as follows:

● 1 partition for values greater than or equal to 1 and less than 5

● 1 partition for values greater than or equal to 5 and less than 20

● 1 partition for values of 44

● 1 rest partition is created for all values that Creates partitions for ranges using <= VALUES <

semantics

● Partitioning column has to be part of the primary key

● Only STRING, INT and DATE are allowed as data types for the partitioning column

Related Information

SAP HANA SQL and System Views Reference