Calculate index size

Hi, I want to calculate the index size on my own and need some help. I have the following table definition: fld1 char(8) fld2 decimal(2,0) fld3 char(1) The primary key consists of fld1 and fld2. I have the following index definitions according to sysindexes: Idx1 fld1, fld2 Unique Idx2 fld2 Duplicates Idx3 fld1 Duplicates dbschema -d dbase -t table gives me the following values: Row size: 11 (this value is clear to me) Nr of cols: 3 (also clear ;-) Index size: 48 (Why?) I looked up some chapters in the SQL Reference Guide but that didn't help me in this case. Maybe someone of you can shed some light on this problem... TIA for your help! Many regards, Stephan. -- Stephan Stresing MR Informatik GmbH mailto:st@mr-informatik.de Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

OK the calculation is: (SUM(keysizes) + (numindexes * 4)) * 1.5 which is: ( (10 + 2 + 8) + (3 * 4)) * 1.5 = ( 20 * 12 ) * 1.5 = ( 32 ) * 1.5 = 48 This comes from: (total keysize * rowids) adjusted for index overhead. BTW just to confuse things all but the most recent versions of dbshema (and dbexport for that matter) incorrectly fail to count the keysize of any column declared DESCending in an index so your calculations may be greater than dbschema's (and more accurate) using this formula. This was reported to Informix as bug #111832 and has been fixed in the latest releases of 7.3x and maybe 7.24 as well. Myschema.ec gets it right, BTW, which is how I tracked down the bug (beating my head against the wall trying to get the calculation to match dbschema). Art S. Kagel Stephan Stresing wrote: > > Hi, > I want to calculate the index size on my own and need some help. > I have the following table definition: > > fld1 char(8) > fld2 decimal(2,0) > fld3 char(1) > > The primary key consists of fld1 and fld2. > > I have the following index definitions according to sysindexes: > > Idx1 fld1, fld2 Unique > Idx2 fld2 Duplicates > Idx3 fld1 Duplicates > > dbschema -d dbase -t table gives me the following values:> > Row size: 11 (this value is clear to me) > Nr of cols: 3 (also clear ;-) > Index size: 48 (Why?) > > I looked up some chapters in the SQL Reference Guide but that didn't > help me in this case. > Maybe someone of you can shed some light on this problem... > > TIA for your help! > > Many regards, > Stephan. > > -- > Stephan Stresing > > MR Informatik GmbH > mailto:st@mr-informatik.de > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.

Why the 1.5 multiplier???? John Carlson Informix DBA WHSmith USA Art S. Kagel wrote: > > OK the calculation is: > > (SUM(keysizes) + (numindexes * 4)) * 1.5 > > which is: > ( (10 + 2 + 8) + (3 * 4)) * 1.5 = > ( 20 * 12 ) * 1.5 = > ( 32 ) * 1.5 = 48 > > This comes from: > (total keysize * rowids) adjusted for index overhead. > > BTW just to confuse things all but the most recent versions of dbshema > (and dbexport for that matter) incorrectly fail to count the keysize of > any column declared DESCending in an index so your calculations may be > greater than dbschema's (and more accurate) using this formula. This was > reported to Informix as bug #111832 and has been fixed in the latest > releases of 7.3x and maybe 7.24 as well. Myschema.ec gets it right, BTW, > which is how I tracked down the bug (beating my head against the wall > trying to get the calculation to match dbschema). > > Art S. Kagel > > Stephan Stresing wrote: > > > > Hi, > > I want to calculate the index size on my own and need some help. > > I have the following table definition: > > > > fld1 char(8) > > fld2 decimal(2,0) > > fld3 char(1) > > > > The primary key consists of fld1 and fld2. > > > > I have the following index definitions according to sysindexes: > > > > Idx1 fld1, fld2 Unique > > Idx2 fld2 Duplicates > > Idx3 fld1 Duplicates > > > > dbschema -d dbase -t table gives me the following values:> > > > Row size: 11 (this value is clear to me) > > Nr of cols: 3 (also clear ;-) > > Index size: 48 (Why?) > > > > I looked up some chapters in the SQL Reference Guide but that didn't > > help me in this case. > > Maybe someone of you can shed some light on this problem... > > > > TIA for your help! > > > > Many regards, > > Stephan. > > > > -- > > Stephan Stresing > > > > MR Informatik GmbH > > mailto:st@mr-informatik.de > > > > Sent via Deja.com http://www.deja.com/ > > Share what you know. Learn what you don't.

