RE: Clustering factor smaller than table blocks.
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
- Date: Wed, 27 Sep 2006 11:53:28 +0200
Tank for the links, I did not know it at all.
I read dizz comment about the possibility of having a smaller block
counter :
"
The best possible clustering factor you can get, assuming your table is
packed as tightly as it can be, is the number of blocks in the table
containing data (obviously, an index will never send you to a completely
empty block, because there won't be any index entries that point to such
a block). That would correspond to the BLOCKS column in DBA_TABLES after
fresh statistics have been collected on the table. If a table has been
subject to lots of deletes, then BLOCKS records the total number of
blocks ever to have contained data (because they're under the table's
High Water Mark), but the index will only ever send you to blocks which
continue to contain data. So it is in fact possible to have a clustering
factor smaller than BLOCKS... and that indicates major space problems on
the table, and not actually a problem with the index at all.
"
If I read it correctly then a table whose number of blocks in PK
inferior to number of blocks reported in dba_Tables had endure delete.
There are empty blocks unde the HW. That's why I hadded the count of
'empty block'.
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
So I still don't understand why dba_Tables reports 368 blocks while pk
index CF is 345.
Regards,
B. Polarski
--
//www.freelists.org/webpage/oracle-l
Other related posts:
