SQL & CDS Table Partitioning the graphical way
As a fellow HANA Model developer the concept of partitioning in HANA modelling really intrigued me, however in many of the blogs I found it to be very informative but I wanted to see something more like from the modelling stand point how its helps and if this can be achieved though the graphical way.
In this blog I will discuss how we can partition SQL table by Partitioning it graphically through HANA Studio and also how we can partition a CDS table from the Web IDE.
Prerequisites : The developer has concept of Table partitioning and why it is needed
If not already aware please go through the link .Still I would like to give a brief overview.
In SAP HANA a non partitioned table can hold up to a maximum of 2 billion records but after that we can keep more records in the same table by partitioning the table. This being a need for storing huge data sets is not the only reason of doing partition as we can understand a table which has around 2 Billion of record count can have indicative performance impacts on the upper model as full table scan will be costly. Thus partitioning helps in reliving load on the model performance as well.
There are basically 3 types of partitioning for Single level:
- Round Robin
There is also multi level partitioning too .
SQL table graphical partitioning:
First select the schema where the non partitioned table resides on. Then right click and select from the options show table distribution.
In the next screen select the non-partitioned table. Then right click on the table and select partition table.
In this screen you will have option to select the type of partition and the number of partitions. The number of partitions can be decided on the basis of number of hosts in the system in our case it is single host system.so its one ,we can manually set the number of parameters for HASH partition.
Note: the partition strategies will be from the drop down depending on the table structure .i.e. if the table supports that’s strategy or not. For example HASH and Range partition Key columns need to be in partitioning column but in case of Round robin the table should not have a Key column[So round robin is not supported by our table]. Below screen shots have the screen shot for how the column selection happens.
Note: for range partition there can be ranges given and also single value is supported. Ranges are like start value(>=) and End Value (<).
CDS Table partitioning:
Similar to the way we partitioned the catalog SQL table above we can partition a CDS table.Below would be the steps.
Double click on the CDS table and open the GUI,
Double click on the table name and go to the partition tab as given below.
Check the box below and as before please select the type of partition you want to do.
So in the above blog we have learnt how to Partition SQL and CDS code bases tables in a Graphical manner,also we could see how SQL queries go to access the partitions sufficing the proper conditions in the where clause .
Hope this will be helpful !! Please share your feedback and suggestions .