Partitions for tables in SAP HANA

[Former Member]

Hi HANA experts,

I have the following questions wrt partitioning in SAP HANA.  It would be great if somebody can share the sample SQL code also

1.  What are the types of partitioning supported by SAP HANA ?

2.  I range partitioned a table and loaded the data - but i am unable to query for a partition or find out what are the number of records in each partition ?

THanks,

RENOO

[Former Member]

Thanks murali

[hai_murali_here]

Hi,

There is a very good document available

SAP HANA database – Partitioning and Distribution of Large Tables.pdf as part of SAP HANA Appliance Software SPS 03

You can refer this

Rgds,

Murali

[Former Member]

Hi Renoo,

1.Single-Level Partitioning.
Hash, range and round-robin are offered as single-level partition.

Hash Syntax.
CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, b) PARTITIONS n

Round Robin Syntax. (No primary Key for this table)
CREATE COLUMN TABLE mytab (a INT, b INT, c INT) PARTITION BY ROUNDROBIN PARTITIONS n

Range Syntax.
CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b))
PARTITION BY RANGE (a)
(PARTITION 1 <= VALUES < 5,
PARTITION 5 <= VALUES < 20,
PARTITION VALUE = 44,
PARTITION OTHERS)

2.Multi-Level Partitioning.
You can combine the single level partitions with one another to get multi level partitioning.

[former_member184768]

Hi Renoo,

Please refer to the following blog. It provides very valuable information on Partitioning and performance improvement.

http://scn.sap.com/community/in-memory-business-data-management/blog/2012/03/20/inside-sap-hana--opt...

Regards,

Ravi

[patrickbachmann]

Ravi I'm very interested in reading this but the link is broken.  Can you tell me where to find it please?  Thanks!!

[henrique_pinto]

Hi Patrick,

this is a rather old thread.

I'd refer to the SPS5 Administration Guide: http://help.sap.com/hana/hana_admin_en.pdf

Check the "Reference10.8 Table Partitioning in the SAP HANA Database" section.

Also, if you have attended TechEd (or know someone who has), check the presentation for "ALM360 - Operations in SAP HANA - Advanced". It has a section dedicated to table partitions & table distribution in SAP HANA.

[patrickbachmann]

Excellent!  Just what I was looking for.  Thanks for your help.