Re: Index CLUSTING_FACTOR

username <PARTKK_at_USCCMAIL.uscc.bms.com> wrote:

>Does anybody have any info about CLUSTURING FACTOR of an Index? I am
>looking into tuning table access and I came accross this parameter in
>Index Statistics.
 

>I would appreciate any thoughts/ideas on this as to what is its impact
>and how to improve CLUSTERING FACTOR.

Hi,
Clustering factor is not really a factor. Let see how it works.

The minimum blocks needed for accessing all the row in a table is the table's number of blocks.
The maximum, is the table's number of rows.

Clustering factor is a number between those 2 values. It is represent the number of blocks that oracle will read for a full table scan via this index.
(As I said before, it is not a factor)

Now, lets look on those 2 indexes.

Index 1:

values          Table block
1		2
5		1
5		3
6		2
7		1
8		3
9		2

Index 2:
values          Table block
1		1
5		1
5		2
6		2
7		2
8		3
9		3

Index 1 is order in different order then his table. We may need to read 7 blocks to get all the information. (7 rows)

Index 2 is order in the same order as his table. We have to read maximum of 3 blocks to get all the information.

This factor shuold be a parameter when you decide what index to use to access a table.
Reorg a table will change the clustering fuctor.

Regards,

   Izack Varsanno. Received on Mon Jan 27 1997 - 00:00:00 CST