Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Clustering factor smaller than table blocks.
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 27 2006 - 04:53:28 CDT
![]() |
![]() |