Table partition thoughts

[Former Member]

Hello, We decided to partition a table by Hash for the key (GUID) and range for the periods – one partition for each month. Now we are thinking how to make the range dynamic. Should we create the ranges for all the year in advance? Do the empty partitions will influence our performance and memory space? Is there any other way to create partitions automatic for each month – we though about creating a special script for this but what will happen if we miss a period? Thanks, Amir

[justin_molenaur2 · Accepted solution]

http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf

Take a good read through the above document at page 133, it should help you in your partitioning quest.

Keep in mind - you can always change the partitioning methods on the fly and put the partitions back together into one piece, so you are not setting anything in stone. Of course, this can take some time with large tables, but it's still possible. At current client in a stage in environment, a 1.6b record table was merged and repartitioned in about 10 hours.

Regards,

Justin

[Former Member · Accepted solution]

Hi Justin,

Thank you, but our table has only one field in the key = guid. and the wizard for creating partitions allow to use  for the first level of the partition only the table keys: for the second level we can choose whatever we like

[justin_molenaur2 · Accepted solution]

Ah, that's true - first level partitions must have at least one of the keys included. The previous recommendation on adding partitions into the future still is relevant here I would say.

I have not seen that partition wizard yet, cool stuff.

Regards,

Justin

[Former Member]

Hi Amir,

Regarding your question, you can create the partitions in advance or you can create them later dynamically. It depends on you. If you want to create a partition each month dynamically, you can use the job scheduling to realize this. You can write the logic of creating a new partition in .xsjs and write the job scheduling in a .xsjob file.

In addition, I've made a test for you according to your scenario.

First I created a partitioned table, multi-level, hash-range.

CREATE COLUMN TABLE TEST_PART (

  ID INTEGER PRIMARY KEY,

  NAME NVARCHAR(100),

  _DATE DATE

) PARTITION BY HASH (ID) PARTITIONS 2, RANGE (MONTH(_DATE)) (

PARTITION '2014-06' <= VALUES < '2014-07',

PARTITION '2014-07' <= VALUES < '2014-08',

PARTITION '2014-08' <= VALUES < '2014-09'

);

When you open the definition of the table and switch to the "Runtime Information" tab, you can find the following info.

Then I added a partition dynamically as follows.

ALTER TABLE TEST_PART ADD PARTITION '2014-09' <= VALUES < '2014-10';

You can find the latest info.

In addition, you can also drop the partition if you do not need it anymore.

ALTER TABLE TEST_PART DROP PARTITION '2014-09' <= VALUES < '2014-10';

Best regards,

Wenjun

[justin_molenaur2]

You can also unpartition the table completely at a later point in time (given that there are under 2b rows), and also completely change the partitioning criteria "on the fly" (meaning without reload/unload).

Regards,

Justin

[Former Member]

Hi Justin,

Absolutely. I just did not show them in my example.

Best regards,

Wenjun

[Former Member]

Hi Wenjun,

I have used your method for a date partition in SAP HANA, but I ran into a situation where my date column is defined as Nvarchar and I need to partition this my month.

In this case I wanted to do something like below which is not allowing me to create partitions on existing table.

Any help on this is much appreciated.

ALTER TABLE Testschema.Test_Part

PARTITION BY HASH (ID) PARTITIONS 2, RANGE(LEFT(TestString,6))

(

PARTITION Values = '201406',

PARTITION Values = '201407',

PARTITION OTHERS

);

Regards

Devi

[Former Member]

Hi Devi,

Yes, it's not allowed. If you have a look at ALTER TABLE - SAP HANA SQL and System Views Reference - SAP Library, the definition of <partition_expression> is as follows.

<partition_expression> ::= <column_name> | YEAR(<column_name>) | MONTH(<column_name>)

So, LEFT(TestString,6) in your case is not allowed. Please consider to add a date column using "GENERATED ALWAYS AS" in your case.

<col_gen_as_expression> ::= GENERATED ALWAYS AS <expression>

Best regards,

Wenjun

[Former Member]

Hi Wenjun,

I Tried using Generated Always As  and created new column and used it in the  

ALTER TABLE  Testschema.Test_Part ADD (TEST1 DATE GENERATED ALWAYS AS (TO_DATE(TestString)));

ALTER TABLE Testschema.Test_Part

PARTITION BY HASH (ID) PARTITIONS 2, RANGE(Month(TEST1))

(

PARTITION Values = '201406',

PARTITION Values = '201407',

PARTITION OTHERS

);

