Re: Question on Extent Size

There are two options.... Fragmented Table : First extent size = (no. of rows * rowsize)/# of fragments. Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments this way you only need to rebuild the table once every quarter. Non Fragmented table : First extent size = (no. of rows * rowsize) Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. Here is a sql for obtaining the extent report. select t.tabname,count(*) n_ext from sysmaster:sysextents e, <"databasename">:systables t where e.tabname = t.tabname and dbsname = <"database name"> and t.tabname not matches 'sys*' group by 1 having count(*) > 1 order by 2 desc; EOF Hope it helps. -Abraham --- KalpanaPai <kalpanapai@hotmail.com> wrote: > Hi All > > We are running informix 9.21 on Solaris, the page > size is 2k . The db > was setup by prev DBA, as i am new to Informix (from > oracle) I have > few queries on extent size. > > We have around 100 tables , every table has created > with default > extent size (16k and next 16k). > > Some of the tables are showing extent size like > > table1 : 1208787 next ext 120878 > table2: 2472 next 247 > 56723 next 5672 etc, > Some small tables shows still 16k and next extent > 16 , > > But when i run the query to find the no of extents > for each table > still they show les than 8 extetns some of them even > 1 or 2 extents. > > 1. Is above extents size are ok or will it affect > any queries? > 2. Do they have to be dropped and recreated? > 3. The tables with what extent size needs to be > recreated? > 3. If i need to recreate , what is the first and > next extent size i > should give? > > Any suggestions, guidelines highly appreciated. > > Thanks in Advance > Kalpana __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com sending to informix-list

Hi Abraham Thanks very much for your reply. As we are setting up a new database Server, i thought if i need to recreate any of the tables of having more extents. When i run the same query it does it return any rows, it means all the tables are having single extent correct me if i am wrong. And also if i run the dbschema and oncheck the following is the result . with dbschema for one table which is everyday growing table extent size 1208787 next size 120878 with oncheck foll is the output TBLspace Report for vam2:informix.asset Physical Address 3deb94 Creation date 09/05/2003 12:45:36 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 24249 Number of special columns 30 Number of keys 0 Number of extents 1 Current serial value 189122 First extent size 604393 Next extent size 60439 Number of pages allocated 565682 Number of pages used 14737 Number of data pages 14726 Number of rows 51347 Partition partnum 3146009 Partition lockid 3146009 Extents Logical Page Physical Page Size 0 3491a9 565682 Could anyone suggest me with ref to the above output, do i need recreate such tables? If so what is the allowable growth of extent at present it is showing 1208787 next size 120878 . If i get the approximate extent size i can check rest of the tables and recreate them in the new server. Also for first and next extent size cal ,rowsize you mean (Maximum row size )(24249) to be considered? Please advise me which are the other areas i need to consider, as i am importing the whole db to new server dbexport/dbimport. Any suggestions much appreciated Thanks Kalpana Abraham Kirubakaran <bull_informix@yahoo.com> wrote in message news:<bjqtle$so2$1@terabinaries.xmission.com>... > There are two options.... > Fragmented Table : > First extent size = (no. of rows * rowsize)/# of > fragments. > Next extent size = ( .01* First extent size) > Or to be more precise > = ((no of rows expected per month > *4)*rowsize)/#of fragments > this way you only need to rebuild the table once every > quarter. > > Non Fragmented table : > First extent size = (no. of rows * rowsize) > Next extent size = ( .01* First extent size) > Or to be more precise > = ((no of rows expected per month > *4)*rowsize)/#of fragments > > > As a general rule, make sure you don't exceed more > than 8extents per fragment for a table, this helps > performance. > Here is a sql for obtaining the extent report. > > > select t.tabname,count(*) n_ext > from sysmaster:sysextents e, <"database> name">:systables t > where e.tabname = t.tabname > and dbsname = <"database name"> > and t.tabname not matches 'sys*' > group by 1 > having count(*) > 1 > order by 2 desc; > EOF > > Hope it helps. > > -Abraham > --- KalpanaPai <kalpanapai@hotmail.com> wrote: > > Hi All > > > > We are running informix 9.21 on Solaris, the page > > size is 2k . The db > > was setup by prev DBA, as i am new to Informix (from > > oracle) I have > > few queries on extent size. > > > > We have around 100 tables , every table has created > > with default > > extent size (16k and next 16k). > > > > Some of the tables are showing extent size like > > > > table1 : 1208787 next ext 120878 > > table2: 2472 next 247 > > 56723 next 5672 etc, > > Some small tables shows still 16k and next extent > > 16 , > > > > But when i run the query to find the no of extents > > for each table > > still they show les than 8 extetns some of them even > > 1 or 2 extents. > > > > 1. Is above extents size are ok or will it affect > > any queries? > > 2. Do they have to be dropped and recreated? > > 3. The tables with what extent size needs to be > > recreated? > > 3. If i need to recreate , what is the first and > > next extent size i > > should give? > > > > Any suggestions, guidelines highly appreciated. > > > > Thanks in Advance > > Kalpana > > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > sending to informix-list