Skip to end of metadata
Go to start of metadata

Purpose

DML (Insert,Delete,Update and Load) operations on SAP IQ tables can cause index fragmentation and row fragmentation. The data fragmentation reduce the capacity and performance in SAP IQ. SAP IQ database administrators might expect to have to rebuild IQ indexes occasionally to improve the performance and increase the capacity. The purpose of this content is to explain the differences between the index and row fragmentation.

Overview

There are two different internal data fragmentations in SAP IQ: index fragmentation and row fragmentation. Stored procedure sp_iqindexfragmentation can be used to check for the the index fragmentation. Stored procedure sp_iqrowdensity can be used to report the row fragmentation. Store procedure sp_iqrebuiltindex can be used to rebuild the indexes and reduce the fragmentation.

Index Fragmentation

Internal index fragmentation occures when index pages are not being used to their maximum volumn.

SAP IQ indexes are generally self-reorganizing and seldom need to be rebuilt. However for HG and WD indexes, their internal data structure B-tree and G-Array pages can end up with unused slots as a result of deletes or incremental inserts with keys having new distinct values. Unused slots are always zeroed to help with compression.For those indexes (HNG, LF, CMP, DATE, TIME, DTTM) that uses the bitmap as internal structure usually will not regain any appreciable amount of space if rebuilt. FP, HG, and WD indexes may benefit from index rebuilding.

sp_iqindexfragmentation

This system stored procedure reports information about the amount of empty space within the btrees, garrays and bitmaps in all non-FP IQ indexes. 

The report shows how full the types of pages are in an index, in ten percentage ranges. The B-Tree and G-Array page percentages can help in making a decision about rebuilding an index. If most of the btree or garray pages are in the highest ranges such as "91 -- 100%", rebuilding the index will not be useful. If most are in the lowest ranges such as "0 -- 10%" and "11 -- 20%", then rebuilding may yield improvements in storage compression. However, rebuilding an HG index could take substantial time so that must be weighed against any potential space recovery in deciding whether or not to rebuild an index.

When reading the sp_iqindexfragmentation report:

  • garray pages apply to HG and WD indexes
  • btree pages apply to DATE, DTTM, HG, and WD indexes
  • bitmap pages apply to HNG, LF, CMP, DATE, TIME, DTTM, HG, and WD indexes.

The output for G-array and B-tree pages will never reflect 100% packing, This is intentional, to leave room on btree pages to handle new keys that may be added. This increase the performance in load, insert, and update. When an HG index is rebuilt, btree pages will be at most 50% packed.

The ranges for page percentages may not change significantly comparing the output of sp_iqindexfragmentation before and after using sp_iqrebuildindex to rebuild an HG or WD index. In general, rebuilding an HG or WD index using sp_iqindexfragmentation is likely to yield productive results only if most B-tree or G-array pages of an index are shown to be in the lowest Fill Percent ranges. As previously noted, other types of IQ indexes will not benefit from rebuilding index.

Syntax

sp_iqindexfragmentat syntax

Row Fragmentation

Row fragmentation occurs when rows are deleted. SAP IQ only free the page when all rows are deleted from the page.

 sp_iqrowdensity


This system stored procedure reports information about the internal row fragmentation for a table at the FP index level.

It reports density as a number between 0 and 1 representing the ratio of the minimum number of pages required by an index for existing table rows to the number of pages actually used by the index. The density is not intended to be an indication of the number of disk pages that could be reclaimed should an FP index be recreated or reorganized in some way.

A default flat-style FP index will not change its density if rebuilt. Thinking about it like an array where not every slot in the array is occupied, rebuilding the index will not change the structure of the array -- that is, it will not eliminate unused slots -- so the calculated density must remain the same.

If the cardinality of values in the index becomes much different than anticipated when the index was first created, rebuilding the index using the sp_iqrebuildindex command may alter the storage of index values.

Syntax

sp_iqrowdensity syntax 

Rebuilding Index

The system stored procedure sp_iqrebuildindex allows you to recreate indexes with a different IQ UNIQUE value, which also may result in altering its storage. If a substantial number of rows have been deleted in a column, rebuilding FP index may improve compression of FP index data pages. If no rows have been deleted, rebuilding such an FP index with the same IQ UNIQUE value will likely not accomplish any change.

Syntax

sp_iqrebuildindex syntax

Related Documents

Reference: Building Blocks, Tables, and Procedures

SAP IQ Internal Data Structure


__________________________________________________________________________________________________________

Use this structure to help you compose your contributions for WIKI and at the same time will ensure spelling and grammar.

  • No labels