Re: Index Usage Statistics

SaTriGuy wrote: > > >> } > >> } I am looking for a way to determine when an index has become > >> } so fragmented that it should be re-built. > >> } An application I am developing continually deletes and inserts > > Be aware that the Informix engine does a lot of work to minimize the > probability of this problem by btree merges as index items are removed > from the index. The performance degredation may not really be caused by > the index degredation. The indexes that can experience performance degradation over time are indexes which 1)have grown up with the table so that index pages are seriously interleaved with data pages and 2)do not gain from index node locality with the data pages. This happens, usually, to secondary keys and to primary keys which do not represent the arrival order of the data rows. In other words the newly added row gets its key added to an index page that is somewhere else on the disk and may cause an existing node to be split moving half of the keys to the new node located at another extent further hurting locality and causing much head movement to follow the nodes and then to go find the matching data pages. See my post to Doron Rippel's post 'Performance Problems' a little while ago for solutions. None are good. And I do not know any good way to track this mess. In 7.xx you can detach the offending index to prevent the problem altogether. Art S. Kagel