Re: Index Fragmentation strategy

Art S. Kagel wrote: > > Pete Smith wrote: > > > > Hello all > > > I am trying to determine the best method to resolve this situation: > > A table is fragmented by expression across 5 dbspaces. The table has 5 > > indexes which reside in the same spaces. From the output from oncheck > > -pe the table has nearly 800 extents! On closer inspection this was > > found to be due to the indexes having their own extents ( and almost as > > many as the table ). > > > To avoid this situation reoccuring I would like to separate the indexes > > from the data. > > My indecision lies in how best to do this. > > Should I : > > 1. create a dbspace for each index ( there are 4 indexes), > > 2. create a single, larger, dbspace and place all 4 indexes in it, > > 3. create a number of dbspaces ( say 4 or 5 ) and fragment the indexes > > across these spaces, or > > 4. do something else I haven't thought of? > > Because the index extent size is calculated using the ratio of key > length to row length whether the index is attached or detached > (actually an attached index on a fragmented table has actually just > been detached and fragmented on the same scheme and dbspaces as the > table itself since 7.20) ... I think it started with the first inofficial 7.0 version. > ...the ONLY WAY to keep the indexes from > generating lots of extents is to give each index or index fragment its > own dbspace so that all extents compress into a single extent per > chunk. > This will improve performance in general but may hurt performance if > the table and index grew in such a way that the index extents and the > data extents they reference exhibited a high degree of locality (ie > index pages near to data pages) and the extent size was not too large. > This is because caching and read-ahead, both by your disk/disk array > controllers and by Informix will be more likely to sweep up needed > pages together with the attached indexes. In general this gain is > outweighed by the advantages of the same kind of sweeps catching up > related index and data pages that you may also need, but sometimes not. > > Art S. Kagel Hi Art, I completely agree. But I think, if we have no idea of the first extent size of our table, what should be the size of the first chunks for the index dbspaces ? And if we would know the correct size for the index dbspace, then it must be possible to calculate the correct size for the table's first extent. I think, we are just talking about different terms (dbspace/extent). When the first extent becomes full, Informix will automatically add an additional extent. If you decided to have a seperate dbspace, then you must add a new chunk to the dbspace ( yet another extent ). Bye Stefan Weideneder