Hi,
I've run across an anomaly while updating statistics. It appears that the sample size specified by the analyze statement is not producing the results I'd hoped for. By my calculations, the 5% estimate has a sample size of 1%, the 10% uses 1.6%, and the 40% uses 5%. Are there any known causes for this type of effect? Has anyone else encountered this before? I'm wondering if it has to do with the way the first few blocks are filled. I'm seeing this for a number of tables.
Thanks,
Russ Brooks
sapsan:SAN>analyze table bnka delete statistics;
Table analyzed.
sapsan:SAN>analyze table bnka estimate
statistics sample 5 percent;
Table analyzed.
sapsan:SAN>@s
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
BNKA 580
sapsan:SAN>analyze table bnka delete statistics;
Table analyzed.
sapsan:SAN>analyze table bnka estimate
statistics sample 10 percent;
Table analyzed.
sapsan:SAN>get
s
1 select table_name, sample_size from user_tables
2* where table_name
= 'BNKA'
TABLE_NAME SAMPLE_SIZE
------------------------------
-----------
BNKA 931
sapsan:SAN>analyze table bnka delete statistics;
Table
analyzed.
sapsan:SAN>analyze table bnka estimate statistics sample 40
percent;
Table analyzed.
sapsan:SAN>get s
1 select
table_name, sample_size from user_tables
2* where table_name = 'BNKA'
sapsan:SAN>r
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
BNKA 2996
sapsan:SAN>analyze
table bnka compute statistics;
Table analyzed.
sapsan:SAN>get
s
1 select table_name, sample_size from user_tables
2* where table_name
= 'BNKA'
sapsan:SAN>r
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
BNKA 57734
