Re: fillfactor

Below is a conversation which probably should have been posted to the list originally, but I screwed up, so here it is now. Anyway, I would like to hear comments on this subtle point in index fillfactors from anyone who can address it. The point in question is the behavior of indexes with ever-ascending keys, as in serial columns. Does the insertion of a new key (higher than any existing key) cause the last page to split, or does Informix merely add a new page? Mark Collins mcollins@us.dhl.com ================================================================= On Wed, 16 Jul 1997 MCOLLINS@iahnbcis.attmail.com wrote: > Art, > > I agree with your points about splitting requiring extra I/Os and leading > to growth of the index. I am curious about your view of an appropriate > fillfactor for an ever-increasing key (for example, a serial column, or > datetime stamp). I believe that in this case, you may want to rebuild > the index with fillfactor 100, then allow inserts to split the last page, > as you would not be going back to insert on any of the other pages. I definitely agree on this point. A fill factor of 99-100% would be ideal. I'd want to leave one or two entries per page so I can insert missing serial numbers manually but otherwise yes. > Actually, if Informix is really on the ball, they should recognize that > the new key is the highest in the index and rather than splitting the > existing page, they should just add a new page at the end. VSAM on IBM > mainframes performs in just this way, and this eliminates the > under-utilization caused by this sort of key. Also a salient point. I wonder if Jonathan or others from Informix can comment about current behavior in 5.xx, 7.xx in this case? > > FROM: ilist%rmy.emory.edu@rmy.rmy.emory.edu > > Mark D. Stock wrote: > > > > tony edwards wrote: > > > > > > Hi, > > > > > > Does anyone use the "fillfactor" syntax when creating an index ? If > so > > > have you noticed reduction in the physical size used by the index ? > > > and > > > does this result in better performance ? > I find Mark's answer too brief so here is an expanded discussion: > > You will see a reduction in size by increasing the fill factor for an > index on a table which is already populated. For indexes that are [TRIM] Art S. Kagel, kagel@bloomberg.com