SEO

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....