Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> Table access prediction based on clustering factor

# Table access prediction based on clustering factor

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Wed, 27 Sep 2006 11:20:41 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D568715@mailserver1>

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-l
```
Received on Wed Sep 27 2006 - 04:20:41 CDT

Original text of this message

 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US