Unused Indexes

Unused Indexes

Print

Indexes should help to find data sets fast(er) and more targeted. But the usage of too many Indexes leads to poor performance. Why? In reading mode the optimizer has to calculate all access possibilities and the corresponding workload and finally to compare the alternatives. Performing write activities mean that new data have to be inserted in the new Indexes. This needs time, too. Additionally Indexes occupy plenty of space and can reduce free space of DBspaces significantly. Until now the “oncheck –pt” command was the most favourite option to show the time stamp of the last usage of the Index. But following this approach was work intensive because every “oncheck” command referred to one table only!

Since IFX 14.10 xC6 you can use the new created field „acc_or_mod_tm“ in the „sysptnhdr“  table. The sysptrhdr table contains information about partition headers. The new field saves the time of the last access. Executing a select command it is now possible to get an overview about the usage of the Indexes e. g. on database level very quickly.

But before deleting Indexes you should be aware that within companies periodic events play an important role. Sometimes an Index is used correctly only once per month, quarter or even year. Check it internally before you delete an putative “unused” Index!