Indexes are structures that hold keys that allow fast access to record data. The index structure is typically about 10% of the total table size. Ideally heavily accessed tables will have the entire index structure cached in memory.
The index structure is held in a B-Tree format. A B-Tree allows traversing the tree to find the location of a key that matches the request. This is done quickly and typically only needs to access 3 or 4 database blocks to find the match. From there the application can fetch the row based on the ROWID. Conversely, if there was no index, hundreds or thousands of database blocks would need to be accessed to find the desired row.
The Index holds the entire range of values in the table. For example, if there is an index on a field called “Name”. The index would contain all possible values for the names in the table.
When all the index blocks are completely full, the next new entry will create an index block split. Before a new key can be inserted into the block, the block is split into 2 different blocks and the parent block gets the new pointer to the new block.
Once this is complete, the new key can be inserted into the appropriate block. This is a common occurrence when adding new data. Over time while rows are being created to the table this leads many index block splits which results in poor index utilization.
Use Dbanalys or Ixanalys to find an index block utilization.
proutil dbname -C dbanalys
In the above example there are many indexes on the tr_hist table with some of them having 3 levels indexes and 4 level indexes.
The tr_hist table has 47,118,632 rows in it. This is stored in 3,524,450 4 KB database blocks, or about 13 rows per block. Conversely the tr_addr_eff index uses 20,482 4 KB database blocks, which means it can store 2,300 keys in each block.
The tr_part_eff index is a 4-level index consuming 74,469 4 KB database blocks, but the index size is only 148.7 MB in size. 74,469 x 4096 = 305 MB. This means that most of the index blocks are half filled. Compacting this index would accomplish two things.
It is run one index at a time
proutil dbname -C idxcompact table.index [utilization]
The index compact is going to read the entire index into memory and work on the index block by block to make changes. During this time, there will be much more Before Image activity, After Image activity, and OE Replication activity. It is best to first test it on a test copy of your database to see how long it takes to run, then decide when to run it on production, either at night or on a weekend, if it takes a long time to run.
There is not much value to compacting small indexes as their utilization will decrease rapidly as there are not many index blocks associated with the index. A good rule of thumb is to look at indexes that are more than 50 MB in size. If these larger sized indexes have a utilization of less than 80%, it is worth compacting them. The default compaction level for index compact is 80%, which is too low, especially for large indexes. It is better to use 90% - 95%. Do not be tempted to use 100%, as this will cause many index splits, which can be a performance killer, when rows are being added to the table.
Maintaining database indexes is just one of the many activities required to have a well performance application environment. Regular monitoring and periodic maintenance is required. Stale systems have the risk to running into problems like poor performance or unplanned outages. This article is addressing one aspect of the required maintence. If in doubt, ask a professional
Is your Progress OpenEdge database running optimally? Could there be an unknown issue that could potentially damage or bring down your production database?