Two reasons: 1) because it works ;-) 2) Seriously, because the index overhead is approximately 1/2 the keysize. This allows space for btree nodes, wasted space, etc. It's in the admin guide somewhere. I usually find it after about 2 hours of poking when I need it. Art S. Kagel "Carlson@WHSmith" wrote: > > Why the 1.5 multiplier???? > > John Carlson > Informix DBA > WHSmith USA > > Art S. Kagel wrote: > > > > OK the calculation is: > > > > (SUM(keysizes) + (numindexes * 4)) * 1.5 > > > > which is: > > ( (10 + 2 + 8) + (3 * 4)) * 1.5 = > > ( 20 * 12 ) * 1.5 = > > ( 32 ) * 1.5 = 48 > > > > This comes from: > > (total keysize * rowids) adjusted for index overhead. > > > > BTW just to confuse things all but the most recent versions of dbshema > > (and dbexport for that matter) incorrectly fail to count the keysize of > > any column declared DESCending in an index so your calculations may be > > greater than dbschema's (and more accurate) using this formula. This was > > reported to Informix as bug #111832 and has been fixed in the latest > > releases of 7.3x and maybe 7.24 as well. Myschema.ec gets it right, BTW, > > which is how I tracked down the bug (beating my head against the wall > > trying to get the calculation to match dbschema). > > > > Art S. Kagel > > > > Stephan Stresing wrote: > > > > > > Hi, > > > I want to calculate the index size on my own and need some help. > > > I have the following table definition: > > > > > > fld1 char(8) > > > fld2 decimal(2,0) > > > fld3 char(1) > > > > > > The primary key consists of fld1 and fld2. > > > > > > I have the following index definitions according to sysindexes: > > > > > > Idx1 fld1, fld2 Unique > > > Idx2 fld2 Duplicates > > > Idx3 fld1 Duplicates > > > > > > dbschema -d dbase -t table gives me the following values:> > > > > > Row size: 11 (this value is clear to me) > > > Nr of cols: 3 (also clear ;-) > > > Index size: 48 (Why?) > > > > > > I looked up some chapters in the SQL Reference Guide but that didn't > > > help me in this case. > > > Maybe someone of you can shed some light on this problem... > > > > > > TIA for your help! > > > > > > Many regards, > > > Stephan. > > > > > > -- > > > Stephan Stresing > > > > > > MR Informatik GmbH > > > mailto:st@mr-informatik.de > > > > > > Sent via Deja.com http://www.deja.com/ > > > Share what you know. Learn what you don't.

