Connor,
Auto_sample_size is declared as constant Zero inside dbms_stats package, given that what do we gain/loss when estimate_percent=auto_sample_size and estimate_percent=0 ?
-- constant used to indicate auto sample size algorithms should
-- be used.
AUTO_SAMPLE_SIZE CONSTANT NUMBER := 0;
I did this test on 11.2.0.4, i don't find any , help me to understand ( big_table has one million rows, its a copy of all_objects, with a couple of index defined on it).
rajesh@ORA11G> exec dbms_stats.create_stat_table(user,stattab=>'auto_sample_size');
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.create_stat_table(user,stattab=>'zero_sample_size');
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'big_table',estimate_percent=>dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.export_table_stats(user,'big_table',stattab=>'auto_sample_size');
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'big_table',estimate_percent=>0);
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.export_table_stats(user,'big_table',stattab=>'zero_sample_size');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select * from table(dbms_stats.diff_table_stats_in_stattab(
2 user,'big_table',
3 stattab1=>'auto_sample_size',
4 stattab2=>'zero_sample_size',
5 pctthreshold=>0));
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
############################################################################### 0
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : BIG_TABLE
OWNER : RAJESH
SOURCE A : User statistics table AUTO_SAMPLE_SIZE
: Statid :
: Owner : RAJESH
SOURCE B : User statistics table ZERO_SAMPLE_SIZE
: Statid :
: Owner : RAJESH
PCTTHRESHOLD : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN COLUMN STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
1 row selected.
rajesh@ORA11G> exec dbms_stats.drop_stat_table(user,stattab=>'auto_sample_size');
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_stats.drop_stat_table(user,stattab=>'zero_sample_size');
PL/SQL procedure successfully completed.
rajesh@ORA11G>
November 16, 2015 - 12:50 am UTC
Because in the "next" release of Oracle, we might make it "-1", or 101, or 1000, or anything we choose.
Anyone using "dbms_stats.auto_sample_size" will be fine. Anyone who used their own constants is now in a spot of bother.