Index extent size

Hello, I have been reading the Manual about how Informix calculates the index extent size. Index Extent size = (index key size/table row size) * table extent size and Index Next Size = (index key size/table row size) * table extent size (Performance Guide for IDS 2000 Page 7-4) I have a problem when creating indexes on wide tables that have varchar columns. Lets suppose that that I create a table as Create table t1 ( col1 serial, description varchar(255,20) ) in dataspace extent size 2000 next size 400 lock mode row; create index t1_idx1 on t1(col1)in indexspace; Informix Creates the table with First Extent size: 500 Pages, Next extent size: 100 Pages And the index with First extent size: 13 pages Next extent size: 4 pages If I expect 90% of the data to have a description of 20 chars or less and allocate the extent sizes accordingly; the extent sizes allocated by Informix are too small and causes the index to have multiple extents. After executing 3,500 times INSERT INTO T1 VALUES(0,"TESTING 1") The space allocations reported by oncheck -pT are: Table: Number of pages allocated: 500, Number of pages used: 28 Extents: 1 Index: Number of pages allocated: 17 Number of pages used: 14 Extents: 2 !! Have any of you had the same problem? Do you have suggestions? TIA Tino PS. IDS 9.20 AIX 4.3

Tino Cremidis wrote: > > Hello, > > I have been reading the Manual about how Informix > calculates the index extent size. > > Index Extent size = (index key size/table row size) * table extent size > and > Index Next Size = (index key size/table row size) * table extent size > (Performance Guide for IDS 2000 Page 7-4) Correct. > I have a problem when creating indexes on wide tables > that have varchar columns. OK ... > Lets suppose that that I create a table as > > Create table t1 ( > col1 serial, > description varchar(255,20) > ) in dataspace extent size 2000 next size 400 > lock mode row;> > create index t1_idx1 on t1(col1)in indexspace;> > Informix Creates the table with > First Extent size: 500 Pages, > Next extent size: 100 Pages > > And the index with > First extent size: 13 pages > Next extent size: 4 pages > > If I expect 90% of the data to have a > description of 20 chars or less and allocate > the extent sizes accordingly; the extent sizes > allocated by Informix are too small > and causes the index to have multiple extents. AH! > After executing 3,500 times > INSERT INTO T1 VALUES(0,"TESTING 1")> > The space allocations reported by > oncheck -pT are:> > Table: Number of pages allocated: 500, > Number of pages used: 28 > Extents: 1 > > Index: Number of pages allocated: 17 > Number of pages used: 14 > Extents: 2 !! So ... what's the beef? The second extent? Not a performance problem. The fact that the extent size for the index probably should be about 50% larger? Put in a feature request for EXTENT SIZE and NEXT SIZE options in CREATE INDEX for detached indexes, I have. Enough voices and maybe we'll get it, until then reorg the indexes using ALTER FRAGMENT ON INDEX ... INIT IN ...; periodically. > Have any of you had the same problem? Likely some do, but since I diligently avoid VARCHAR columns I don't. > Do you have suggestions? Made them above. Art S. Kagel

You could perform an alter table tablename modify next size nnnnn before creating your indices. Clifton Bean In article <397F4489.EF75579D@bloomberg.net>, kagel@bloomberg.net wrote: > Tino Cremidis wrote: > > > > Hello, > > > > I have been reading the Manual about how Informix > > calculates the index extent size. > > > > Index Extent size = (index key size/table row size) * table extent size > > and > > Index Next Size = (index key size/table row size) * table extent size > > (Performance Guide for IDS 2000 Page 7-4) > > Correct. > > > I have a problem when creating indexes on wide tables > > that have varchar columns. > > OK ... > > > Lets suppose that that I create a table as > > > > Create table t1 ( > > col1 serial, > > description varchar(255,20) > > ) in dataspace extent size 2000 next size 400 > > lock mode row;> > > > create index t1_idx1 on t1(col1)in indexspace;> > > > Informix Creates the table with > > First Extent size: 500 Pages, > > Next extent size: 100 Pages > > > > And the index with > > First extent size: 13 pages > > Next extent size: 4 pages > > > > If I expect 90% of the data to have a > > description of 20 chars or less and allocate > > the extent sizes accordingly; the extent sizes > > allocated by Informix are too small > > and causes the index to have multiple extents. > > AH! > > > After executing 3,500 times > > INSERT INTO T1 VALUES(0,"TESTING 1")> > > > The space allocations reported by > > oncheck -pT are:> > > > Table: Number of pages allocated: 500, > > Number of pages used: 28 > > Extents: 1 > > > > Index: Number of pages allocated: 17 > > Number of pages used: 14 > > Extents: 2 !! > > So ... what's the beef? The second extent? Not a performance > problem. The fact that the extent size for the index probably should > be about 50% larger? Put in a feature request for EXTENT SIZE and > NEXT SIZE options in CREATE INDEX for detached indexes, I have. > Enough voices and maybe we'll get it, until then reorg the indexes > using ALTER FRAGMENT ON INDEX ... INIT IN ...; periodically. > > > Have any of you had the same problem? > > Likely some do, but since I diligently avoid VARCHAR columns I don't. > > > Do you have suggestions? > > Made them above. > > Art S. Kagel > Sent via Deja.com http://www.deja.com/ Before you buy.