Re: fillfactor

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 growing the effective fill-factor always approaches 100% and any split nodes are always 50% full immediately after the split. This is, however, only effective long term for tables that will not grow, or will grow only slowly. Otherwise you are only trading a short term space savings, the index pages for future rows have to be allocated sometime, for a greatly increased insert throughput penalty as the table grows (see below for the reasons for the runtime cost of large fill-factors). The effect on performance is mainly during inserts and updates. If an index page is full and an inserted key, or the new value of an updated key, needs to be added to that node the node must be split which entails several I/O's including updating the appropriate bit-map page and may cause a new extent to be allocated to the table or index (if detached) which takes even more time. Therefore, if you know that you will be adding large numbers of new rows to a table then create it's indexes with a small fill factor wasting space, temporarily, to save time later. A good example of this would be integrity indexes (unique index needed to validate a dataload) added to an empty table before loading data. Art S. Kagel