SQL/MX 2.x Reference Manual (H06.04+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—540440-003
10-42
Histograms
CACHE_HISTOGRAMS_
REFRESH_INTERVAL
Controls the time interval, in seconds, at which
histograms in the histogram cache are refreshed. This is
the maximum time a histogram in the cache can be out
of date.
Allowable values: 0 through 4294967040.
The default value is 3600 seconds.
DYNAMIC_HISTOGRAM_
COMPRESSION
Set to ON or OFF. When set to ON, NonStop SQL/MX
reduces compile time by reducing the number of
histogram intervals. Histogram interval reduction has
more affect on complex queries, especially if the
underlying data distribution is evenly distributed.
The compiler reduces the number of histogram intervals
for columns containing numeric data types and
nonnumeric data type columns only if there is no join or
range predicate.
The default is ON.
HIST_DEFAULT_SEL_FOR_
LIKE_WILDCARD
Specifies the selectivity factor used by the optimizer for
LIKE predicates where the matched pattern starts with a
wildcard, for example, the predicate (user_email LIKE
'%.net'). The value is expressed as a percentage so that
.10 is 10 percent and .333 is 33.3 percent.
Allowable values: 0 through 1.
The default value is 0.10.
HIST_DEFAULT_SEL_FOR_
PRED_RANGE
Specifies the selectivity factor used by the optimizer for
range predicates when current histogram statistics do not
exist. This default is also used for the selectivity of range
predicates involving host variables or parameters, for
example, the predicate (quantity > ?p1). The value is
expressed as a percentage so that .333 is 30 percent.
Allowable values: 0 through 1.
The default value is 0.333.
HIST_JOIN_CARD_
LOWBOUND
NonStop SQL/MX uses certain assumptions about the
relationship between columns from different tables that
are involved in a join. In case of insufficient multicolumn
statistics, these assumptions might result in
underestimating the join cardinality result. The estimated
cardinality of the join should not be less than a
percentage of the cardinality of the smallest table
involved in the join. This default specifies this percentage
or fraction value.
Allowable values: 0 through 1.
The default value of 1.0 corresponds to a join cardinality
lower bound equal to 100 percent of the cardinality of the
smallest table in the join.
A value of 0 means that there is no lower bound limit
applied to the join cardinality.
Attribute Setting