The partitioning feature of the SAP HANA database is used to solve the problems of handling of very large data sets. Using the partitioning feature of the SAP HANA database, tables can be partitioned horizontally into disjunctive sub-tables or “partitions” as they are also known. Partitioning supports the creation of very large tables by decomposing them into smaller and more manageable pieces.
Typical uses cases for partitioning:
- Load balancing: Using partitioning, individual partitions may be distributed over the landscape. This way, a query on a table is not processed by a single server but by all servers that host partitions that are relevant for processing.
- Parallelization: Operations are parallelized by using several execution threads per table.
- Partition pruning: Queries are analyzed to see if they match the given partition specification of a table. If a match is found, it is possible to determine the actual partitions that hold the data in question. Using this method the overall load on the system can be reduced and the response time is typically better.
- Explicit partition handling: Applications may actively control partitions, for example by adding partitions that should hold the data for an upcoming month.
While using partitioning in your SAP HANA landscape, please consider the following factors:
1. A non-partitioned table cannot store more than 2 billion rows. By using partitioning, this limit may overcome by distributing the rows to several partitions. Please note, each partition must not contain more than 2 billion rows.
2. The delta merge performance of the database is dependent on the size of the main index. If data is only being modified on some partitions (see Time-Based Partitioning below), there will be less partitions that need to be delta-merged and therefore the performance will be better.
Note
- Partitioning is typically used in distributed landscapes. But it may also be beneficial for singlehost systems.
- Partitioning is available for column store tables only.
Read Here for More SAP HANA Tutorials.