Btree Index Level

Hi all, I have a database running with FILLFACTOR at 90%. I'm not sure is this the correct value I should maintain.

On Sun, 28 Mar 2004 21:02:16 -0500, miyaki wrote: > Hi all, > > I have a database running with FILLFACTOR at 90%. I'm not sure is this the > correct value I should maintain. From the documentation, the FILLFACTOR is > the parameter to controling how indexes are filled. With the current > setting, the database only has 10% room of index growth. Since the database > is supporting a heavy OLTP type of transaction, I'm afraid that the 10% room > for growth might not sufficient and might create a multi level of index > nodes which may affect the performance. Is there a way to find how many > level of index nodes in the database? FILLFACTOR only has an effect during an index build. It permits you to control the default amount of unused nodes in a NEW index to permit growth without node splitting overhead. So if you know that you normally build new indexes mostly on mature tables then the 90% default is fine. If you ever build an index on a table you are about to add large numbers of rows to you can override the default with the FILLFACTOR keyword in the CREATE INDEX statement to a lower value like 50. Art S. Kagel