Adaptive Server stores two values for the density of column values:
“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.
“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 is 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 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 group: "pub_id", "type" Last update of column statistics: Apr 8 2008 9:22:40:963AM Range cell density: 0.0000000362887320 Total density: 0.0000000362887320 Range selectivity: default used (0.33) In between selectivity: default used (0.25) Unique range values: 0.0000000160149449 Unique total values: 0.0000000160149449 Average column width: default used (8.00) Statistics for column group: "pub_id", "type", "pubdate" Last update of column statistics: Apr 8 2008 9:22:40:963AM Range cell density: 0.0000000358937986 Total density: 0.0000000358937986 Range selectivity: default used (0.33) In between selectivity: default used (0.25) Unique range values: 0.0000000158004305 Unique total values: 0.0000000158004305 Average column width: 2.0000000000000000
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.