Database Reference
In-Depth Information
Figure 8-7.
Gathering column statistics and histograms for all columns with a single value for the
size_clause
parameter (see Table
8-5
)
•
Gathering column statistics on all columns and histograms on a subset of columns.
All histograms are created with the very same value for the
size_clause
parameter.
The syntax is a combination of that in Figure
8-7
and Figure
8-8
: the former specifies the
gathering of column statistics, and the latter specifies the gathering of histograms. For
example, specifying
for all columns size 1 for columns size 254 col1
causes
column statistics to be gathered for every column and a histogram with up to 254 buckets
to be gathered for the
col1
column only.
Figure 8-8.
Gathering column statistics and histograms only for a subset of columns but with different values
of the
size_clause
parameter (see Table
8-6
). For the columns that don't explicitly specify a
size_clause
, the
default
size_clause
(the first one) is used. If no columns are specified, no column statistics are gathered at all.
The
column_clause
can be a column name, an extension name, or an extension. If an extension that doesn't exist
is specified, a new extension is automatically created. This syntax is available only when the
gather_table_stats
procedure is used
Table 8-6.
Values Accepted by the
size_clause
Parameter
Value
Meaning
size n
The value specifies the maximum number of buckets. If
size 1
is specified, no histograms are
created. In any case, column statistics are gathered normally.
size skewonly
Histograms are gathered only for columns with skewed data. The number of buckets is
determined automatically.
size auto
Histograms are gathered only for columns with skewed data, such as
skewonly
, and, in addition,
for those that have been referenced in
WHERE
clauses. This second condition is based on the
column usage history. The number of buckets is determined automatically.
size repeat
Refreshes available histograms.
•
Gathering column statistics and histograms only for a subset of columns and with
different values for the
size_clause
parameter. The syntax is shown in Figure
8-8
. For
example, specifying
for columns size 1 id, col1 size 100, col2 size 5, col3
