stats not playing nice

We have a fairly large table (24G) that has a status field. there are 6 possible statuses and the Majority are status 5(completed) for which there is a need to retain for 13 months.

Now the stats on that table take fairly long to compute so estimate is used, but as the data is very skewed sometimes we miss out on some values altogether.

Now, I think the best solution would have been at creation time to split this up and move the completed records to a separate table partitioned by month and keep the batch processing to use the lean and mean table.

Failing that:

I can:

1. Run a full compute statistic at an opportune time including histograms (takes a long time and I am not sure we get a window for this. and than lock the stats untill we do it again
2. keep using estimate statistics and manually insert the values for this one column.

I have found a blog post on Jonathan Lewis website where he has an example that is fairly easy to follow:

This is the Code from Jonathan Lewis blog post from almost a decade ago.

I have one question regarding the density. What should I be putting here

declare

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

    m_distcnt   := 6;
    m_density   := ??????;
    m_nullcnt   := 0;
    m_avgclen   := 5;

    n_array     := dbms_stats.numarray(0,1,2,3,4,5);

        ownname     => 'XXXXXX',
        tabname     => XXXXEVENTXXXXX',
        colname     => 'STATUS',
        distcnt     => m_distcnt,
        density     => m_density,
        nullcnt     => m_nullcnt,
        srec        => srec,
        avgclen     => m_avgclen

--
http://www.freelists.org/webpage/oracle-l