sp_modifystats

Allows the system administrator, or any user with permission to execute the procedure and update statistics on the target table, to modify the density values of columns in sysstatistics.

Syntax

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

Or:

sp_modifystats [database].[owner].table_name, 
column_name | null,
	REMOVE_SKEW_FROM_DENSITY
	REMOVE_STICKINESS

Parameters

table_name – is the name of the table to change. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
column_group – an ordered list of column names. To change a statistic for multiple columns (such as a density value), list the columns in the order used to create the statistic. Separate the column names with commas. For example, if your table has a density statistic on columns a1, a2, a3, a4:
- “a1” modifies column a1.
- “a1,a2,a3” modifies the column group a1,a2,a3,
- You can also use a wildcard character , %, with the column_group parameter to represent a range of characters. For example, “a1,%,a3” modifies the groups a1,a2,a3 and a1, a4, a3, and so on; “a1,%” modifies the groups a1,a2 and a1,a2,a3, and so on, but not a1; “a1%” modifies the groups a1,a2 and a1,a2,a3, and so on, as well as a1.
all – modifies all column group for this table. Because “all” is a keyword, it requires quotes.
modify_density – allows you to modify either the range or total density of a column or column group to the granularity specified in the value parameter. Range cell density represents the average number of duplicates of all values that are represented by range cells in a histogram. value is either the specified density value or a multiple for the current density. Must be between zero and one, inclusive, if absolute is specified.See the Performance and Tuning Guide for more information.Where:
- range – modifies the range cell density.
- total – modifies the total cell density.
- absolute – ignore the current value and use the number specified by the value parameter.
- factor – multiply the current statistical value by the value parameter.
modify_default_selectivity – specifies the default selectivity value. Must be between zero and one, inclusive.
Where:
- 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.
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.
REMOVE_STICKINESS – removes the stickiness associated with the specified column. Specify null to remove the stickiness from all columns in the table.
“Stickiness” occurs when the SAP ASE server retains the memory for these update statistics parameters:
- using step values
- sampling
- histogram_tuning_factor
- hashing
- no_hashing
- partial_hashing
Once a phrase is “sticky,” the SAP ASE server retains its behavior for that column on subsequent update statistics commands, even if you do not explicitly specify the parameters.
column_name – is the name of a column in that table.
REMOVE_SKEW_FROM_DENSITY – allows the system administrator to change the total density of a column to be equal to the range density, which is useful when data skew is present. Total density represents the average number of duplicates for all values, those in both frequency and range cells. Total density is used to estimate the number of matching rows for joins and for search arguments with a value that is unknown when the query is optimized. See the Performance and Tuning Guide for more information.
REMOVE_SKEW_FROM_DENSITY also updates the total density of any composite column statistics for which this column is the leading attribute. Most commonly, a composite index for which this column is the leading attribute would produce these composite column statistics, but they can also be produced when you issue a composite update statistics command.

Examples

Example 1 – Changes the range density for column group c00, c01 in table tab_1 to 0.50000000:
```
sp_modifystats  "tab_1", "c00, c01", MODIFY_DENSITY, range, absolute, "0.5"
```
Example 2 – The total density for column group c00, c01 in tab_1 is multiplied by .5. That is, divided in half:
```
sp_modifystats  "tab_1", "c00,c01", MODIFY_DENSITY, total, factor, "0.5"
```
Example 3 – The total density for all the columns in table tab_1 is multiplied by .5.
```
sp_modifystats  "tab_1", "all", MODIFY_DENSITY, total, factor, "0.5"
```
Example 4 – Total density for all column groups starting with c12 is changed to equal the range density.
```
sp_modifystats "tab_1", "c12" REMOVE_SKEW_FROM_DENSITY
```
Example 5 – 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"  
```
Example 6 – 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"  
```
Example 7 – 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" 
```
Example 8 – 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"  
```

Usage

There are additional considerations when using sp_modifystats:

- Allows the system administrator to modify the density values of a column—or columns—in sysstatistics.
- Use optdiag to view a table’s statistics. See the Performance and Tuning Guide for more information about table density and using optdiag.
- Any modification you make to the statistics with sp_modifystats is overwritten when you run update statistics. To make sure you are using the most recent statistical modifications, you should run sp_modifystats after you run update statistics.
- Because sp_modifystats modifies information stored in the sysstatistics table, you should make a backup of statistics before execute running sp_modifystats in a production system.
- You can use modify_default_selectivity only on an individual column, not a column group.
- SAP ASE uses the default selectivity for modify_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.

See also update statistics in Reference Manual: Commands.

Permissions

The permission checks for sp_modifystats differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `update statistics` on the object or with `manage any statistics` privilege. You must have execute permission on the procedure.
Disabled	With granular permissions disabled, you must be a user with with `update statistics` on the object or sa_role. You must have execute permission on the procedure.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect

Tables used

sysstatistics