Effect of lowering FILLFACTOR

Yesterday I asked the best way to circumvent a bad index extent size calculation by IDS and Art suggested lowering FILLFACTOR. However can someone explain under what circumstances IDS will use the remaining space - INSERTs or UPDATEs? And will it make a difference to the space take-up what values are written to the index, i.e. whether they're new or duplicate and whether they're in the middle of an existing value range or at one end? Does the attempt to reach a certain fillfactor only apply during index creation, or afterwards as well? We have some indexes where, if I size the data extents to ACTUAL data, the index extent size ends up being out by a factor of 10 (i.e. with FILLFACTOR=90 the new index ends up being 10 x the generated extent size). This is because of most of the rowsize being taken up with VARCHARs that hardly get used. (The VARCHARs themselves aren't indexed.) We need to allow for 10-20% growth of data. What would be a sensible fill factor in this case? Andy

ANDY KENT wrote: > Yesterday I asked the best way to circumvent a bad index extent size > calculation by IDS and Art suggested lowering FILLFACTOR. > > However can someone explain under what circumstances IDS will use the > remaining space - INSERTs or UPDATEs? And will it make a difference to the > space take-up what values are written to the index, i.e. whether they're new > or duplicate and whether they're in the middle of an existing value range or > at one end? Does the attempt to reach a certain fillfactor only apply during > index creation, or afterwards as well? > FILLFACTOR is only operative during index creation. Once the index exists the engine ignores it altogether (indeed, as noted to another poster last month the FILLFACTOR used to build the index initially is not stored anywhere - I looked in the hopes that myschema could optionally output the actual value - no dice). So, once the index is built, any new key will go onto the page containing the next lowest key if there is room on that page. If not the page is split into to pages each 50% full and the new key is added to the appropriate of those two pages. This is normal operation, no matter what the fill factor is/was. > We have some indexes where, if I size the data extents to ACTUAL data, the > index extent size ends up being out by a factor of 10 (i.e. with FILLFACTOR=90 > the new index ends up being 10 x the generated extent size). This is because > of most of the rowsize being taken up with VARCHARs that hardly get used. (The > VARCHARs themselves aren't indexed.) We need to allow for 10-20% growth of > data. What would be a sensible fill factor in this case? > Depends on the nature of the key and what new key values will look like. For a key, like say SERIAL or SERIAL8, which is monotonically increasing in value, the index will always be appended to and any space reserved by FILLFACTOR will be unused forever and so wasted. For indexes like these, use a FILLFACTOR of 100. For indexes with a smooth or normal distribution of key values set FILLFACTOR to somewhere between 70 & 80 depending on how smooth the key distribution tends to be. Birth year may be good example of a key exhibiting a fairly normal distribution over the population of living people. Art S. Kagel Oninit > Andy > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========