How to delete statistics from Table/index/column in Oracle

We may sometimes need to delete the statistics on the Table/index/column in Oracle. In this post we will check out how we can do it with standard oracle defined procedures

How to delete statistics from Table

Oracle has provided a procedure in DBMS_STATS Package

DBMS_STATS.DELETE_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   cascade_parts    BOOLEAN  DEFAULT TRUE, 
   cascade_columns  BOOLEAN  DEFAULT TRUE,
   cascade_indexes  BOOLEAN  DEFAULT TRUE,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN  DEFAULT FALSE,
   stat_category    VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

• Here cascade parameters are default true which means when table stats are deleted, partition, index, and column stats are also deleted. If you dont want the default behavior, you can change it while executing the package
• stattab, statid is relevant, if you are deleting stats from the user statistics table. The default is null, so stats are deleted from the dictionary
• partname defines the name of the table partition or subpartition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level.

Example

BEGIN
dbms_stats.delete_table_stats(ownname=>'SCOTT', tabname=>'EMP_DATA');
END;
/

How to delete statistics from index

Oracle has provided a procedure in DBMS_STATS Package

DBMS_STATS.DELETE_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2,
   partname         VARCHAR2 DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   cascade_parts    BOOLEAN  DEFAULT TRUE,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'ALL',
   force            BOOLEAN  DEFAULT FALSE);
   stat_category    VARCHAR2 DEFAULT DEFAULT_DEL_STAT_CATEGORY); 

• Here cascade parameters are default true which means when index stats are deleted, index partition stats are also deleted. If you dont want the default behavior, you can change it while executing the package
• stattab, statid is relevant, if you are deleting stats from the user statistics table. The default is null, so stats are deleted from the dictionary
• partname is the Name of the index partition for which to delete the statistics. If the index is partitioned and if partname is NULL, then index statistics are deleted at the global level.

Example

BEGIN 
dbms_stats.delete_index_stats(ownname=>'SCOTT', indname=>'EMP_DATA_N1'); 
END;
 /

How to delete the column statistics

DBMS_STATS.DELETE_COLUMN_STATS (
   ownname        VARCHAR2, 
   tabname        VARCHAR2, 
   colname        VARCHAR2, 
   partname       VARCHAR2 DEFAULT NULL,
   stattab        VARCHAR2 DEFAULT NULL, 
   statid         VARCHAR2 DEFAULT NULL,
   cascade_parts  BOOLEAN  DEFAULT TRUE,
   statown        VARCHAR2 DEFAULT NULL,
   no_invalidate  BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force          BOOLEAN  DEFAULT FALSE,
   col_stat_type  VARCHAR2 DEFAULT 'ALL');

• Here cascade parameters are default true which means if the table is partitioned and if partname is NULL, then setting this to true causes the deletion of statistics for this column for all underlying partitions as well.
• stattab, statid is relevant, if you are deleting stats from the user statistics table. The default is null, so stats are deleted from the dictionary
• partname is the Name of the table partition for which to delete the statistics. If the table is partitioned and if the partname is NULL, then column statistics are deleted at the global level.

Example

BEGIN  
dbms_stats.delete_column_stats(ownname=>'SCOTT', tabname=>'EMP_DATA',colname=>'EMP_NAME');  
END;  
/

I hope you like this post on How to delete statistics for table in Oracle, How to delete statistics for index in Oracle, How to delete statistics for columns in Oracle

Related Articles

How to import statistics in Oracle
How to export statistics in Oracle
How to transfer statistics between databases in Oracle
How to create stat table in Oracle
How to set statistics for table in oracle