Clustering factor smaller than table blocks.

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Wed, 27 Sep 2006 10:47:43 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D568714@mailserver1>

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:

select EMPTY_BLOCKS,AVG_SPACE_FREELIST_BLOCKS ,NUM_FREELIST_BLOCKS

from dba_tables where table_name = 'CUSTOMERS' and owner =
'SOE' ;
EMPTY_BLOCKS AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS

------------ ------------------------- -------------------
           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', statown=>'SOE')

Table name                          Owner         NUM_ROWS     BLOCKS
Size (m)  LAST_ANALYSED       
------------------------------ ---------------- ---------- ----------
--------  ------------------- 
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 10.1.0.4

B. Polarski

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 27 2006 - 03:47:43 CDT