Re: Question on Extent Size

Hi Kalpana, What has happened is "extent concatenation" ...when Informix engine tries to allocate space for the table ( in extents) it first tries to find space (if available) right next to the current extent of the table ... it looks like for your table you are in pretty good shape ... Actually, after Online 5 version, the number of extents rule of thumb does not matter too much ..(although its good to have a clean table) ... but in your case you don't really require to do anything since your table is existing in physically contiguous space (even though the extent is big) .. HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi kalpanapai@hotmai l.com To: informix-list@iiug.org (KalpanaPai) cc: Sent by: Subject: Re: Question on Extent Size owner-informix-li st@iiug.org 09/11/2003 09:23 PM Please respond to kalpanapai 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 sending to informix-list sending to informix-list