Re: FILL FACTOR
Q: how to find out the FILLFACTOR an existing index was created with. Answer: it isn't stored anywhere in the system catalogs (sysindices/sysindexes), because the fill factor only applies at index build time and isn't maintained afterwards. Workarounds offered: inspect an index leaf page directly, or use oncheck -pT output and calculate the actual page fullness — accurate right after a rebuild, but drifting over time as the index changes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Neil Truby wrote: > Having spent about half an hour looking for a CREATE INDEX example showing > show to use the FILLFACTOR option I now can't find how to display the > current fill factor for an index! > > Does anyone know please? > Neil, It's not recorded in the system catalog at all anywhere. Someone though that since it's ONLY effective at the time that the index is created that no one really cares what it was at that time so there's nowhere in sysindices (or the older sysindexes) to record the FILLFACTOR that was used to create the index. Remember that the FILLFACTOR level is NOT maintained after creation. Art S. Kagel Oninit > Thx > Neil > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >
"Art S. Kagel (Oninit LLC)" <art@oninit.com> wrote in message news:mailman.214.1199603911.20610.informix-list@iiug.org... > It's not recorded in the system catalog at all anywhere. Someone though > that since it's ONLY effective at the time that the index is created that > no one really cares what it was at that time so there's nowhere in > sysindices (or the older sysindexes) to record the FILLFACTOR that was > used to create the index. Remember that the FILLFACTOR level is NOT > maintained after creation. Thanks, Art, for the explanation as to why I can't find it. I take issue though with the assertion that no-one cares what it was at index creation time. If you'd just spent 5 hours of your Saturday on Tech Support advice altering the fill factor on indexes to try to get around a bug, you'd like to be able to confirm that you'd actually done it properly! rgds Neil
Neil Truby wrote: > "Art S. Kagel (Oninit LLC)" <art@oninit.com> wrote in message > news:mailman.214.1199603911.20610.informix-list@iiug.org... > >> It's not recorded in the system catalog at all anywhere. Someone though >> that since it's ONLY effective at the time that the index is created that >> no one really cares what it was at that time so there's nowhere in >> sysindices (or the older sysindexes) to record the FILLFACTOR that was >> used to create the index. Remember that the FILLFACTOR level is NOT >> maintained after creation. > > Thanks, Art, for the explanation as to why I can't find it. > I take issue though with the assertion that no-one cares what it was at > index creation time. If you'd just spent 5 hours of your Saturday on Tech > Support advice altering the fill factor on indexes to try to get around a > bug, you'd like to be able to confirm that you'd actually done it properly! > > rgds > Neil > > You should be able to see if it was 'done properly' by examining one of the index pages. Just grab one of the leave pages and look at data Cheers Paul
>I take issue though with the assertion that no-one cares what it was at
>index creation time. If you'd just spent 5 hours of your Saturday on
Tech
>Support advice altering the fill factor on indexes to try to get around
a
>bug, you'd like to be able to confirm that you'd actually done it
properly!
If you had to rebuild your indices as part of your work, you could use
the oncheck -pT math I posted the other day, it should prove your
fillfactor worked, but over time it would not hold true. I did the math
on a larger table today and it was relatively accurate ... If I had to
rebuild my indices, it would be very accurate I think.
Norma Jean
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================
"Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com> wrote in message
news:mailman.217.1199680013.20610.informix-list@iiug.org...
>> If you had to rebuild your indices as part of your work, you could use
the oncheck -pT math I posted the other day, it should prove your
fillfactor worked, but over time it would not hold true. I did the math
on a larger table today and it was relatively accurate ... If I had to
rebuild my indices, it would be very accurate I think.
Thanks.
It's "maths" by the way .. ;-)
>It's "maths" by the way .. ;-) You may correct all my mistakes in Kansas sir, see you there :) ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================