System Procedures

sp_modifystats adds these parameters:

 sp_modifystats [database].[owner].table_name, {"column_group" | "all"},
. . . 
modify_default_selectivity,
    {inequality | inbetween},
    {absolute | factor},
'value'
modify_unique
    {range | total },
    {absolute | factor},
'value'

Where:

• modify_default_selectivity – specifies the default selectivity value. Must be between zero and one, inclusive. modify_default_selectivity is one of:
  • inequality – indicates columns in which the predicate has an upper bound or a lower bound, but not both, and includes these range operators: > =, <=, >, <. The default value for inequality is .33
  • inbetween – indicates columns in which the predicate includes the upper bound and lower bound, and includes these range operators: > =, <=, >, <. The default value for inbetween is .25
  • absolute – ignore the current value and use the number specified by the value parameter.
  • factor – multiply the current statistical value by the value parameter.
  Note: You can use modify_default_selectivity only on an individual column, not a column group.

  Adaptive Server uses the default selectivity when an unknown constant prevents it from using a histogram to estimate selectivity of the respective predicate. The default selectivity for a search argument using inequality is 33%. inequality search arguments include columns for which there is an upper bound predicate or a lower bound predicate, but not both, and use the >=, <=, >, < range operators. The default selectivity for search arguments that include an inbetween search arguments is 25%. inbetween search arguments include columns that have both an upper bound predicate and a lower bound predicate, or use the between operator.

• modify_unique – allows you to modify the range unique or total unique values of a column or column group to the granularity specified in the value parameter.
  • range – modifies the estimate for the number of unique values found in the range cells of the histogram. range does not include the frequency cells (that is, single-valued histogram cells). The estimate is represented as a fraction between 0.0 and 1.0, equal to:

    unique_range_values / (range_cell_rows * total rows_in_table)

  • total – modifies the estimate of the number of unique values for the column or column group (including the NULL value). The optimizer uses this value to estimate group by and distinct cardinality. It is represented as a fraction between 0.0 and 1.0 where the 1.0/<unique count> is stored in the catalogs.
  • absolute – ignore the current value and use the number specified by the value parameter.
  • factor – multiply the current statistical value by the value parameter.

This example sets the default selectivity of inequality predicates with unknown constants (for example, a1>@v1) to 0.09:

sp_modifystats t10, a1, MODIFY_DEFAULT_SELECTIVITY, inequality, absolute, "0.09" 

This example sets the default selectivity for column a2 to use a value of 0.11 if you specify upper bound and a lower bound predicates with unknown constants (for example, a2>@v1 and a2<@v2):

sp_modifystats t10, a2, MODIFY_DEFAULT_SELECTIVITY, inbetween, absolute, "0.11"

This example modifies the range value for all columns for table t10 by a factor of 0.13:

sp_modifystats t10, "all", MODIFY_UNIQUE, range, factor, "0.13"

This example modifies the total unique value for all columns for table t10 to an absolute value of 0.14, which indicates there are (1.0 / 0.14) = 7.1428 unique values for each column in the table:

sp_modifystats t10, "all", MODIFY_UNIQUE, total, absolute, "0.14" 
go