The update statistics command has been extended to specify an out-of-range histogram adjustment at the column level.
Column statistics for rapidly growing tables may become out-of-date when an update statistics command completes. This results in out-of-range SARGs (search clauses) that select a greater range of values than described by the column’s histogram. Out-of-range SARGS have a selectivity of 0. The out-of-range histogram adjustment feature adjusts a column’s histogram, and assigns an appropriate selectivity value to such SARGs.
update statistics table_name (column_name)using out_of_range [on | off| default]
on – Enables out-of-range histogram adjustment for column_name.
off – Disables out-of-range histogram adjustment for column_name.
default – Affects the out-of-range histogram adjustment depending on the value of trace flag 15355:
Disables out-of-range histogram adjustment when Traceflag 15355 is on.
Enables out-of-range histogram adjustment when Traceflag 15355 is off.
With Adaptive Server Enterprise version 15.0.2 ESD #4
and later, histogram adjustment for out of range SARGS is enabled
server wide by default. You can turn it off using Traceflag 15355.
Example 1: When an out_of_range SARG is detected for a column, the optimizer adjusts the column’s histogram and assigns an appropriate selectivity value to the out-of-range clause.
update statistics TOFO_FUOP_ORD(OrdDt) using out_of_range on
Example 2: In this example, if trace flag 15355 is turned on, the column’s histogram is not adjusted for out-of-range SARGs.
update statistics TOFO_FUOP_ORD(OrdDt) using out_of_range default
optdiag has been enhanced to display
the out_of_range histogram adjustment
value and to read this value from optdiag output
files. The default value is used for reading optdiag files
that do not have out_of_range histogram adjustment
information (for example, from a version earlier than 15.0.2 ESD #4).
Error 16015 is raised if you attempt to use out_of_range options for update statistics alongside other options such as consumers or sampling.
Error 16016 is raised if you specify out_of_range options for a column that currently has no column level statistics.
update statistics permission defaults to the table owner and is not transferable. The command can also be executed by the Database Owner, who can impersonate the table owner by running the setuser command.
For optdiag syntax and usage, see Chapter 6, “Statistics Tables and Displaying Statistics with optdiag” in Performance and Tuning Series: Monitoring and Analyzing:
