Re: Clustering factor smaller than table blocks.

  • From: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
  • To: Bernard.Polarski@xxxxxxxxxxxxxx
  • Date: Wed, 27 Sep 2006 12:05:14 +0300

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:

          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', 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

B. Polarski

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

I blog at :
"Winners don't do different things. They do things differently."

Other related posts: