Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Table access prediction based on clustering factor
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 27 2006 - 04:20:41 CDT