cancel
Showing results for 
Search instead for 
Did you mean: 

"Check record count of not partitioned column table and consider to split it"?

former_member191664
Active Contributor
0 Kudos

 

I have seen many of these alerts from Hana: "Check record count of not partitioned column table and consider to split it"

According to page 29 of the "Technical Operations Manual Trigger-Based Data Replication Using SAP LT (Landscape Transformation) Replication Server for SAP HANA", it says that "If the table size (in the SAP HANA database) exceeds 2 billion records, you must split the table by using the available partitioning features."

1st, why are we getting these premature alerts? For example, BSC_OASIS.CLM_CAPITIN has record count 679208656 and BSC_CLAIMS_DAILY.DAILY_CLM_SMRY has record count 377118666.  None of them is exceeding 2 billion records yet.

2nd, what partition method(s) can be used to split a column table?

At last, should tables to be created as partitioned from now on just in case we won’t have to worry about the splitting table later?

Appreciate your advice on this table partitioning.

Regards,

Jin-Chong

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Jin-Chong,

1. On our POC exercise, we created a table that was meant to have around 5 billion records completely unaware of the 2 billion limit restriction around non-partitioned tables. Ofcourse, we did see the alerts but didn't pay too much attention and a consequence we have had to recreate the table again with partitions on it and redo the whole data load.

So, there you go - the importance of a pre-mature alert - if only we had paid more attention

2. In http://help.sap.com/hana_appliance/ if you scroll down under System Administration and Maintenance Information, there is a nice pdf on partitioning and distribution of large tables - explains different types of partitions and sample code for each.

3. The best pratice as I see it would be to partition the table when you create it or while it's still has low data volume. Ofcourse, you can ALTER tables by splitting and merging partitions but doing that for large tables is both memory intensive and slow.

Although, one question still remains for me - in the context of replicating large ECC tables, at what point do we intercept and split the tables into partitions? If it is after the initial load, for large tables this could turn out to be too late...  Any advice from others in this forum who have done this would be beneficial.

Thanks

Anooj

rama_shankar3
Active Contributor
0 Kudos

I do not think that you can partition the tables after the data is loaded. Are you sure?

I will also check this and get back to you.

Regards,

Rama

Former Member
0 Kudos

Hi Rama,

Yes, you can partition a non-partitioned table with data on it:

For e.g.

ALTER TABLE SCHEMA.TABLE PARTITION BY ROUNDROBIN PARTITIONS 4;

I tried the above statement for a non-partitioned table with 200 records and it took close to 2 secs for completion.

Thanks,

Anooj

rama_shankar3
Active Contributor
0 Kudos

Thanks Anooj. I still feel that it is a good practice to decide on partions needed prior to loading data.

Rama

former_member191664
Active Contributor
0 Kudos

Thank you so much, Anooj.

In addition, the number of partitions is determined by the engine at runtime according to its configuration, such as

ALTER TABLE SCHEMA.TABLE PARTITION BY ROUNDROBIN PARTITIONS GET_NUM_SERVERS();

Regards,

Jin-Chong

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Jinchong,

Check this thread http://scn.sap.com/thread/3171886

Anooj to answer your question,if you know in advance the table needs partitioning; you need to create them manually in HANA with partition and then do the replication from ECC.

Hope this helps.

Regards,

Khadar

Former Member
0 Kudos

Thanks Khadar.

Was thinking you could also import the meta data of the ECC table into HANA (using Data Services) which will create the table for you and then can create the partitions on it isn't it?

Thanks,

Anooj