Re: Index Usage Statistics
Drew (asimpson@matcom2.fcic.usda.gov) wrote:
: I am looking for a way to determine when an index has become
: so fragmented that it should be re-built.
: An application I am developing continually deletes and inserts
: rows into a large table. I don't want the program to recreate
: the index every time a large number of deletes and inserts have
: occurred, because of the time it takes to rebuild the index. I
: would like to have the program query the status of the index,
: and display a message when the index needs to be re-built.
: Thanks in advance,
: Drew
Conventional wisdom says that you may want to lower the fillfactor a bit
if you plan on doing a lot of inserts and deletes. This should start you
out with enough room to avoid costly index shuffling. It's only effective
at index creation, sort of like clustered indexes.
You may want to look at the output on oncheck -pT dbname:[owner].tablename.
You'll get a report that includes data like this:
Index Usage Report for index ix782_6 on crm2:informix.member
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 5 1872
2 5 31 926
3 157 28 983
----- -------- -------- ----------
Total 163 28 987
Index Usage Report for index xpkmember on crm2:informix.member
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 40 1544
2 40 113 550
----- -------- -------- ----------
Total 41 111 574
The "average free bytes" should give you some indication of how
densely packed (or how sparse) the index is. I don't think that's
as important as how many levels the index has, but others may
have better info.
QUESTION TO THE GROUP: how do **you** use this report?
Joe
--
---------------------------------------------------------------------------
Joe Lumbley(jlumbley@netcom.com) author of: "INFORMIX DBA Survival Guide"
Slaving away on "INFORMIX DEBUGGERS Survival Guide", available early 1998
from Prentice Hall/Informix Press. If you have debugging tips, tools, or
ideas to share, please send me some e-mail.
---------------------------------------------------------------------------