The following generalizations are made from these test results:
- Maintenance costs for aggregate join indexes are much lower than maintenance costs for comparable simple join indexes.
- Maintenance costs for no join index are lower than maintenance costs for foreign and ad hoc join indexes.
For example, maintenance costs for a 2-table in-place aggregate join index are 1.1 to 2.5 times greater than maintenance costs computed for the base table without a join index.
- Maintenance costs vary with the type of join index defined.
For example, with an in-place aggregate join index, the higher the number of hits per data block, the less the overhead incurred. This effect is marginal for inserts, for which maintenance costs are already minimal.
However, for an in-place simple join index at 1 hit per data block, deletes cost 3.4 times more and updates cost 4.5 times more than the case where no join index is defined.
In contrast to the in-place aggregate join index, maintenance costs worsen as the number of hits per data block increase, as indicated by the following graph of CPU path per transaction as a function of number of hits per data block.
In the case of an in-place simple join index, inserts cost less than 1.4 times the maintenance required when no join index is defined.
In all cases, inserts are never more than four times more expensive than the maintenance cost when no join index is defined.