Database Reference
In-Depth Information
diff_table_stats_in_pending
compares, for one table, either the current object statistics
or a set from the history, with the pending statistics. To specify object statistics stored in
the history, the parameter
time_stamp
is provided. If this parameter is set to
NULL
(default),
current object statistics are compared to pending statistics. The following example compares
the current statistics with the pending statistics:
•
dbms_stats.diff_table_stats_in_pending(ownname => user,
tabname => 'T',
time_stamp => NULL,
pctthreshold => 10));
Deleting Object Statistics
You can delete object statistics from the data dictionary. Except for testing purposes, this is usually not necessary.
Nevertheless, it might happen that a table shouldn't have object statistics because you want to take advantage of
dynamic sampling (covered in Chapter 9). In that case, the following procedures are available in the
dbms_stats
package:
delete_database_stats
,
delete_dictionary_stats
,
delete_fixed_objects_stats
,
delete_schema_stats
,
delete_table_stats
,
delete_column_stats
, and
delete_index_stats
.
As you can see, for each
gather_*_stats
procedure, there is a corresponding
delete_*_stats
procedure.
The former ones gather object statistics, and the latter ones delete object statistics. The only exception is the
delete_column_stats
procedure. As its name suggests, it's used for deleting column statistics and histograms.
Table
8-8
summarizes the parameters available for each of these procedures. Most of them are the same and,
therefore, have the same meaning as the parameters used by the
gather_*_stats
procedures. I describe here only the
parameters that have not already been described with the earlier procedures:
•
cascade_parts
specifies whether statistics for all underlying partitions are deleted. This
parameter accepts the values
TRUE
and
FALSE
. The default value is
TRUE
.
•
cascade_columns
specifies whether column statistics are deleted as well. This parameter
accepts the values
TRUE
and
FALSE
. The default value is
TRUE
.
•
cascade_indexes
specifies whether index statistics are deleted as well. This parameter accepts
the values
TRUE
and
FALSE
. The default value is
TRUE
.
•
col_stat_type
specifies which statistics are deleted. If it's set to
ALL
, column statistics and
histograms are deleted. If it's set to
HISTOGRAM
, only histograms are deleted. The default value
is
ALL
. This parameter is available as of version 11.1.
•
stat_category
specifies which category of statistics is deleted. It accepts a comma-separated
list of values. If
OBJECT_STATS
is specified, object statistics (table statistics, column statistics,
histograms, and index statistics) are deleted. If
SYNOPSES
is specified, only information
supporting incremental statistics is deleted. By default, both object statistics and synopses are
deleted. This parameter is available as of version 12.1.