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

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 of Step 1> + 4 * <result of Step 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?

Hello All, this > select sum(c.collength) is wrong if you have decimals, datetimes, intervals or (n)varchars. i have a version for calculation it all data plus index size with a given # of rows and asumeing idx size needs to be multiplied 1.25 per traing manual. this lot works fine on V7. anyways it needed to be hacked for 9 (done so however am haven't checked the values returned properly yet. V 9 bitches about not be able to select from sysindexes it pukes with 999 ...) and before i forget please run on test only!! anyways if you want it please tell me where to send it to; it is a sql script of 321 lines. See you Superboer. piece of it: CREATE PROCEDURE "informix".calc_idx_size(collength SMALLINT, coltype INT) -- this procedure gets the encoded column length and type and converts -- it to the real length for storage on disk -- the columns with datatype decimal money datetime interval varchar -- and nvarchar are the ones that have to be converted because informix -- stores these values encoded in the system catalog. -- other datatypes have their length in the system catalog -- the length in bytes is returned in an integer. RETURNING INT; DEFINE ret_idx_size INT; -- when we have a decimal or money column in an index calculate -- it's size if the column does not allow nulls the type is the -- original type plus 256 IF (coltype = 8 or coltype = (8 + 256) OR coltype = 5 OR coltype = (5 + 256)) THEN -- corrected by Alan Stanford training manual is wrong --(1 + (((collength - (MOD(collength , 256))) /256 )/2)); LET ret_idx_size = (1 +(1+((collength-MOD(collength,256))/256)-MOD(collength,256))/2 +(1+(MOD(collength , 256)))/2 ); -- the formula to get the number of bytes on disk for -- decimal or money data types -- we are dealing with varchar or nvarchar calculate the size ELIF (coltype = 13 OR coltype = (13 + 256) OR coltype = 16 OR coltype = (16 + 256)) THEN coltype = 16 OR coltype = (16 + 256)) THEN IF (collength < 0) THEN LET ret_idx_size = (MOD((collength + 65536) , 256)); ELSE LET ret_idx_size = (MOD(collength , 256)); END IF -- formula to get the size for nvarchar or varchar -- now we are dealing with datetime or intervals ELIF (coltype = 10 OR coltype = (10 + 256) OR coltype = 14 OR coltype = (14 + 256)) THEN LET ret_idx_size = (1 + ROUND(((collength - (MOD(collength , 256))) /256 )/2)); -- the formula to calculate their sizes ELSE -- finally if none of the above is true we can take the length from the -- system catalog so a column without a decoded length LET ret_idx_size = collength; END IF -- return the found value RETURN ret_idx_size; END PROCEDURE; "June C. Hunt" <june.c.hunt@gmail.com> wrote in message news:<ybP9d.4571$l07.1742@twister.nyroc.rr.com>... > 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 of Step 1> + 4 * <result of Step 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....