Re: Dan Tow's SQL formula

[Resend. I'm forced to trim quoted text down when posting a message. Complete 
original message can be found at 
http://www.freelists.org/post/oracle-l/Dan-Tows-SQL-formula]

> To better my understanding I did a test with more familiar table which is
> scott.emp table with following query:
>
> select sum(count(mgr)*count(mgr)) / (sum(count(mgr))*sum(count(*)))
> from emp
> group by mgr
>
> SUM(COUNT(MGR)*COUNT(MGR))/(SUM(COUNT(MGR))*SUM(COUNT(*)))
> ----------------------------------------------------------
>                                                 .225274725
>
>
> So I got a selectivity of roughly 0.225. Does not sound right to me because
> there are 6 managers and 13 employees (14 but one has no manager)
>
> My question is really, does anyone understand this formula and actually use
> it?
>
> If so please throw some lights :-)))
>
>
> Thank you all
>
> Alex

I don't know the answer. But Dan Tow's formula looks very much like Jonathan 
Lewis' calculation of density when he describes histograms (see his book on 
p.172), or Ari Mozes's patent 6732085 
(http://www.freepatentsonline.com/6732085.html) where he says "density can be 
calculated as the sum of the square of the repetition counts for non-popular 
values divided by the product of the number of rows in the table and the number 
of non-popular values in the table", which is kind of beyond my knowledge.

Unfortunately, the number you get by applying Dan's formula to scott.emp, 
.225274725, is NOT the density for the mgr column, which is .038461538 on my 
database. But it's close to index selectivity (ix_sel) shown in 10053 trace 
when an index is created on mgr column and a query has "where mgr = <some 
number>" is parsed.

Yong Huang



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

Other related posts: