General index maintenance
Whenever indexes are created and used, there is a potential for a decrease in I/O velocity due to maintenance, therefore, you should consider the maintenance cost of creating and using additional indexes. For radix indexes with MAINT(*IMMED) maintenance occurs when inserting, updating or deleting rows.
To reduce the maintenance of your indexes consider:
- Minimizing the number of indexes over a given table by creating composite (multiple column) key indexes such that an index can be used for multiple different situations.
- Dropping indexes during batch inserts, updates, and deletes
- Creating in parallel. Either create indexes, one at a time, in parallel using SMP or create multiple indexes simultaneously with multiple batch jobs
- Maintaining indexes in parallel using SMP
The goal of creating indexes is to improve query performance by providing statistics and implementation choices, while maintaining a reasonable balance on the number of indexes so as to limit maintenance overhead
Parent topic:
Binary radix indexes