The size of join index rows is another important factor to consider when evaluating the maintenance overhead of a join index. Given a constant disk space, as the size of the rows increases, the number of rows decreases, while the number of data blocks remains the same. The result is that the number of hits per data block decreases.
Maintenance Costs in Terms of CPU Path Per Transaction
CPU path per transaction times for updates increase steadily as a function of increased row size, while the number of transactions, or qualified rows, decreases drastically. This can be seen in the following graph, where CPU path per transaction is presented as a function of percentage of rows touched during an update operation.
Maintenance Costs in Terms of Other Measures
The following table indicates the trends in performance cost of join index maintenance as a function of increasing row size in terms of various performance measures:
Performance Measure | Effect as a Function of Increasing Row Size |
---|---|
Elapsed time to insert, delete, or update join index rows. | Decreases |
I/Os per insert, delete, or update transaction | Increases |
Number of point-to-point BYNET insert, delete, or update transactions | Increases |
Summary
Summarizing these results, each join index maintenance transaction performs more work as a function of increased row size as measured by the following list of variables.
- CPU path per transaction
- I/Os per transaction
- BYNET transactions per transaction