Dan Tow's SQL formula
Date: Sun, 22 Feb 2009 20:15:33 +0100
A few months ago I was asking some SQL tuning question and someone sggested me Dan Tow's SQL Tuning book release by Oreilly. So I bought the book a few weeks ago and just started to reading it.
I am a bit puzzled about the formula in page 30, calculating the filter selectivity for a query which searchs for last_name:
where last_name = 'SMITH'
And he suggested this formula to obtain the selectivity, sum of (n(i)/C') x
(n(i)/C) where n(i) is the count of rows wiyj ith nonnull last name (I
understand as count of all last_name) and C is count of all rows and C' is count of all nonnull last name which turns out to be following SQL statement
(which I dont understand)
SELECT SUM(COUNT(Last_Name)*COUNT(Last_Name)) /
GROUP BY Last_Name;
To start with the formula is not exactly as described, there are are 3 SUM() instead of one.
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(*)))
group by mgr
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
AlexReceived on Sun Feb 22 2009 - 13:15:33 CST