Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Clustering factor smaller than table blocks.

Re: Clustering factor smaller than table blocks.

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Wed, 27 Sep 2006 13:09:02 +0200
Message-ID: <4ef2fbf50609270409i4214a49eg540c96b492d224e@mail.gmail.com>


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
Received on Wed Sep 27 2006 - 06:09:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US