In article <37E933CC.CD3E6696@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Two reasons: > >1) because it works ;-) >2) Seriously, because the index overhead is approximately 1/2 the keysize. >This allows space for btree nodes, wasted space, etc. It's in the admin guide >somewhere. I usually find it after about 2 hours of poking when I need it. > >Art S. Kagel > What about detached indexes, check the FAQ below. One up on Art, not bad start to the week. "See I do maintain the FAQ, I do!" <Boing> <Boing> <Boing> (Sound of Dave bouncing up and down with excitement) ..."Daves back" ..."Yeah, don't annoy him again, we've only jut fixed the damage after last time. It's ok guys, the bugs will be fixed and everything will running 10 times faster by the end of the week. Just leave Dave alone and try not to disturb him again..." PS Wrote a data conversion script to upgrade a site from a very old version of our product to the latest version and found missing functionality. I loved the long his face when I told a developer (It's ok, I find the bugs and you fix them! I work 9am-5pm an 8hr day and go home, you work 5pm to 9am and have things fixed by the time I return!). >"Carlson@WHSmith" wrote: >> >> Why the 1.5 multiplier???? >> >> John Carlson >> Informix DBA >> WHSmith USA >> >> Art S. Kagel wrote: >> > >> > OK the calculation is: >> > >> > (SUM(keysizes) + (numindexes * 4)) * 1.5 >> > >> > which is: >> > ( (10 + 2 + 8) + (3 * 4)) * 1.5 = >> > ( 20 * 12 ) * 1.5 = >> > ( 32 ) * 1.5 = 48 >> > >> > This comes from: >> > (total keysize * rowids) adjusted for index overhead. >> > >> > BTW just to confuse things all but the most recent versions of dbshema >> > (and dbexport for that matter) incorrectly fail to count the keysize of >> > any column declared DESCending in an index so your calculations may be >> > greater than dbschema's (and more accurate) using this formula. This was >> > reported to Informix as bug #111832 and has been fixed in the latest >> > releases of 7.3x and maybe 7.24 as well. Myschema.ec gets it right, BTW, >> > which is how I tracked down the bug (beating my head against the wall >> > trying to get the calculation to match dbschema). >> > >> > Art S. Kagel >> > >> > Stephan Stresing wrote: >> > > >> > > Hi, >> > > I want to calculate the index size on my own and need some help. >> > > I have the following table definition: >> > > >> > > fld1 char(8) >> > > fld2 decimal(2,0) >> > > fld3 char(1) >> > > >> > > The primary key consists of fld1 and fld2. >> > > >> > > I have the following index definitions according to sysindexes: >> > > >> > > Idx1 fld1, fld2 Unique >> > > Idx2 fld2 Duplicates >> > > Idx3 fld1 Duplicates >> > > >> > > dbschema -d dbase -t table gives me the following values:>> > > >> > > Row size: 11 (this value is clear to me) >> > > Nr of cols: 3 (also clear ;-) >> > > Index size: 48 (Why?) >> > > >> > > I looked up some chapters in the SQL Reference Guide but that didn't >> > > help me in this case. >> > > Maybe someone of you can shed some light on this problem... >> > > >> > > TIA for your help! >> > > >> > > Many regards, >> > > Stephan. >> > > >> > > -- >> > > Stephan Stresing >> > > >> > > MR Informatik GmbH >> > > mailto:st@mr-informatik.de >> > > >> > > Sent via Deja.com http://www.deja.com/ >> > > Share what you know. Learn what you don't. -- David Williams

Hi Art, thanks for your help and your explanations! Now I see much more clearly what is calculated in Informix internally and how I can adapt it to our needs. Thanks again, Stephan. In article <37E92532.7DF78099@bloomberg.net>, kagel@bloomberg.net wrote: > OK the calculation is: > > (SUM(keysizes) + (numindexes * 4)) * 1.5 > > which is: > ( (10 + 2 + 8) + (3 * 4)) * 1.5 = > ( 20 * 12 ) * 1.5 = > ( 32 ) * 1.5 = 48 > > This comes from: > (total keysize * rowids) adjusted for index overhead. > > BTW just to confuse things all but the most recent versions of dbshema > (and dbexport for that matter) incorrectly fail to count the keysize of > any column declared DESCending in an index so your calculations may be > greater than dbschema's (and more accurate) using this formula. This was > reported to Informix as bug #111832 and has been fixed in the latest > releases of 7.3x and maybe 7.24 as well. Myschema.ec gets it right, BTW, > which is how I tracked down the bug (beating my head against the wall > trying to get the calculation to match dbschema). > > Art S. Kagel > > Stephan Stresing wrote: > > > > Hi, > > I want to calculate the index size on my own and need some help. > > I have the following table definition: > > > > fld1 char(8) > > fld2 decimal(2,0) > > fld3 char(1) > > > > The primary key consists of fld1 and fld2. > > > > I have the following index definitions according to sysindexes: > > > > Idx1 fld1, fld2 Unique > > Idx2 fld2 Duplicates > > Idx3 fld1 Duplicates > > > > dbschema -d dbase -t table gives me the following values:> > > > Row size: 11 (this value is clear to me) > > Nr of cols: 3 (also clear ;-) > > Index size: 48 (Why?) > > > > I looked up some chapters in the SQL Reference Guide but that didn't > > help me in this case. > > Maybe someone of you can shed some light on this problem... > > > > TIA for your help! > > > > Many regards, > > Stephan. > > > > -- > > Stephan Stresing > > > > MR Informatik GmbH > > mailto:st@mr-informatik.de > > > > Sent via Deja.com http://www.deja.com/ > > Share what you know. Learn what you don't. > -- Stephan Stresing MR Informatik GmbH mailto:st@mr-informatik.de Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.