Adaptive Server stores two values for the density of column values:
The “Range cell density” measures the duplicate values only for range cells.
If there are any frequency cells for the column, they are eliminated from the computation for the range-cell density.
If there are only frequency cells for the column, and no range cells, the range-cell density is 0.
See “Understanding histogram output” for information on range and frequency cells.
The “Total density” measures the duplicate values for all columns, those represented by both range cells and frequency cells.
Using two separate values improves the optimizer’s estimates of the number of rows to be returned:
If a search argument matches the value of a frequency cell, the fraction of rows represented by the weight of the frequency cell will be returned.
If a search argument falls within a range cell, the range-cell density and the weight of the range cell are used to estimate the number of rows to be returned.
For joins, the optimizer bases its estimates on the average number of rows to be returned for each scan of the table, so the total density, which measures the average number of duplicates for all values in the column, provides the best estimate. The total density is also used for equality arguments when the value of the search argument is not known when the query is optimized.
See “Range and in-between selectivity values” for more information.
For indexes on multiple columns, the range-cell density and total density are stored for each prefix subset. In the sample output below for an index on titles (pub_id, type, pubdate), the density values decrease with each additional column considered.
Statistics for column: "pub_id"
Last update of column statistics: Feb 4 1998 12:58PM
Range cell density: 0.0335391029690461
Total density: 0.0335470400000000
Statistics for column group: "pub_id", "type"
Last update of column statistics: Feb 4 1998 12:58PM
Range cell density: 0.0039044009265108
Total density: 0.0039048000000000
Statistics for column group: "pub_id", "type", "pubdate"
Last update of column statistics: Feb 4 1998 12:58PM
Range cell density: 0.0002011791956201
Total density: 0.0002011200000000
With 5000 rows in the table, the increasing precision of the optimizer’s estimates of rows to be returned depends on the number of search arguments used in the query:
An equality search argument on only pub_id results in the estimate that 0.0335391029690461 * 5000 rows, or 168 rows, will be returned.
Equality search arguments for all three columns result in the estimate that 0.0002011791956201 * 5000 rows, or only 1 row will be returned.
This increasing level of accuracy as more search arguments are evaluated can greatly improve the optimization of many queries.
