IDS Forum

Re: Effect of lowering FILLFACTOR

Posted By: Art S. Kagel (Oninit)
Date: Thursday, 6 March 2008, at 10:15 a.m.

In Response To: Effect of lowering FILLFACTOR (ANDY KENT)

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

===========================================================================================

Messages In This Thread

• Effect of lowering FILLFACTOR
  ANDY KENT -- Thursday, 6 March 2008, at 9:46 a.m.
  • Re: Effect of lowering FILLFACTOR
    Art S. Kagel (Oninit) -- Thursday, 6 March 2008, at 10:15 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.