Re: statistics stability

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Sun, 21 Jan 2007 14:24:00 +0100
Message-ID: <4ef2fbf50701210524s31d652a2ic5cbebcff19d0203@mail.gmail.com>

> 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_at_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_at_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

Received on Sun Jan 21 2007 - 07:24:00 CST