Re: Index Usage Statistics

Joe As a reader of your excellent survival guide I feel very much like a student teaching his teacher, but my answer to your question is from Liz Suto's performance guide. Perhaps a better analogy would be a person playing two chess grandmasters simultaneously in different rooms? :-). Well to answer your question. I would look at the fill factor at the leaf level only since that is where the index fragmentation would happen in a B tree structure. For instance using your example: > Index Usage Report for index xpkmember on crm2:informix.member > > Average Average > Level Total No. Keys Free Bytes > ----- -------- -------- ---------- > 1 1 40 1544 > 2 40 113 550 > ----- -------- -------- ---------- > Total 41 111 574 The fillfactor in this case would be calculated as: Fillfactor = 100 - (550/2048)*100 = 74 % (here 2048 is the size in bytes of the Informix page on my HP-UX box, would be 4096 on AIX boxes). If the table is very volatile I would recreate my index if the fillfactor goes beyond 65-70%. When recreating I would specify my FILLFACTOR = 50. For more static tables (like code master tables) I would create my index with FILLFACTOR = 100 (or the default of 90). BTW, about the number of levels, a DB2 DBA once told me that if your index has more than 4 levels, probably it is not a very good candidate for a B-tree index in the sense that it might have high cardinality. Also it would need more accesses to get the data from the index. Since then I have attempted to monitor indexes that have more than 3-4 levels to see if I could design a better index. HTH Sujit Pal