Partition Limit issue

[Former Member]

I have a 7 Node Hana Instance ; I have to implement a Multi-Level Hash Range Partitioning on a Date Column .

I want to partition in a way to take advantage of partition pruning and load distribution across all nodes .

My Partition statement looks like

ALETR TABLE A PARTITIO0N BY HASH (COL1) PARTITION 7 ,

RANGE (COL2)

(PARTITION '2015-01-01' <= VALUES < '2015-01-06',

PARTITION '2015-01-06' <- VALUES < '2015-01-11',

.....

150 PARTITIONS )

This is failing for me with a message "Number of Parts must not exceed 1000" . I know HANA has a limit of max 1000 Partitions .

But here in this case , its not the number of Ranges which are getting counted (150 in this case ) ; but the number of partitions is being calculated as

7 * 150 = 1050

How do we work around the 1000 partition limit . In my case this translates to just 150 Range Partitions ; which falls short of the requirement .

[justin_molenaur2 · Accepted solution]

Kris, that's the expected behavior actually. With multi level partitioning you are creating partitions within a partition (also called a sub partition), so you would still be at the mercy of the 1000 partition limit per table.

Let's analyze

CREATE COLUMN TABLE "MOLJUS01"."TEST_PARTITION"

("COL1" NVARCHAR(10), "COL2" NVARCHAR(10), "COL3" NVARCHAR(10));

single part created

ALTER TABLE "MOLJUS01"."TEST_PARTITION" PARTITION BY HASH ("COL1") PARTITIONS 5;

5 parts created

--reset table partitioning

ALTER TABLE "MOLJUS01"."TEST_PARTITION" MERGE PARTITIONS;

ALTER TABLE "MOLJUS01"."TEST_PARTITION" PARTITION BY HASH ("COL1") PARTITIONS 5, RANGE("COL2")

(PARTITION '20150101' <= VALUES < '20150106',

PARTITION '20150106' <= VALUES < '20150111',

PARTITION '20150111' <= VALUES < '20150116',

PARTITION '20150116' <= VALUES < '20150121',

PARTITION '20150121' <= VALUES < '20150126',

PARTITION OTHERS);

30 parts created

From this query you can see it more clearly. PART_ID is the unique counter of partitions, while PARTITION indicates the main partition and SUBPARTITION indicates the sub levels. This is similar but not the same look as seen in the run time view of the table.

SELECT * FROM "SYS"."M_CS_PARTITIONS"

WHERE "TABLE_NAME" = 'TEST_PARTITION'

There are 30 partitions total for this table.

So in a nutshell, the system behavior looks correct.

Happy HANA,

Justin

[Former Member · Accepted solution]

Thank You Justin . The example makes it very clear . I hope a future release increases the 1000 Partition Limit .