How to partition PCL2 table

[former_member539700]

Hi experts,

the customer asked me to make a partition on the table PCL2.

Do you have any suggestion about it? There are specific criteria to partitioning on this table? Is there any documentation SAP?

There are specific indications for the HR tables?

Please help me.

Thank you very much.

[ACE-SAP · Accepted solution]

Hi

Why does your Customer want to create partitions for that table ?

There is a note on parition engine, but it is designed to be used to speed up archiving of data.

Partitioning is native on BW but not on ERP.

742243  - General table partitioning as of SAP_BASIS 46C

722188  - FAQ: Oracle partitioning
1333328  - Partitioning Engine for Oracle

If performance is the reason there are other ways to improve it, table and index compression can be useful (even if it might not be that efficient for a table such as PCL2 that contains a LongRaw field)

An other solution would be to migrate the LongRaw field to a  Securefile if your Oracle version is 11g.

Securefiles are supposed to improve performance

1426979  - Oracle 11g: SecureFiles - The new way to store LOB data

Regards

[former_member539700 · Accepted solution]

Hi Yves,

thanks for the reply.

The problem is that this table is growing and now it has come to 1TB in size.

This growth has led to a loss of performance. This growth has led to a loss of performance. We had already converted from LONG_RAW to LOB_SEGMENT. We used the BRTools.

The quality group would like to improve query performance by partitioning the table using the field RELID.

Is correct in this case use a partitioning strategy? There are contraindications for no BW tables? And specifically for the critical tables in HR?

Thanks in advance

[ACE-SAP · Accepted solution]

Hi

I've never worked on an HR system so I do not know the use of PCL2 and what is stored inside RELID.

Who has recommended partition, does any study on that subject took place ?

I'm sure you've already search for OSS notes on performance in that area.

Check if PCL2 has been converted to securefile and if it has been compressed

SELECT table_name, column_name, securefile, compression FROM dba_lobs where table_name='PCL2' and owner like 'SAP%';

If not I would recommend to perform some test with medium compression

brspace -u / -f tbreorg -a lob2lob -o sap<sid> -s <src_tsp> -t PCL2 -n <new_tsp> -compress ctablob -lobcompr medium|high

1431296 - LOB conversion and table compression with BRSPACE 7.20

1436352 - Oracle Database 11g Advanced Compression for SAP Systems

If you really want/need to go with partitioning you should open a customer message to discuss that with SAP (if that subject is not considered as consulting...)

There are not technical issues, partitioning is transparent for SAP, it does not slow down inserts and can speed up updates/reads ... if  you have choose the good field for partitioning.

You will have to manage partitioning manually. As RELID is a char on 2 positions that can lead to at least 676 partitions with alphabetic... or up to 1296 if alpha + numeric.

Check that thread on the same subject https://scn.sap.com/thread/3429669

Regards

[former_member539700 · Accepted solution]

Thank you very much Yves,

your suggestions were very helpful.

As soon as I have finished I will let you know the results. It might be useful to someone else.

Best regards

[Former Member · Accepted solution]

Hi Silvio,

Here you can find my first experience:

Kind Regards

[former_member539700 · Accepted solution]

Hi Jose,

thanks for your support.

I would like to know about partitioning you did:

Was it via Oracle or SAP (TCODE SE14)?

What kind of partitioning option did you use?

I suppose you used “RANGE” option on the first step and then “LIST” option,

is this correct?

  Regards