Is this a good definition for clustering factor
- From: Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx>
- To: oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 16 Feb 2005 19:01:20 -0400
Hi List after googling I formulated this definition, is tihs right, or
I missed some point, thanks.
Clustering Factor
The clustering factor is used by the optimizer when a large number of
data is gathered.
When an index can be used to access data, Oracle looks the clustering
factor information on the index.
This Clustering factor tells oracle, how much blocks will need to be
read to get the data required by the query condition.
If to get data, oracle is going to read almost al the table, then the
optimizer choose a full table scan without using the index.
A high clustering factor means more blocks needed to read data because
the value on that field(s) are spread across several database blocks,
and conversely a low clustering factor means the value on that
field(s) are spread in few database blocks.
The value stored in the clustering factor is a counter, registering
the number of times the index pointed to another table block when the
index was scaned in order.
When the number of rows is higher then the number of blocks.
If the count is close to the number of blocks, then the table is
ordered according to the index.
(index entries in a given leaf block point to the same block )
If the count is close to the number of rows, then the table is NOT
ordered according to the index.
(index entries in a given leaf block point to different blocks)
Clustering factor is present in DBA_INDEXES, DBA_IND_PARTITIONS and
DBA_IND_SUBPARTITIONS view.
Tuning.
Rebuild a index only because is has a high clustering factor is
useless, because the reason for the high clustering factor is the
order of the table, and the only way to change it, is changing the
index or the order of the data in the table (or the way the data is
stored in the table).
You will have to decide the best order in the table based on the index you have.
--
Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i
8 years of experience in Oracle 7,8i,9i,10g and developer 6i
--
//www.freelists.org/webpage/oracle-l
Other related posts:
