11G,可以只刪除直方圖資訊

wei-xh發表於2011-03-14
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 should not have statistics
because you want to take advantage of dynamic sampling (this feature is covered in Chapter 5).
In that case, the following procedures are available in the package dbms_stats:
• delete_database_stats
• delete_dictionary_stats
• delete_fixed_objects_stats
• delete_schema_stats
• delete_table_stats
• delete_column_stats
• delete_index_stats
As you can see, for each procedure gather_*_stats, there is a corresponding procedure
delete_*_stats. The former ones gather object statistics, and the latter ones delete object
statistics. The only exception is the procedure delete_column_stats. As its name suggests, it is
used for deleting column statistics and histograms.
Table 4-10 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
procedures gather_*_stats. I will 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 is set to ALL, column statistics
and histograms are deleted. If it is set to HISTOGRAM, only histograms are deleted. The
default value is ALL. This parameter is available as of Oracle Database 11g.
The following call shows how to delete the histogram of one single column (a full example
is found in the script. delete_histogram.sql) without modifying the other statistics:
dbms_stats.delete_column_stats(ownname => user,
tabname => 'T',
colname => 'VAL',
col_stat_type => 'HISTOGRAM')

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-689414/,如需轉載,請註明出處,否則將追究法律責任。

相關文章