- Disk resources (expressed in bytes)
Join indexes are stored in separate tables, often of high cardinality, and are therefore space-intensive.
- Creation and maintenance (expressed in elapsed time and CPU path per transaction)
Elapsed time has the advantages of conceptual simplicity and ease of measurement, but is not comparable across different system configurations and is not independent of other workloads running on a system.
With respect to join index creation, elapsed time is the time needed to create the index.
With respect to maintenance, elapsed time is the time needed to update a join index table beyond the time required to perform the update of the base table, where update refers to the operation performed by the following SQL statements:- DELETE
- INSERT
- MERGE
- UPDATE
CPU path per transaction is a sophisticated measure that is comparable across different system configurations. CPU path per transaction is difficult to measure without the proper tools and difficult to understand.
Whether creation and maintenance cost is measured as elapsed time or as CPU path per transaction, the same proportional relationship holds: the smaller the number, the better.
The total cost for a join index is the sum of its creation and maintenance costs.
Cost is expressed as follows: