Re: "index size" for a particular table by querying the sysstem tables

Posted in 2004

DL Redden — Sun, 10 Oct 2004 12:04:51 -0700 (PDT) — source: Usenet: comp.databases.informix

--0-1257991893-1097435091=:6006 Content-Type: text/plain; charset=us-ascii I appologize June. I wasn't paying enough attention. Obnoxio The Chav <obnoxio@serendipita.com> wrote: DL Redden said: > Jane, Me Tarzan, she June. > "June C. Hunt" wrote: > On Thu, 07 Oct 2004 13:52:20 -0500, REBELLO, Rulesh Felix > wrote: >> Hello Group: >> >> Can I find the "index size" for a particular table by querying the >> sysstem tables .. ..??? >> >> It is the silimar outout which we get in the dbschema utility fo > the ifo >> in the .sql file of a dbexport. > > I might only be able to help a small bit. The following was posted to > c.d.i. by Rajib Sarkar of IBM back on 2002-06-10: > > Step 1: > ========= > select sum(c.collength) > from syscolumns c, sysindexes i > where i.tabid = c.tabid > and i.tabid = > and (c.colno = ABS(i.part1) or > c.colno = ABS(i.part2) or > c.colno = ABS(i.part3) or > c.colno = ABS(i.part4) or > c.colno = ABS(i.part5) or > c.colno = ABS(i.part6) or > c.colno = ABS(i.part7) or > c.colno = ABS(i.part8) or > c.colno = ABS(i.part9) or > c.colno = ABS(i.part10) or > c.colno = ABS(i.part11) or > c.colno = ABS(i.part12) or > c.colno = ABS(i.part13) or > c.colno = ABS(i.part14) or > c.colno = ABS(i.part15) or > c.colno = ABS(i.part16)); > > This will just give you the width of the index, now you would require to > add the overhead to it, and the formula would be: > Step 2 > ========= > select count(*) from sysindexes where tabid = ;> > index length = ( + 4 * ) * 3/2; > > I've tested this formula against IDS 9.20.UC3 and get the index size > that I expect. I've also tested this formula against IDS 9.40.FC2 and > it does not come out to be the value I would expect. I'm not sure if > the difference is due entirely to the version of IDS, in some portion > to the 32 versus 64-bit difference, a combination of the two, or > something completely different. I've spent some time looking at the > Administrator's Guide (Art Kagel referenced that in a post that dealt > with this same issue) and the Performance Guide (the Admin Guide > references the Performance Guide), but haven't found an answer yet. > > I will continue to look for a formula that will work with IDS 9.4 (I > can be stubborn that way), but would be happy to end my search if > anyone else cares to post the correct answer. If not, I'll post what > I find if and when I get a chance to get back to this.... > > -- > June Hunt > > P.S. There appears to have been a minor glitch with the list... Better > now? > > > --------------------------------- > Do you Yahoo!? > Yahoo! Mail - You care about security. So do we. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco --------------------------------- Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. --0-1257991893-1097435091=:6006 Content-Type: text/html; charset=us-ascii <DIV>I appologize June. I wasn't paying enough attention. Obnoxio The Chav <obnoxio@serendipita.com> wrote: <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"> DL Redden said: > Jane, Me Tarzan, she June. > "June C. Hunt" <JUNE.C.HUNT@GMAIL.COM>wrote: > On Thu, 07 Oct 2004 13:52:20 -0500, REBELLO, Rulesh Felix > wrote: >> Hello Group: >> >> Can I find the "index size" for a particular table by querying the >> sysstem tables .. ..??? >> >> It is the silimar outout which we get in the dbschema utility fo > the ifo >> in the .sql file of a dbexport. > > I might only be able to help a small bit. The following was posted to > c.d.i. by Rajib Sarkar of IBM back on 2002-06-10: > > Step 1: > ========= > select sum(c.collength) > from syscolumns c, sysindexes i > where i.tabid = c.tabid > and i.tabid = > and (c.colno = ABS(i.part1) or > c.colno = ABS(i.part2) or > c.colno = ABS(i.part3) or > c.colno = ABS(i.part4) or > c.colno = ABS(i.part5) or > c.colno = ABS(i.part6) or > c.colno = ABS(i.part7) or > c.colno = ABS(i.part8) or > c.colno = ABS(i.part9) or > c.colno = ABS(i.part10) or > c.colno = ABS(i.part11) or > c.colno = ABS(i.part12) or > c.colno = ABS(i.part13) or > c.colno = ABS(i.part14) or > c.colno = ABS(i.part15) or > c.colno = ABS(i.part16)); > > This will just give you the width of the index, now you would require to > add the overhead to it, and the formula would be: > Step 2 > ========= > select count(*) from sysindexes where tabid = ; > > index length = ( + 4 * ) * 3/2; > > I've tested this formula against IDS 9.20.UC3 and get the index size > that I expect. I've also tested this formula against IDS 9.40.FC2 and > it does not come out to be the value I would expect. I'm not sure if > the difference is due entirely to the version of IDS, in some portion > to the 32 versus 64-bit difference, a combination of the two, or > something completely different. I've spent some time looking at the > Administrator's Guide (Art Kagel referenced that in a post that dealt > with this same issue) and the Performance Guide (the Admin Guide > references the Performance Guide), but haven't found an answer yet. > > I will continue to look for a formula that will work with IDS 9.4 (I > can be stubborn that way), but would be happy to end my search if > anyone else cares to post the correct answer. If not, I'll post what > I find if and when I get a chance to get back to this.... > > -- > June Hunt > > P.S. There appears to have been a minor glitch with the list... Better > now? > > > --------------------------------- > Do you Yahoo!? > Yahoo! Mail - You care about security. So do we. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco </BLOCKQUOTE></DIV> <hr size=1>Do you Yahoo!? <a href="http://us.rd.yahoo.com/mail_us/taglines/virus/*http://promotions.yahoo.com/new_mail/static/protection.html">Yahoo! Mail</a> - Helps protect you from nasty viruses. --0-1257991893-1097435091=:6006-- sending to informix-list