Delete Histogram

Here’s a note that I drafted in November 2010, then didn’t publish. I found it earlier on this morning while looking for another note I’d written about histograms so, even though it may not be something that people need so much these days, I thought: better late than never.

I’ve pointed out in the past that I’m not keen on seeing lots of histograms on a system and tend to delete them if I think they are not needed. Here’s an example of the type of code I use to delete a histogram.

rem
rem     Script:         delete_histogram.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2010
rem 

declare

        srec                    dbms_stats.statrec;
        m_distcnt               number;
        m_density               number;
        m_nullcnt               number;
        m_avgclen               number;

        n_array                 dbms_stats.numarray;

begin

        dbms_stats.get_column_stats(
                ownname         => user,
                tabname         => 't1',
                colname         => 'n1',
                distcnt         => m_distcnt,
                density         => m_density,
                nullcnt         => m_nullcnt,
                srec            => srec,
                avgclen         => m_avgclen
        ); 

        srec.bkvals := null;
        srec.novals := dbms_stats.numarray(
                utl_raw.cast_to_number(srec.minval),
                utl_raw.cast_to_number(srec.maxval)
        );
        srec.epc := 2;
        dbms_stats.prepare_column_values(srec, srec.novals);

        m_density := 1/m_distcnt;

        dbms_stats.set_column_stats(
                ownname         => user,
                tabname         => 't1',
                colname         => 'n1',
                distcnt         => m_distcnt,
                density         => m_density,
                nullcnt         => m_nullcnt,
                srec            => srec,
                avgclen         => m_avgclen
        ); 

exception
        when others then
                raise;          -- should handle div/0

end;
/

The code basically reads the column stats, resets the histogram figures to just the low and high values for the column, setting the endpoint-count to two, then adjusts the density to the standard for a column with no histogram. This specific example is for a numeric column.

Footnote: my preferred method of collecting statistics is to use method_opt => ‘for all columns size 1’ (i.e. no histograms) and then run scripts to create the histograms I want. This means that after any stats collection I need to run code that checks to see which tables have new stats, and then re-run any histogram code that I’ve written for that table.

To move from Oracle’s default histogram collection to this strategy, you could start by switching to method_opt => ‘for all columns size repeat’ (i.e. recreate existing histograms, don’t create new ones [ but see footnote 3, this is no longer safe]), then simply delete histograms as you find that you don’t need them, and introduce scripts to recreate the histograms that you do need. When you’ve finally got to the point where every histogram is scripted you can then switch to method_opt => ‘for all columns size 1’.

 Footnote 2: Since 2010 when I drafted this note Oracle 12c has launched, and the changes it has introduced for frequency and Top-N histograms means that I’m far less stringent in my demand that if a histogram is worth having it’s better to write code to create it. There’s a series of three articles about 12c histograms in particular at this link.

Footnote 3: Since writing this article I’ve discovered that “size repeat” has what Oracle should have published as a “notable change in behaviour” for 12c and it is no longer a suitable method_opt for histogram collection. See this note.