Unix Technical Forum - View Single Post - Re: "index size" for a particular table by querying the sysstem tables

#1 (**permalink**) 04-20-2008, 07:52 AM

--0-683474517-1097354829=:99715
Content-Type: text/plain; charset=us-ascii

Jane,

Without doing any work or research on this I wonder if the difference is in the 4 byte overhead for each index. This theory stems from the large chunk capabilities of 9.4. I have no basis for this theory it was just a passing thought.

"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.
--0-683474517-1097354829=:99715
Content-Type: text/html; charset=us-ascii

<DIV>Jane,</DIV>
<DIV> </DIV>
<DIV>Without doing any work or research on this I wonder if the difference is in the 4 byte overhead for each index. This theory stems from the large chunk capabilities of 9.4. I have no basis for this theory it was just a passing thought.</DIV>
<DIV> "June C. Hunt" <june.c.hunt@gmail.com> wrote:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">On Thu, 07 Oct 2004 13:52:20 -0500, REBELLO, Rulesh Felix <RREBELLO@FSL.ORG.JM>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 = <YOUR 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 = <TABID>; index length = (<RESULT 1 Step of> + 4 * <RESULT Step of 2>) * 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? </BLOCKQUOTE>
<hr size=1>Do you Yahoo!? 
<a href="http://us.rd.yahoo.com/mail_us/taglines/security/*http://promotions.yahoo.com/new_mail/static/protection.html">Yahoo! Mail</a> - You care about security. So do we.
--0-683474517-1097354829=:99715--
sending to informix-list

Thread: Re: "index size" for a particular table by querying the sysstem tables View Single Post

Thread: Re: "index size" for a particular table by querying the sysstem tables
View Single Post