Re: How to drop/delete/remove histograms? (CBO Column stats)

eka_mercury-forums_at_yahoo.com wrote:
> Hello friends,
>
> Can anyone please tell me how to drop (only) histograms on a column?
>
> Ours is an OLTP application.
> Few of our queries go really bad when there are histograms.
> We need only NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS,
> AVG_COL_LEN in the data dictionary.
>
> Or rather, how to retain only the above statistics and drop rest stats?
>
> The procedure DBMS_STATS.DELETE_COLUMN_STATS does not have any option
> to remove/add only selected statistics.
>
> - Manu

First I would recommend that you read Mr. Lewis's new book Cost based Oracle Fundamentals. In particular you might want to look at the section in chapter 5 "Correcting the Statistics".

Proceed slowly, carefully, and check your results on a test system before implementing what you are thinking about.

You should be able to use some combination of dbms_stats procedures as follows: get_column_stats, get_index_stats, get_table_stats. Get the stuff that you need and save it somewhere.

You can use delete_column_stats, delete_index_stats, and delete_table_stats to clear things ( or maybe just set the ones you don't want to something else ? ). Test out very carefully if you do use the delete procedures ( test out carefully anyhow ).

You can use set_column_stats, set_index_stats, and set_table_stats to set the values that you want.

Might be somewhat version dependent and expect to find some bugs along the way! Received on Wed Dec 06 2006 - 13:51:52 CST