Do SAP recommend table partition and compression?

[Former Member]

Dear expert,

Do SAP recommend table partition and compression in its environment?

Seems that the table partition is not favorable since it may cause other big perf problem.

What do you think?

And the table compression, it is practical or not in SAP environment？

BR,

Kate

[stefan_koehler · Accepted solution]

Hi Kate,

> Do SAP recommend table partition and compression in its environment?

In what kind of environment? Table partitioning is excessively used in OLAP environments (BI). Implementing partitioning in OLTP environments (e.g. classical ERP) is pretty limited as SAP is not using the best data types on oracle database level for it. However there are some "work-arounds" like SAP Partitioning engine for that, but this is just a rake related to the "real power" of partitioning. What kind of compression are you talking about (OLTP compression?)? It can be used on partition level at all.

> Seems that the table partition is not favorable since it may cause other big perf problem.

It is pretty limited by its implementation by SAP (not because of performance problems or whatever). Partitioning is usually used for better data management and not because of performance only (but there are several cases where it can improve the performance of course).

> And the table compression, it is practical or not in SAP environment？

It depends.

General questions like "do you recommend" or" what is the best way" are useless.

Just think about a real life example like "Do you recommend to use a car or a truck?". I would usually check "For what?". A truck would be the best option, if you need to transport large objects, but a car if you don't need to. It all depends on the requirements and targets.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stephen,

Thank you very much for answer.

For the partition part, now i have general idea about it thanks to your answer.

Why I ask this is that I think

From point of view of Oracle, all the tools he created , he will strongly recommended.

But it may be another story , if the oracle DB is implemented in SAP environment.

Even , just like you said,  the principle varies among different SAP solutions.

So my last question:)

Is there situtaion where SAP does not recommand to do the table/index compression?

Thank you very much!

Kate

[stefan_koehler · Accepted solution]

Hi Kate,

> Is there situtaion where SAP does not recommand to do the table/index compression?

Yes, but this is not SAP specific. Table and index key compression are working differently and so they have different limitations. I still assume that you are talking about OLTP table compression (as there are several different compression options).

OLTP table compression

Oracle Documentation (Restrictions on Table Compression):

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2128733

SAPnote #1431296 (Exception handling during table compression) due to SAP application specific table handling.

Index key compression

Depends on the amount of leading columns and duplicated keys:

http://scn.sap.com/community/oracle/blog/2008/04/21/oracle-index-key-compression

There are several other cases when compression is not recommended or makes the situation even worst, but i guess this would be too far away right now.

Regards

Stefan

[Former Member · Accepted solution]

Hello Stefan,

Well, i have to say , it needs time to consume all these info.

Thank you very much:)

Best Regards,

Kate

[former_member188883]

Hi Kate,

Following SAP notes should answer your queries

  Note 105047 - Support for Oracle functions in the SAP environment

Note 1436352 - Oracle 11g Advanced Compression for SAP Systems

Regards,

Deepak Kori