Sizing an index

[lawsonb]

I am new to Informix - so this might sound like a daft question - but if I know the number of rows in a table - how would I go about working out what size the index will be before I build it in Megabytes? Is their a simple forumla I could use?

 

[vpshriyan]

Hi,

A rough calculation would base on the formula below:
Number of rows * column size

You may use multiple columns for composite indices. Hence, each column size need to be added up to arrive at a approximate index data page size. A SQL statement below gathers information from the the backend database and calculates size accordingly for the specified table and it's columns.

Please note that the table name and column(s) need to be provided for this SQL to function properly. The result is given out in Bytes size and it could be a round figure since index page overheads are not taken into consideration.

set isolation to dirty read;

select tabname,colname,round(collength/256)+mod(collength,256) collength
from syscolumns a, systables b
where a.tabid=b.tabid and
tabname='test_table' and colname in ('fld1','fld2','fld3')
into temp x with no log;

select * from x;

select sum(ti_nrows * collength) Bytes
from sysmaster:systabinfo a, sysmaster:systabnames b, systables c, x
where a.ti_partnum=b.partnum and b.tabname=c.tabname
and c.tabname=x.tabname ;

Regards,
Shriyan
It's better to understand a little than to misunderstand a lot.