Table access prediction based on clustering factor

  • From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 27 Sep 2006 11:20:41 +0200

Sorry for my previous mail was sent before I completed it due to me
hitting bad keystroke

Here is what I want to express:

If  I have a rowset of 20, and avg block of 31 and the best CF possible,
then I will access only one block, maybe two if my rowset overspill a
block boundary.
And If my clustering factor is the worst possible then I will access 20
table blocks.

So for a table total count block of 345, rownum of 32000 and CF of 345 (
best case), given a rowset of 20 I will access at most 2 blocks
Again for a table total count block of 345, rownum of 32000 and CF of
32000 (worst case), rowset of 20 I will access 20  blocks.

So I derived  the following formula ( index clustering factor / table
blocks)  / (table num rows/table blocks) = table block accessed 

I obtain 1% in the best case wheen CF = table blocks
I get 100% in the worst case when CF = table num_rows :

          (345/345) / (32000/345) * 100 = .011623535
          (32000/345)/(32000/345) * 100 = 100

Let's call this ratio R

Obiously I can retrieve the min and max, but still given the same rowset
if I am told that the CF is now 45% how does it translate ?

Can we assume that I will access the number of table block equal to 45%
of my index rowset of 20, 
say that predicted table acces block will be equal to (rowset/R*100) so
here 20/45*100=9 blocks?

B. Polarski

Other related posts: