Re: Index Usage Statistics

>> } >> } 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 >> } rows into a large table. I don't want the program to recreate >> } the index every time a large number of deletes and inserts have >> } occurred, because of the time it takes to rebuild the index. I >> } would like to have the program query the status of the index, >> } and display a message when the index needs to be re-built. >> } >> } Thanks in advance, >> } Drew > >====================================================================== > >Bill, > >Thanks for your response. >oncheck -ci dbname:table_name does verify the indexes are consistent.>I guess I phrased my question incorrectly. We are experiencing >performance degradation after rows are deleted from a table and then >re-inserted. Informix said the index pages are not cleaned entirely >and the only cure is an index rebuild. I am looking for an indicator >of when a tables indexes are so fragmented that they need to be rebuilt. >Since a client program is in control of the deletes and inserts, I was >hoping to include this indicator in the program so as to notify the user >that the indexes need to be rebuilt. > >Thanks again, >Drew Take the number of leaves (oncheck -ci) and divide that by the number of rows for that table. As this number increases, then that means the index is becoming "more empty". 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. Madison Pruet