cancel
Showing results for 
Search instead for 
Did you mean: 

Table Partition

Former Member
0 Kudos

Hello,

We are trying to do a partition for the first time.

We used the following:

ALTER TABLE "BNHP_UTILS"."TC_RDL_BAL_CP_3"

PARTITION BY

     RANGE ("/BA1/C55POSTD")

     (

          PARTITION '20130901' <= VALUES < '20131001',

          PARTITION '20131001' <= VALUES <'20131101',

          PARTITION OTHERS

     ),

     HASH ("/BA1/C55AOTYPE")  PARTITION 5

We are getting a syntax error.

What are we doing wrong?

Thanks,

Amir

Accepted Solutions (1)

Accepted Solutions (1)

AtulKumarJain
Active Contributor
0 Kudos

Hi Amir,

Please check

SAP BW on HANA Cookbook

It will help you if still you facing issue please update screen shot.

BR

Atul

patrickbachmann
Active Contributor
0 Kudos

I'm not sure you can do a range followed by hash sub-partition. (I will double check documentation)  You can do a hash-range however.  The syntax should look like this;

ALTER TABLE "MYSCHEMA"."MSEG" PARTITION BY HASH (MBLNR) PARTITIONS 10, RANGE (MJAHR)

(PARTITION 1 <= VALUES < 2002,

PARTITION 2002 <= VALUES < 2003,

PARTITION 2003 <= VALUES < 2004,

PARTITION 2004 <= VALUES < 2005,

PARTITION 2005 <= VALUES < 2006,

PARTITION 2006 <= VALUES < 2007,

PARTITION 2007 <= VALUES < 2008,

PARTITION 2008 <= VALUES < 2009,

PARTITION 2009 <= VALUES < 2010,

PARTITION 2010 <= VALUES < 2011,

PARTITION 2011 <= VALUES < 2012,

PARTITION 2012 <= VALUES < 2013,

PARTITION OTHERS)

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Try this;

ALTER TABLE "BNHP_UTILS"."TC_RDL_BAL_CP_3"

PARTITION BY HASH ("/BA1/C55AOTYPE")  PARTITIONS 5

     RANGE ("/BA1/C55POSTD")

     (

          PARTITION 20130901 <= VALUES < 20131001,

          PARTITION 20131001 <= VALUES <20131101,

          PARTITION OTHERS

     ),

NOTE: I'm assuming "/BA1/C55A0TYPE" is a column in your table?  Also I don't think you need the quotes around the column name, however I'm not sure how the SLASH will be interpreted.

lbreddemann
Active Contributor
0 Kudos

Hi Patrick

the double quotes are required to allow special characters like / in identifiers.

Particularly like your approach to consult the documentation!

- Lars

patrickbachmann
Active Contributor
0 Kudos

Ahh, thanks for clarifying Lars.  Also I double-checked my documentation and I only see HASH-RANGE, HASH-HASH and ROUND-ROBIN-RANGE as multi level partitioning options.

-Patrick

former_member182302
Active Contributor
0 Kudos

The reason i think why the RANGE-HASH partitioning is not given may be because

HASH -- will properly do load balancing and distributes the load across all the nodes

RANGE -- will further help me to partition on Time characteristics

If i do RANGE before,we need to have better idea on the Time lines of the data to properly segregate and hence may not have given a option of doing RANGE before HASH.

Also at the first level i.e HASH , the primary key columns must be part of the Partitioning, however if the table doesn't have primary keys it will allow non key column.

And on second level, this primary key restriction is removed.

Regards,

Krishna Tangudu

Answers (0)