The DBMS_STATS package provides the following four procedures for you to delete statistics:
DELETE_TABLE_STATS: deletes table-level statistics.DELETE_COLUMN_STATS: deletes column-level statistics.DELETE_SCHEMA_STATS: deletes statistics on all tables in a schema.DELETE_INDEX_STATS: deletes index statistics.
# Delete all statistics on the T1 table under the TEST user.
call dbms_stats.delete_table_stats('TEST', 'T1');
# Delete statistics on the C1 column of the T1 table under the TEST user.
call dbms_stats.delete_column_stats('TEST', 'T1', 'C1');
# Delete statistics on all tables under the TEST user.
call dbms_stats.delete_schema_stats('TEST');
# Delete statistics on index IDX under the TEST user.
call dbms_stats.delete_schema_stats('TEST', 'IDX');
The DBMS_STATS package allows you to set only the following table-level statistics: the number of rows, average length of the rows, number of macroblocks, and number of microblocks, as well as column-level attributes like number of distinct values (NDV), number of NULL values, and average column length. Other statistics, such as histograms, are not supported. The following three procedures are provided to set statistics:
SET_TABLE_STATS: sets table-level statistics.SET_COLUMN_STATS: sets column-level statistics.SET_INDEX_STATS: sets the index statistics.
# Set the total number of rows in the T1 table under the TEST user to 10,000.
call dbms_stats.set_table_stats('TEST', 'T1', numrows=>10000);
# Set the NDV and number of NULLs in the C1 column of the T1 table under the TEST user to 10.
call dbms_stats.set_column_stats('TEST', 'T1', 'C1', distcnt=>10, nullcnt=>10);
# Set the total number of rows of the IDX index under the TEST user to 10,000.
call dbms_stats.set_table_stats('TEST', 'IDX', numrows=>10000);