Hi All,
Oracle 11G R2 AIX
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:
a) 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
b) 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;
begin
m_distcnt := 6;
m_density := ??????;
m_nullcnt := 0;
m_avgclen := 5;
n_array := dbms_stats.numarray(0,1,2,3,4,5);
srec.bkvals := dbms_stats.numarray( 74086, 152500, 704, 858271,
1257221,156704455); ---Actual distribuution
srec.epc := 6;
dbms_stats.prepare_column_values(srec, n_array);
dbms_stats.set_column_stats(
ownname => 'XXXXXX',
tabname => XXXXEVENTXXXXX',
colname => 'STATUS',
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => srec,
avgclen => m_avgclen
);
end;
/
Jack van Zanen
-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation
