RE: Clustering factor smaller than table blocks.

Not sure to understand, It is the primary key. 

-----Original Message-----
From: Syed Jaffar Hussain [mailto:sjaffarhussain@xxxxxxxxx] 
Sent: Wednesday, 27 September, 2006 11:05 AM
To: Polarski, Bernard
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Clustering factor smaller than table blocks.

Are the values for the indexed column are sequential values?
CF depends on how the values organized in bloks of this column.

On 9/27/06, Polarski, Bernard <Bernard.Polarski@xxxxxxxxxxxxxx> wrote:
>
>
>
> 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
>


--
Best Regards,
Syed Jaffar Hussain
8i,9i & 10g OCP DBA

I blog at :http://jaffardba.blogspot.com/

http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
------------------------------------------------------------------------
----------
"Winners don't do different things. They do things differently."



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


Other related posts: