Re: Clustering factor smaller than table blocks.

On 9/27/06, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote: 
The rather more surprising thing about your example
is that you have done a compute, so the results should
be accurate, but you have  36,364 rows in the table
and only 36,112 entries in what we guess is the primary
key index.  Do you have a corrupt index perhaps ?


There could be a simpler explanation - if the statistics
are computed while the table is being modified, the
table and index stats could be not consistent, even
when using cascade in the same dbms_stats statement.

In 10.2.0.2:

dellera@ORACLE10> create table t (pk  constraint t_pk primary key) as
 2  select rownum from dual connect by level <= 1000000;

Table created.

dellera@ORACLE10> insert into t(pk) values (-1);

1 row created.

dellera@ORACLE10> commit;

Commit complete.

Immediately before the insert, I started the stats collection,
that ended some minutes after:

dellera@ORACLE10> exec dbms_stats.gather_table_stats (user, 't',
cascade=>true, estimate_percent=>100);

PL/SQL procedure successfully completed.

Outcome:

dellera@ORACLE10> select table_name, num_rows from user_tables where
table_name = 'T';

TABLE_NAME             NUM_ROWS
-------------------- ----------
T                       1000000

dellera@ORACLE10> select index_name, num_rows from user_indexes  where
table_name = 'T';

INDEX_NAME             NUM_ROWS
-------------------- ----------
T_PK                    1000001

hth
al

--
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l


Other related posts: