Index compression on Oracle 11g & SAP R/3 4.7

[Former Member]

Hi,

I have already upgrade my SAP R/3 4.7 system database from Oracle 9i to Oracle 11g (11.2.0.2). I also have try it's index compression and some table compression. I am using BRTOOLS to compress the indexes and tables.

I also have testing system with same environment and recently testing method on implementing indexes & tables compression. When I try to compress index using BRTOOLS, it tooks some times. But when I am using sql command (via SQL Plus) index compression step run much faster.

Any idea about this two different way ?

Thank you.

Ardhian

http://sapbasis.wordpress.com

http://ardhian.wordpress.com

[Former Member · Accepted solution]

I have another question about index compression

I am using Oracle 11g (11.2.0.2) and SAP R/3 4.7. On some SAP Notes (I forgot which one) there are couple tables that should not be compressed such as CDHDR, RFBLG, CDCLS, VBDATA, etc.

I am wondering how about their indexes ? Is it ok to compressed or not ?

Ardhian

[Former Member · Accepted solution]

Yes you can still compress the indexes. But i recommend you only compress the large ones (i would not do VBDATA just because it almost never contains data).

Cheers Michael

[jairo_pedroza · Accepted solution]

Hello,

For indexes, I suggest you read and use scripts attached in SAP Note 1109743 - Use of Index Key Compression for Oracle Databases. It will give you idea about indexes.

In case of tables, read note 1436352 - Oracle 11g Advanced Compression for SAP Systems

exclude ARFCSSTATE ARFCSDATA ARFCRSTATE TRFCQDATA TRFCQIN TRFCQOUT TRFCQSTATE QRFCTRACE QRFCLOG

Hope it help you,

Jairo Pedroza

[Former Member]

Hi Lies,

take a look at this note to have a better knowledge of this topis.

Generally, brtools simply automatize several commands / procedures that you could perform manually using sqlplus.

Brtools doesn't introduce an additional time requirements.

Regards

Leo Capasso

[Former Member]

Basically they are the same, but there are a few options that can greatly influence the performance. I often use brtools to calculate the number of compressed columns (sap_proc) and then run the statement directly with parallel X ('Index parallel degree' -> in brtools) and nologging in sqlplus.

For large tables you definitely have to set the 'Index parallel degree', nologging also helps a lot, but if you have to do a restore/recovery, you will have to recreate the indexes again.

But generally, brtools should not be slower than sqlplus.

Cheers Michael

[Former Member]

Hi Michael,

What kind of procedure we should use on compressing index/table ? orac_proc (default) or sap_proc (procedure from SAP) ?

Is the any best practice or SAP Notes to do that ?

Thanks.

Ardhian

[Former Member]

These methods only determine the number of columns to compress, the compression itself is always done the same way. Personally i always use the sap_proc, because it worked on 10g as well (ora_proc needs 11g).

Check note https://service.sap.com/sap/support/notes/1464156 3.6 to 3.8 for details.

Cheers Michael

[Former Member]

Hi,

Are these both executions using same command?

Yes - Definately a direct call at sql takes less time because its a direct call from and to to the database.

Regards,

Nick Loy