Why do we do table partitioning

[former_member312625]

From the document I read, the data partitioning is done because the load is reduced on the servers as we can distribute the partitions to different servers on the landscape. Based on the query request and partitioning method used, the processor decides the target partition having the required data.

If above is the main reason why we do partitioning, I think it can also be achieved using multiple processors on Hana DB. Each column in the table can be assigned to the processor and multiple operations can be done in parallel. so the overall load on the system can be easily reduced and the performance can be improved as well.

But I am unable to understand the exact reason of partitioning. single level or multi level. Can anyone help me in knowing that?

[Former Member]

See SAP Note 2044468 -> "For what reasons is partitioning required or useful?".

[tomas-krojzl]

Hello,

what you said is correct and SAP HANA is doing that (using different core per each column) - but if you partition then you can use multiple cores per column (one core for each column partition)...

Also if you partition smart you can do something called partition pruning - for example if you partition by year and most of select statements are specifying year as condition you might avoid scanning some partitions completely...

Another reason to partition is based on updates - each partition is having its own delta and performing its own delta merge - if you are updating only one partition then you can do delta merge (being very resource consuming operation) only on subset of data...

Etc. etc.... Two weeks ago at SAP Sapphire 2015 Hasso Plattner was explaining importance of partitioning between actual and historical data - I tried to capture the core of this in this blog (to be updated today by keynote section)...

Hope this helps...

Tomas