-------------------------------------------------------
--  what : slctivty.sql 
--  when : 09-MAY-2006
--   why : This little script measures the selectivity of 
--         a column to see if it is a good candidate for
--         indexing.
-- where : The query is borrowed straight out of Dan Tow's
--         excellent book, _SQL Tuning_ (O'Reilly, 2004).
--         See pp. 28-30. 
--   how : Returns the selectivity of a column as the
--         fraction of rows in a table likely to satisfy
--         an equijoin condition on that column. So a 
--         high value is what is usually referred to as 
--         *low* selectivity.
--   who : David Clement
--   fix :
-----------------------------------------------------------

select sum (power (count (&&column_name), 2) )
/ (sum (count (&&column_name)) * sum (count (*) ) )
from &&table_name 
group by &&column_name
/