Next Extent Size of an index

Hi all !!! Anyone have any idea how to find the next extent size of an index of a table? Informix Version : IBM Informix Dynamic Server Version 11.50.FC4 Operating System : AIX Version 6.1 (64 bits) Thank you very much to all!

The information is in the sysindices database catalog table. The column fextsize is the first extent and nextsize is the next extent size for the index. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 4, 2013 at 1:28 PM, JOSé LUIS CABRERA SACCO < jcabrera@anda.com.uy> wrote: > Hi all !!! > Anyone have any idea how to find the next extent size of an index of a > table? > > Informix Version : IBM Informix Dynamic Server Version 11.50.FC4 > Operating System : AIX Version 6.1 (64 bits) > > Thank you very much to all! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0401fa5d73b26304d71df312

Hello Art I can not find the columns in the table sysindices. Here I send you an example of the result of running "select * from sysindices" (from dbaccess): idxname i_mv_cuen_gr owner informix tabid 124 idxtype D clustered levels 4 leaves 639706.0000000 nunique 9540.000000000 clust 107791710.0000 nrows 0.00 indexkeys 11 [1], 12 [1], 9 [1] amid 1 amparam collation en_US.819 pagesize 4096 Another thing that does not specify: the table is fragmented. Any other suggestions? Thank you very much

Ahh, sorry, forgot you are on 11.50 which does not allow you to set the extent sizing for indexes directly. In that case, this is not recorded anywhere as it is calculated by the engine dynamically as the next extent size of the table that owns the index times the ratio of the key length to the maximum record length. So, if you have a table with a row that is 150 bytes wide and a nextsize of 500 pages an index with a keylength of 30 bytes will have a next extent size of 100 pages or 30/150 or 1/5 of the table's size. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 4, 2013 at 3:08 PM, JOSé LUIS CABRERA SACCO < jcabrera@anda.com.uy> wrote: > Hello Art > I can not find the columns in the table sysindices. > Here I send you an example of the result of running "select * from > sysindices" > (from dbaccess): > > idxname i_mv_cuen_gr > owner informix > tabid 124 > idxtype D > clustered > levels 4 > leaves 639706.0000000 > nunique 9540.000000000 > clust 107791710.0000 > nrows 0.00 > indexkeys 11 [1], 12 [1], 9 [1] > amid 1 > amparam > collation en_US.819 > pagesize 4096 > > Another thing that does not specify: the table is fragmented. > > Any other suggestions? > > Thank you very much > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d04016b490ed2f304d71f43cf

Hi José, In version 11.50, you cannot set the extent size for indexes. You have to move up to 11.70 where you can set the extent size for indexes in your CREATE INDEX statement. The extent sizes are calculated using the following formula: (sizes of the columns of the index)/rowsize* extent size of the table rounded up the minimum size of an extent which is 4 pages. Anyway, you can see the extent sizes in the output of the oncheck -pT command on your table. Ex: oncheck -pT stores:customer This will give all kinds of interesting information on the table and its indexes. Here is part of the output concerning the index 100_1 for the ciustomer table: ...... Index 100_1 fragment partition rootdbs in DBspace rootdbs Physical Address 1:33056 Creation date 11/19/2012 00:49:15 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 134 Number of special columns 0 Number of keys 1 Number of extents 1 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 4 _*First extent size 4 Next extent size 4 *_ Number of pages allocated 4 Number of pages used 2 Number of data pages 0 Number of rows 0 Partition partnum 1049679 Partition lockid 1049678 Extents Logical Page Physical Page Size Physical Pages 0 2:733 4 4 ..... Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 04/03/13 19:28, JOSé LUIS CABRERA SACCO a écrit : > Hi all !!! > Anyone have any idea how to find the next extent size of an index of a table? > > Informix Version : IBM Informix Dynamic Server Version 11.50.FC4 > Operating System : AIX Version 6.1 (64 bits) > > Thank you very much to all! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thank you very much !! Regards

Thank you very much ! Regards