SAP DBTech JDBC: [2048]: column store error: fail to alter partition: [2051] partition specification not valid;The attribute TEST1 is not allowed in a partition specification.

Regards

Devi

[Former Member]

Hi Devi,

I failed either. It seems GENERATED ALWAYS AS column cannot be used as partition expression. Sorry for misleading.

Best regards,

Wenjun

[former_member182114]

Did you tried directly the nvarchar date value?

You should reach same as "month"

PARTITION BY HASH (ID) PARTITIONS 2, RANGE(TestString)

(

PARTITION '20140601' <= Values < '20140701',

PARTITION '20140701' <= Values < '20140801',

PARTITION OTHERS

);

Regards, Fernando Da Rós

[Former Member]

Hi Fernando / Wenjun,

I have tried this yesterday but did not find time last night to update the forum.

This was working perfectly fine.

Thanks for all the help. Much appreciated.

Regards

Devi

[Former Member]

Hi Wenjun /Fernando,

I have couple of questions with respect to this partition implementation.

• We have a requirement to delete the less than 4 year or less than 24 months of data from the tables on a scheduling basis. We thought that Partition wuold be a better approach for that.
• Is there a way to check if the given range partition exists already for the table? We are planning to schedule a Job that deletes / Adds new partition on monthly basis.

Thanks for the help again.

Regards,

Devi

[Former Member]

Good idea Fernando, just stuck to date...

[Former Member]

>Is there a way to check if the given range partition exists already for the table? We are planning to schedule a Job that deletes / Adds new partition on monthly basis.

You can use SELECT * FROM SYS.M_CS_PARTITIONS WHERE ...; to check that. For job scheduling, you can have a look at Scheduling XS Jobs - SAP HANA Developer Guide for SAP HANA Studio - SAP Library

Best regards,

Wenjun

[Former Member]

Hi Wenjun,

Did you see my latest query in this Blog?

I need some advice on first question. I found the answer for second one, we can use M_CS_Partitions table to identify whether the respective partition exists for that table or not.

Regards

Devi

[Former Member]

You can have a look at ALTER TABLE - SAP HANA SQL and System Views Reference - SAP Library and ALTER TABLE - SAP HANA SQL and System Views Reference - SAP Library. If you want a job scheduling on monthly basis, every time you just need to add one partition and drop one partition...

Best regards,

Wenjun

[Former Member]

Hi Wenjun,

Thanks for that.

I was trying to understand if the approach of doing a partitioning and deleting the required partition to keep only relevant data in HANA table is a good approach or there is any other best practices for doing the same?

Regards

Devi

[Former Member]

Thanks Wenjun..

Appreciate your help.

So you think this is a good approach to delete the unnecessary data for maintenance point of view.

Regards

Devi

[Former Member]

It depends on your scenario/hardware/software. What I replied was just about the technical stuff and I did not provide any architecture design. By the way, dynamic tiering is introduced in SAP HANA SPS09 which is also a good idea for managing data cost efficiently.

SAP HANA Dynamic Tiering – SAP Help Portal Page

Best regards,

Wenjun

[Former Member]

Hi Wenjun,

Thanks for everything.

We are currently in SP7+ so looks lihe this is not an option for us.

Thanks again.

Regards

Devi

[former_member182302]

Hi Amir,

Have a look on this thread:

So as of now we too decided to create empty partitions of the future so that we will not be required to create partitions manually every week or every month.

Regards,

Krishna Tangudu

[justin_molenaur2]

Confused on your post. Do you mean to say that you have a multi level partition defined on the table with a hash-range option? This would indicate to me that you always intend to use a GUID value when querying the table, otherwise any advantage of the partitioning in the first level is gone.

In general, for range partitioning you need to define the intervals up front. There will always be an OTHER interval that will catch anything that doesn't meet currently existing intervals. So no need to worry that you will "lose" data, it will just fall into an un optimized bucket. However, when you add new range partition intervals, the OTHER bucket will be split apart if you add intervals that now match data present there.

No performance impact if you have empty intervals, and of course no (maybe a tiny bit) memory will be consumed since their empty.

I would make partition analysis a component of some periodic system activities.

Regards,

Justin

[pratik_doshi2]

Hi Amir ,

SAP hana till now has not provided options for Dynamic Partition for the tables.

So better, you keep the script and scheduled it with the scheduler.

No need to generate in advance. You can schedule it at the start of every month for next month.

I don't think blank partition will bring your performance down.

Regards ,

Pratik Doshi