Re: Clustering factor smaller than table blocks.

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 27 Sep 2006 10:03:27 +0100

One possibility relates to ASSM - you can have blocks below the highwater mark that are not yet formatted. I'd have to check, but I don't think they get recorded as EMPTY_BLOCKS.

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 ?


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

----- Original Message ----- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, September 27, 2006 9:47 AM
Subject: Clustering factor smaller than table blocks.

I have a table with 368 blocks, no free blocks, and the clustering factor of the primary key is 345. how is it possible?

I thought that CF will give you the amount of table blocks that an index
will need to access for a given rowset. Something like
smallest( table blocks, ceil( (CF/tot table rows)*rowset/avg rows per
block)) (real formula must be much complicated.

However if I have an avg ro

Here are the brute facts:

         from dba_tables where table_name = 'CUSTOMERS' and owner =
'SOE' ;

------------ ------------------------- -------------------
          0                         0                   0

The table was analysed with dbms_stat, percent = 100:

exec dbms_stats.gather_table_stats( ownname=>'SOE', tabname=>
'CUSTOMERS', Degree=> 4, estimate_percent=> 100, granularity=>'ALL',
cascade=>TRUE, stattab=>'sm_stattab', statid=>'CUSTOMERS09270906',

Table name Owner NUM_ROWS BLOCKS
------------------------------ ---------------- ---------- ----------
-------- ------------------- CUSTOMERS SOE 36364 368
3 09/27/2006 08:39:50

But still the clust factor reported by dba_indexes for CUSTOMERS_PK is inferiors to CUSTOMERS block count.

Table Index Column Clust Distinct Name Name U Name Factor Keys NUM_ROWS -------------------- ------------------------------ - -------------------- ---------- ---------- ---------- CUSTOMERS CUSTOMERS_PK U CUSTOMER_ID 345 36112 36112

This is oracle

B. Polarski


No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.9/457 - Release Date: 26/09/2006


Other related posts: