Index Fragmentation strategy
Posted in 1998
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?
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