How To Quickly Add/Remove Column Statistics (Histograms) For A Column (文档 ID 390249.1

本文介绍如何使用单一命令快速添加或删除Oracle数据库中特定列的统计信息（即直方图）。这些命令不会影响其他列已收集的统计信息，只针对指定列。此外，还提供了删除所有类型的列统计信息及直方图的方法。

In this Document
 
 Goal
 Fix
 APPLIES TO: Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.3 [Release 8.1.7 to 11.2]
 
 Information in this document applies to any platform.
 
 GOAL
In some situations it is necessary to quickly add or remove histograms from a particular column (also known as column statistics.)
 
 This article shows how to accomplish this with one command.
 
 The commands given in this article will not affect column statistics currently gathered for other columns, they will only touch the specified column.
 
 Column statistics can be:
 - so called minimal consisting of just 1 bucket (2 endpoints) with min/max value information required by the Optimizer
 - histograms with between 2 and 254 buckets (3 and 255 endpoints respectively)
 - absent with no column information recorded in the data dictionary (not normally recommended)
 FIXAssuming you are connected as the owner of the table.
 
 To add column statistics use one of the following varieties:
using up to the default number of buckets 75: 
   
     SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name>');
   
using the appropriate number of buckets for the data distribution (will be minimal when not skewed) : 
   
     SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size skewonly');
   
using maximum number of buckets (up to 254) : 
   
     SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 254');
   
using up to specified number of buckets N (N is between 2 and 254) : 
   
     SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size N');
   
gathering statistics for multiple columns with different bucket sizes : 
   
     SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');
   
gathering statistics for multiple columns with different bucket sizes plus minimal information for the remaining columns: 
   
     SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for all columns size 1 for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');
   
 To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-Oracle11g versions:)

   SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 1');
 
 To completely erase all types of column statistics/histograms for a column including minimal statistics:

   SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>');
 
This is generally not recommended as the Optimizer needs at least the minimal (size 1) information.
 
 In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:

   SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>', col_stat_type=>'HISTOGRAM');
 
 Additionally there is functionality to delete histograms for a partition:
 
   exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>',
  
 colname=>'<column_name>',partname=>'<partition_name>', col_stat_type=>'HISTOGRAM')
 
 and to delete column histograms for the table and all its partitions:
 
   exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>',
  
 colname=>'<column_name>',cascade_parts=>TRUE,col_stat_type=>'HISTOGRAM')

   
            相关内容 
          
              产品 
            
       Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Generic SQL Performance, SQL Execution, Query Optimizer
 
              关键字 
            
     COLUMN STATISTICS;
     
     DBMS_STATS.DELETE_COLUMN_STATS;
     
     DBMS_STATS.GATHER_TABLE_STATS