Skip to Content

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.


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)

SAP.png

Total GLPCA* Indexes size before Index compression:

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

SAP.png


Few of the outcomes (Before and After)

Before:

Z.png

After:

Z.png

Before:

Z.png

After:

Z.png


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

Y.png
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

X.png

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Volker Borowski

    Be aware, that an index has quite a couple of freespace in it after a while. So the space comparison BEFORE vs. AFTER shows an effect, that is not only due to compression. To get that ratio, you should rebuild the index without compression first to eliminate the freespace. Then you compress and compare. Some deviation might occur as well if you rebuild parallel.

    Volker

    (0) 
    1. Harish Karra Post author

      Yes Volker,

      Here the calculation I have taken is to see the result of compression and the compression rates. And its a compress with rebuild option.

      Regards,

      Nick Loy

      (0) 

Leave a Reply