When clustering is high, large I/O is effective. As the cluster ratios decline, effectiveness of large I/O drops rapidly. To refine I/O estimates, the optimizer uses a set of cluster ratios:
For a table, the data page cluster ratio measures the packing and sequencing of pages on extents.
For an index, the data page cluster ratio measures the effectiveness of large I/O for accessing the table using this index.
The index page cluster ratio measures the packing and sequencing of leaf-level index pages on index extents.
The data row cluster ratio, another cluster ratio used
by query optimization, is used to cost the number of data pages
that need to be accessed during scans using a particular index.
It is not used in large I/O costing.
optdiag displays the cluster ratios for tables and indexes.
