In this article I’ll show you how to do table / index compression with brspace (part of brtools). For this example I use Oracle Database 11G (Enterprise Edition). One of the great feature available in Oracle Database 11G is table and index compression (Part of Oracle Advanced Compression) that can reduce your database size significantly. It often happens that your SAP system as your primary OLTP system has high database growth per month, making your storage exhausted. The bigger your database size the more difficult for you to do Database Administration. By doing database compression you can save & release your space.
For more information about Oracle Database 11G Compression Feature you can refer to this SAP note:
1436352 – Oracle 11g Advanced Compression for SAP Systems.
We can do the the task using standard SAP tool brspace 7.20.
1. Run brspace to reorg the tables / indexes you want to compress,
ora<sid># brspace -u / -f tbreorg -t E071K
 |
| How to do table / index compression Pic 1 |
2. Choose option Compression action (ctabind)
 |
| How to do table / index compression Pic 2 |
For more information about available option for compression you can refer to 1464156 – Support for index compression in BRSPACE 7.20. Below is the excerpt from the note about compression option.
table/lob/index compression action:
none - no action ctab - compress tables clob - compress LOBs cind - compress indexes ctablob - compress tables and LOBs ctabind - compress tables and indexes clobind - compress LOBs and indexes ctablobind - compress tables, LOBs and indexes dtab - decompress tables dlob - decompress LOBs dind - decompress indexes dtablob - decompress tables and LOBs dtabind - decompress tables and indexes dlobind - decompress LOBs and indexes dtablobind - decompress tables, LOBs and indexes ctab_only - compress uncompressed tables only clob_only - compress uncompressed LOBs only cind_only - compress qualified indexes only ctablob_only - compress uncompressed tables and LOBs only ctabind_only - compress uncompressed tables and indexes only clobind_only - compress uncompressed LOBS and indexes only ctablobind_only - compr. uncompr. tables, LOBs and indexes only dtab_only - decompress compressed tables only dlob_only - decompress compressed LOBs only dind_only - decompress qualified indexes only dtablob_only - decompress compressed tables and LOBs only dtabind_only - decompress compressed tables and indexes only dlobind_only - decompress compressed LOBs and indexes only dtablobind_only - decompr. compr. tables, LOBs and indexes only
default: none
3. Continue to next screen
 |
| How to do table / index compression Pic 3 |
4. Wait until the process finish and see the message.
 |
| How to do table / index compression Pic 4 |
 |
| How to do table / index compression Pic 5 |
After the compression complete you can compare the size of the organized tables before and after.
 |
| Before |
 |
| After |
Note:
This feature is not available in Oracle Database 10G and below.
To get complete reference about SAP Database Administration with Oracle you can read: [amazon-element asin="1592291201" fields="title,lg-image,large-image-link"]
Hi, does it disturb database performance when doing compression ?
Yes it slightly consumes CPU/memory/IO resources, so you better do it when there’s no workload.