Index compression and Table reorganization (Oracle 10g on)
Index Key compression:
Index Key Compression is supported in SAP environments from Oracle Release 10.2 on. Use Index Key Compression by rebuilding existing B*Tree indexes in the database to store B*Tree indexes as space efficient as possible.
Advantages and Disadvantages of Index Key compression:
Saves disk space for indexes and reduces total database size on Disk
Customer experiences show that up to 75% less disk space is neededfor key compressed indexes. Even after index reorganisations have taken place an additional up to 20% total disk space reduction for the whole database can be achieved using index compression. Without any reorganizations done before the total space savings for the complete database may be higher than 20% using index compression as index compression implicitly reorganizes any index
Reduces physical disk I/O and logical buffer cache I/O improving buffer cache quality
Higher CPU consumption
Every compression technique comes with higher CPU consumption. The higher CPU consumption is more than compensated by doing less logical I/O for index blocks in the database buffer cache
Improved overall database throughput
Early customer experiences have shown a 10-20% better database throughput for an SAP system by using index key compression in a non CPU bound environment
*Limitations and Restrictions are applicable for Compress with convert methods
Table reorganization (Online, Not covered fully):
The term reorganization refers to the reconstruction of objects in the database. A distinction is made between offline and online reorganization.
You can always access the affected segments during the reorganization. In general, the SAP system can therefore be run in parallel to the reorganization.By default, online reorganizations are carried out based on the Oracle package DBMS_REDEFINITION. This means that no access locks occur when segments are copied in the database. Non-SAP tools sometimes execute online reorganizations based on a different system (for example, the contents of the Redo log).
Other compression options available in Oracle 11G (Not covered)
OLTP Table Compression
DATA GUARD NETWORK COMPRESSION
Sample Index compression using simple compression
Before Index compression and post archiving of ACCTIT table, the total free space of QAS is 18% ( 561G approx)
Total GLPCA* Indexes size before Index compression:
Few of the outcomes (Before and After)
Table reorganization reorganizes the data and fragments the space (of all rows).
Given a small amount of space back after the reorganization
Executed only for small tables (>=30G)
Sample result shown below (Number of rows from source to target ,reorganized):
Total freed up size (compare to old) = Reduced Indexes size of the database + Newly added temporary size
Reference documents and SAP Notes:
1289494 – FAQ: Oracle compression
1109743-Use of Index Key Compression for Oracle Databases
1436352 – Oracle 11g Advanced Compression for SAP Systems
1431296 – LOB conversion and table compression with BRSPACE