Re: statistics stability

Niall has a good point but I thought that is only if histograms are used and
you are not in this case


No, it's the same even without histograms:

create table t (x int);
insert into t values(0);
insert into t values(0);
insert into t values(10);
insert into t values(10);
exec dbms_stats.gather_table_stats (user, 't', method_opt=>'for all
columns size 1');
set autotrace traceonly explain

dellera@ORACLE10> select * from t where x = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |     4 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |     4 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("X"=5)

dellera@ORACLE10> select * from t where x = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     2 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("X"=20)

More informations are contained in "Cost Based Oracle"; it's different
in 9i and 10g, but if you select far enough from the min/max range
recorded in the column statistics, you'll get 0 rounded to 1.

So Niall's caveat definitely applies :)
--
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l

Other related posts: