Index compression and Table reorganization

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.

Online:

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

Securefile compression

RMAN 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:

Current GLPCA* Indexes size is as below (Post Index compression using Simple compression option):

Few of the outcomes (Before and After)

Before:

After:

Before:

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):

Current free space available at Database is 22% ( 691G approx)

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