Database Reference
In-Depth Information
To understand the effect of this initialization parameter on the costing formula, it's useful to describe how the
query optimizer computes costs related to table accesses based on
index range scans.
An index range scan is an index lookup of several keys. As shown in Figure
9-4
, the following operations are
carried out:
1.
Access the root block of the index.
2.
Go through the branch blocks to locate the leaf block containing the first keys.
3.
For each key fulfilling the search criteria, do the following:
a.
Extract the rowid referencing the data block.
b.
Access the data block referenced by the rowid.
Figure 9-4.
Operations carried out during table accesses based on index range scans
The number of physical reads performed by an index range scan is equal to the number of branch blocks
accessed to locate the leaf block containing the first key (which is the
blevel
statistic), plus the number of leaf blocks
that are scanned (the
leaf_blocks
statistic multiplied by the selectivity of the operation), plus the number of data
blocks accessed via rowid (the
clustering_factor
statistic multiplied by the selectivity of the operation). This
gives you Formula 9-3, where, in addition, the correction applied by the
optimizer_index_cost_adj
initialization
parameter is taken into consideration.
Formula 9-3.
I/O cost of table accesses based on index range scans
)
optimizer _ index _ cos t _ adj
(
(
)
io _ cos t
≈
blevel
+
leaf _ blocks
+
clustering _ factor
⋅
selectivity
⋅
100
■
In formula 9-3, the same selectivity is used to compute the cost of the index access (operation 3a in figure
9-4
)
and the cost of the table access (operation 3b). In reality, the query optimizer might use two distinct selectivities for these
two distinct costs. This is necessary when only part of the filter is applied through the index access. for example, this
happens when an index is composed of three columns and the second one has no restriction.
Note
