SQL & CDS Table Partitioning the graphical way

[ghoshsayantan3]

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:

1. Hash


2. Range


3. 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.

In case number of partition is decided on number of host

In case we want to specify the partition number

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.

For HASH Partition a combination of columns can be taken

 

For Range Partition we can select the column for which the range is to operate

 

Note: for range partition there can be ranges given and also single value is supported. Ranges are like  start value(>=) and End Value (<).

We can check if our queries are hitting the correct partition. Like the table that we now partitioned on basis of Range partition w.r.to RYEAR. We can see for the proper where clause its going to the correct partition id in explain plan.

From this screen shot we can see the total partitions the table is distributed along side the partition id

The select is hitting the correct partition

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 .