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. SNIP Hi Pete, when you fragment a table, the indexes are always seperated from the data. If you want to avoid the large number of extents, then enter an appropriate extent size forb the tables. The initial extent size of your indexes will be calculated on the initial extent size of your table. That is the reason why you have as many index extents as you have data extents. I would not recommend to use a new fragmentation schema, but you should be more carefull when you create your tables at your new hardware. Don't forget to enter an extent size. At least you should enter the size of your currently allocated pages. The next extent size should be more than 30% of your first extent size. And don't forget to monitor the growth of your tables ! This is important when you reorganize your tables in the future. Bye Stefan Weideneder > 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? > > The system is mainly OLTP with a substantial amount of processing on > this table. > Informix is 7.23.UC1 > Kit is HPUX 10.20 - single processor. > > I am doing this as part of a migration to new hardware, so this > reorganisation would be part of the initial setup. > > I am keen to know which method would give the most efficient > performance. > > Any help much appreciated > > Pete