Change next extentsize for indexes

Hi, Is there a way to change the next extent size for indexes similar like for tables? For tables the syntax is 'alter table <tablename> modify next size <newsize>' Regards! -POW

pow wrote: > Hi, > > Is there a way to change the next extent size for indexes similar like > for tables? For tables the syntax is 'alter table <tablename> modify > next size <newsize>' No. The extent sizes for indexes, are (IIRC) a proportion of the size of the table extents. So, if you change the table's next extent, you should also change the indexes' next size. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Only be modifying the extent size of the table to which they belong. Index extents are automatically calculated as a ratio of the table's row size and the index's key multiplied by the table's extent sizes. There is a feature request to be able to modify an index's extent sizes independent of the table's extents, and it may appear in a future release, but not at this time. If this is important to you, you should file your own feature request with a justification argument to strenghthen the existing requests. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Dec 8, 2009 at 8:54 AM, pow <perow@online.no> wrote: > Hi, > > Is there a way to change the next extent size for indexes similar like > for tables? For tables the syntax is 'alter table <tablename> modify > next size <newsize>' > > Regards! > > -POW > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On 8 Des, 15:10, Art Kagel <art.ka...@gmail.com> wrote: > Only be modifying the extent size of the table to which they belong.  Index > extents are automatically calculated as a ratio of the table's row size and > the index's key multiplied by the table's extent sizes.  There is a feature > request to be able to modify an index's extent sizes independent of the > table's extents, and it may appear in a future release, but not at this > time.  If this is important to you, you should file your own feature request > with a justification argument to strenghthen the existing requests. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (a...@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly.  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 Tue, Dec 8, 2009 at 8:54 AM, pow <pe...@online.no> wrote: > > Hi, > > > Is there a way to change the next extent size for indexes similar like > > for tables? For tables the syntax is 'alter table <tablename> modify > > next size <newsize>' > > > Regards! > > > -POW > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list– Skjul sitert tekst – > > – Vis sitert tekst – Hi, Ooops, I think I managed to hit the wrong button and answered just Art. Anyway.... So if I understand it right, by changing the table next size also the table indexes get the same extent size? We are running IDS 9.40 (planning to upgrade to 11.50 in a couple of months) and the "problemtable" is approx. 150.000.000 rows and according to oncheck there is only 5 extents left before we hit the max extentslimit. An oncheck -pt giromeld:giro_meldinger gives the below output AFTER changing next size to 2 GB (just done in PreProduction) : TBLspace Report for giromeld:efa.giro_meldinger Physical Address 2:52 Creation date 11/27/2009 06:10:34 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit- maps Maximum row size 97 Number of special columns 1 Number of keys 0 Number of extents 227 Current serial value 64018324 First extent size 8 Next extent size 1000000 Number of pages allocated 2490272 Number of pages used 2368786 Number of data pages 2368198 Number of rows 64001897 Partition partnum 2097201 Partition lockid 2097201 .. .. Index giro_meldinger0 fragment in DBspace giro_meld1 Physical Address 2:765 Creation date 11/27/2009 06:10:34 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit- maps Maximum row size 97 Number of special columns 0 Number of keys 1 Number of extents 217 Current serial value 1 First extent size 4 Next extent size 134020 Number of pages allocated 819936 Number of pages used 798428 Number of data pages 0 Number of rows 0 Partition partnum 2097202 Partition lockid 2097201 .. .. Index giro_meldinger3 fragment in DBspace giro_meld1 Physical Address 2:766 Creation date 11/27/2009 06:10:34 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit- maps Maximum row size 97 Number of special columns 0 Number of keys 1 Number of extents 195 Current serial value 1 First extent size 4 Next extent size 140206 Number of pages allocated 311232 Number of pages used 309082 Number of data pages 0 Number of rows 0 Partition partnum 2097203 Partition lockid 2097201 .. .. Index giro_meldinger7 fragment in DBspace giro_meld1 Physical Address 2:767 Creation date 11/27/2009 06:10:34 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit- maps Maximum row size 97 Number of special columns 0 Number of keys 1 Number of extents 225 Current serial value 1 First extent size 4 Next extent size 175257 Number of pages allocated 1114064 Number of pages used 1066713 Number of data pages 0 Number of rows 0 Partition partnum 2097204 Partition lockid 2097201 .. .. Index giro_meldinger8 fragment in DBspace giro_meld1 Physical Address 2:768 Creation date 11/27/2009 06:10:34 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit- maps Maximum row size 97 Number of special columns 0 Number of keys 1 Number of extents 225 Current serial value 1 First extent size 4 Next extent size 140206 Number of pages allocated 1114064 Number of pages used 1100525 Number of data pages 0 Number of rows 0 Partition partnum 2097205 Partition lockid 2097201 .. .. Regards! -POW

Not quite. I said the index extent is calculated from the table's extent not that it is the same! Reread the reply. Here's an example: I can't see your table's index key sizes, but let's make assumptions for simplicity, your rowsize is 97 bytes, plus the slot table entry for each row that's 99 bytes, make it 100 for simplicity. Say one of your keys is 8 bytes, plus 4 bytes for the rowid stored for each row makes 12. The ratio of the key size to the row size is 12:100 or 1:8.333. You have specified ~2GB next extent size or 1,000,000 pages. so: 1000000 / 8.333 = 120004 That means that the page size for our hypothetical index would be 120,004 pages. Your indexes have been set at 140,000 pages roughly, so I'm guessing that the first key is about 10 bytes. FYI, you should reorganize this table soon. BTW, the oncheck report shows that the table has just over 64 million rows not 150 million: Number of rows 64001897 Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Dec 8, 2009 at 9:44 AM, pow <perow@online.no> wrote: > On 8 Des, 15:10, Art Kagel <art.ka...@gmail.com> wrote: > > Only be modifying the extent size of the table to which they belong. > Index > > extents are automatically calculated as a ratio of the table's row size > and > > the index's key multiplied by the table's extent sizes. There is a > feature > > request to be able to modify an index's extent sizes independent of the > > table's extents, and it may appear in a future release, but not at this > > time. If this is important to you, you should file your own feature > request > > with a justification argument to strenghthen the existing requests. > > > > Art > > > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (a...@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. 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 Tue, Dec 8, 2009 at 8:54 AM, pow <pe...@online.no> wrote: > > > Hi, > > > > > Is there a way to change the next extent size for indexes similar like > > > for tables? For tables the syntax is 'alter table <tablename> modify > > > next size <newsize>' > > > > > Regards! > > > > > -POW > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list– Skjul sitert tekst > – > > > > – Vis sitert tekst – > > Hi, > > Ooops, I think I managed to hit the wrong button and answered just > Art. Anyway.... So if I understand it right, by changing the table > next size also the table indexes get the same extent size? We are > running IDS 9.40 (planning to upgrade to 11.50 in a couple of months) > and the "problemtable" is approx. 150.000.000 rows and according to > oncheck there is only 5 extents left before we hit the max > extentslimit. An oncheck -pt giromeld:giro_meldinger gives the below > output AFTER changing next size to 2 GB (just done in PreProduction) : > > TBLspace Report for giromeld:efa.giro_meldinger > > Physical Address 2:52 > Creation date 11/27/2009 06:10:34 > TBLspace Flags 901 Page Locking > TBLspace contains > VARCHARS > TBLspace use 4 bit bit- > maps > Maximum row size 97 > Number of special columns 1 > Number of keys 0 > Number of extents 227 > Current serial value 64018324 > First extent size 8 > Next extent size 1000000 > Number of pages allocated 2490272 > Number of pages used 2368786 > Number of data pages 2368198 > Number of rows 64001897 > Partition partnum 2097201 > Partition lockid 2097201 > > .. > .. > Index giro_meldinger0 fragment in DBspace giro_meld1 > > Physical Address 2:765 > Creation date 11/27/2009 06:10:34 > TBLspace Flags 801 Page Locking > TBLspace use 4 bit bit- > maps > Maximum row size 97 > Number of special columns 0 > Number of keys 1 > Number of extents 217 > Current serial value 1 > First extent size 4 > Next extent size 134020 > Number of pages allocated 819936 > Number of pages used 798428 > Number of data pages 0 > Number of rows 0 > Partition partnum 2097202 > Partition lockid 2097201 > .. > .. > Index giro_meldinger3 fragment in DBspace giro_meld1 > > Physical Address 2:766 > Creation date 11/27/2009 06:10:34 > TBLspace Flags 801 Page Locking > TBLspace use 4 bit bit- > maps > Maximum row size 97 > Number of special columns 0 > Number of keys 1 > Number of extents 195 > Current serial value 1 > First extent size 4 > Next extent size 140206 > Number of pages allocated 311232 > Number of pages used 309082 > Number of data pages 0 > Number of rows 0 > Partition partnum 2097203 > Partition lockid 2097201 > .. > .. > Index giro_meldinger7 fragment in DBspace giro_meld1 > > Physical Address 2:767 > Creation date 11/27/2009 06:10:34 > TBLspace Flags 801 Page Locking > TBLspace use 4 bit bit- > maps > Maximum row size 97 > Number of special columns 0 > Number of keys 1 > Number of extents 225 > Current serial value 1 > First extent size 4 > Next extent size 175257 > Number of pages allocated 1114064 > Number of pages used 1066713 > Number of data pages 0