# Re: Dan Tow's SQL formula

Date: Thu, 26 Feb 2009 06:22:41 -0800 (PST)

Message-ID: <281358.28798.qm_at_web80607.mail.mud.yahoo.com>

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_at_gmail.com> wrote:

*> From: amonte <ax.mount_at_gmail.com>*

*> Subject: Re: Dan Tow's SQL formula*

*> To: yong321_at_yahoo.com*

*> Cc: oracle-l_at_freelists.org*

*> 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-lReceived on Thu Feb 26 2009 - 08:22:41 CST