Re: Dan Tow's SQL formula

I figured out what that 0.225274725 number is. Create an index on mgr column. 
For each distinct mgr value, generate 10053 trace for SQLs

select * from emp where mgr=<each of the mgr values>

Find all index selectivities (ix_sel) in the trace file(s). Get a 
weight-modified average of them as follows (in desc order of mgr value):

(0.15385*2+0.38462*5+0.076923*1+0.076923*1+0.23077*3+0.076923*1)/14 = 
.22527707142857142857

where the numbers 0.xxx in the parentheses are index selectivities and the 
numbers they're multiplied by are the number of occurrencies for the 
corresponding mgr for which the ix_sel is obtained. 14 is the number of rows.

But it still needs some work to match this calculation with Dan's formula.

Yong Huang

--- On Thu, 2/26/09, amonte <ax.mount@xxxxxxxxx> wrote:

> From: amonte <ax.mount@xxxxxxxxx>
> Subject: Re: Dan Tow's SQL formula
> To: yong321@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Date: Thursday, February 26, 2009, 1:12 AM
> 
> I am having some private mailing with Dan, basically I came
> up with 3
> possible selectivities
> 
> 
> 
> 1. Dan's formula gets a selectivity of 0.225274725
> 2. 1/NDV, 0.166666667
> 3. With histograms for different value we get different
> result if we use
> literals and not binds so for example mgr = 7698 we would
> get a selectivity
> of *count(mgr) where mgr = 7698 / count(mgr)* which is 5/13
> which is
> 0.384615385. But for mgr = 7782 we would get an selectivity
> of 0.076923077
> (much better and lower selectivity) but with binds we know
> what is the
> problem, the bind peeking
> 
> Thanks for the reply
> 
> 
> Alex


      
--
http://www.freelists.org/webpage/oracle-l

Other related posts: