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
