Range cell and total density values

Adaptive Server stores two values for the density of column values:

Using two separate values improves the optimizer’s estimates of 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:

This increasing level of accuracy as more search arguments are evaluated can greatly improve the optimization of many queries.