Re: Clustering factor smaller than table blocks.

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: Bernard.Polarski@xxxxxxxxxxxxxx
  • Date: Wed, 27 Sep 2006 11:31:12 +0200

Or have your perhaps queried the wrong information in your example ?

You're gathering statistics into sm_stattab - not into the data dictionary.
Have you perhaps then queried the data dictionary to get your stats ? This
could easily be misleading - when the stats in the DD came from a gather
stats job with less than 100% sample size ?

Just an idea.

Stefan

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*
****

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006