Re: Index Fragmentation strategy

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) 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