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:
