Re: Clustering factor smaller than table blocks.

On 9/27/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 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_at_ORACLE10> create table t (pk constraint t_pk primary key) as   2 select rownum from dual connect by level <= 1000000;

Table created.

dellera_at_ORACLE10> insert into t(pk) values (-1);

1 row created.

dellera_at_ORACLE10> commit;

Commit complete.

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

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

PL/SQL procedure successfully completed.

Outcome:

dellera_at_ORACLE10> select table_name, num_rows from user_tables where table_name = 'T';

TABLE_NAME NUM_ROWS

-------------------- ----------
T                       1000000

dellera_at_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