The onconfig FILLFACTOR

My day for asking questions ... but that is how one gains knowledge :) The lead DBA for the company for which I am now working for as a consultant wants to establish an initial FILLFACTOR of 100 for those indices that represent a serial column. Since none of the tables having a serial field are considered to be static tables, I believe he will wind up thrashing the index once additional rows are added to the table. He believes he will save tons of space and that the index will perform better since the index will be more compact. Has anyone experimented with a FILLFACTOR 100 for serial column indices? Has anyone have any success stories when establishing a small FILLFACTOR in the hopes of keeping the index levels down? Interested minds waiting for information. :) Take care. Clifton

He will indeed get a compact index, initially. Since the serial number is monotonically increasing (at least until it wraps) only the last node will split as new rows are added. So, the index will indeed remain highly compressed. In a more randomly keyed index, over time - as rows are added, nodes would be split in half and a single new key added to one of the halves. In short order you would have an index which is about 50% full which is the normal steady state condition of a healthy Btree that's being added to regularly. However, for the serial key only index, this will not happen. That DBA's not completely crazy ;-). Yes, the first key added will cause a split, but since the last node will normally be the only one added to anyway, even with FILLFACTOR=10 that would happen in a rather short time. To maintain the compression over time, you will have to reorg the index periodically. Also note, in favor of your instinct, if most common queries access recent rows only, there will be very little ongoing performance gains after the first few hours following a reorg. In that case, the ONLY gain is the storage savings which is nothing to ignore without reason. Art S. Kagel ----- Original Message ----- From: Clifton M. Bean <cmbean@sbcglobal.net> At: 3/ 1 16:10 > My day for asking questions ... but that is how one gains knowledge :) > > The lead DBA for the company for which I am now working for as a consultant > wants to establish an initial FILLFACTOR of 100 for those indices that represent > a serial column. > > Since none of the tables having a serial field are considered to be static > tables, I believe he will wind up thrashing the index once additional rows are > added to the table. He believes he will save tons of space and that the index > will perform better since the index will be more compact. > > Has anyone experimented with a FILLFACTOR 100 for serial column indices? > > Has anyone have any success stories when establishing a small FILLFACTOR in the > hopes of keeping the index levels down? > > Interested minds waiting for information. :) > > Take care. > Clifton