filling up index pages

I'm running IDS 7.30 TC7 and I've got my FILLFACTOR set to the default of 90, but when I poke around in some of my indexes with oncheck -cT I see that the leaf nodes can often be 60% full or so. Some of these indexes include varchar columns and others are as simple as two integer columns. I've checked the formulas in the Performance Book, etc and they seem to indicate that I should get tighter packing of index nodes than this. Any ideas from the experts? Cheers

On Tue, 04 May 1999 21:20:40 -0400, Jay Walters <jwalters@computer.org> wrote: >I'm running IDS 7.30 TC7 and I've got my FILLFACTOR set to the default >of 90, but when I poke around in some of my indexes with oncheck -cT I >see that the leaf nodes can often be 60% full or so. Some of these >indexes include varchar columns and others are as simple as two integer >columns. > >I've checked the formulas in the Performance Book, etc and they seem to >indicate that I should get tighter packing of index nodes than this. FILLFACTOR is only significant at index-creation time, i.e. the FILLFACTOR is not maintained over the life of the index. Over time, index nodes will average about 75% full no matter what you do (assuming a volatile table, of course). Only option to "guarantee" denser packing is to rebuild regularly (which isn't likely worth the bother). If a leaf node has recently split, it may well be down to almost 50% full; as additional rows are added, these should start to fill up. You should not be interested in the fullness of any individual index pages, as it is rather insignificant in the context of the entire index. Dave

I suppose what bothers me about having the leaf nodes 60% full is that I need to keep these specific indexes in memory, and 1% = 1MB on this index. While I've got 2GB in my machine, spending 40MB of that on just this index holding empty space doesn't seem like the most efficient thing I've ever done. David Kosenko wrote: > On Tue, 04 May 1999 21:20:40 -0400, Jay Walters > <jwalters@computer.org> wrote: > > >I'm running IDS 7.30 TC7 and I've got my FILLFACTOR set to the default > >of 90, but when I poke around in some of my indexes with oncheck -cT I > >see that the leaf nodes can often be 60% full or so. Some of these > >indexes include varchar columns and others are as simple as two integer > >columns. > > > >I've checked the formulas in the Performance Book, etc and they seem to > >indicate that I should get tighter packing of index nodes than this. > > FILLFACTOR is only significant at index-creation time, i.e. the > FILLFACTOR is not maintained over the life of the index. Over time, > index nodes will average about 75% full no matter what you do > (assuming a volatile table, of course). Only option to "guarantee" > denser packing is to rebuild regularly (which isn't likely worth the > bother). > > If a leaf node has recently split, it may well be down to almost 50% > full; as additional rows are added, these should start to fill up. > You should not be interested in the fullness of any individual index > pages, as it is rather insignificant in the context of the entire > index. > > Dave

On Thu, 06 May 1999 08:51:32 -0400, Jay Walters <jwalters@computer.org> wrote: >I suppose what bothers me about having the leaf nodes 60% full is that I >need to keep these specific indexes in memory, and 1% = 1MB on this index. >While I've got 2GB in my machine, spending 40MB of that on just this index >holding empty space doesn't seem like the most efficient thing I've ever >done. You could rebuild the indexes using a lower fillfactor that you had originally (say 75%). This would allow more room for gowth w/o splits, while being more full than the 60% you are seeing. Dave