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
All the site contents are Copyright © www.stechies.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies. The site www.stechies.com is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.stechies.com or the content authors.