SAP HANA Forum
For what reasons is partitioning required or useful?
Kindly help me out.
In general partitioning is most useful for large tables. In the following situations you can take advantage of partitioning:
Tables with many records: Each table and partition must not contain more than 2 billion records in the column store. Tables with a risk to reach the 2 billion record limit mid-term have to be partitioned.
Complex queries: In scale-out scenarios you can distribute the load of complex requests across different nodes if you locate the table partitions on different hosts. Be aware that this kind of approach can have a negative impact on "simple" queries that also require to access multiple hosts.
Complex queries can also take advantage of parallelism. By processing data in all partitions concurrently the runtime can be reduced significantly.
Tables with hot and cold data: If tables contain frequently accessed (hot) areas and other areas with hardly any access (cold) these ranges can be separated by partitioning. As a consequence only the hot data is regularly accessed and there is no overhead for loading the cold areas into memory and processing them.
The possibility to satisfy a query with accessing only a subset of partitions is also called partition pruning.
Delta merge optimization: If data is mainly modified in some partitions there is less data that needs to be delta-merged.
Explicit partition handling: In some cases it can be useful that the application controls the creation and existence of partitions based on specific criteria.
Sign up for STechies