Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Clustering factor smaller than table blocks.

Re: Clustering factor smaller than table blocks.

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Wed, 27 Sep 2006 11:31:12 +0200
Message-ID: <486b2b610609270231p38473540l1ec12bb8f12d101@mail.gmail.com>


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_at_atosorigin.com> 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*
> ****
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 27 2006 - 04:31:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US