Josh,

I guess the first step is to decide what your definition of "SKEW" is.
Maybe it's defined as the most common value is x% more common than the
least common value? Then, you can adjust x to meet your liking.

Here's an example,
SQL> select * from mike;

COL1
----------
a
a
a
b
c

select col1, cnt, ratio_to_report(cnt) over() * 100 || '%' rtr
  from (select col1, count(*) cnt from mike group by col1);

COL1         CNT RTR
---------- ---------- ----------
a             3 60%
b             1 20%
c             1 20%


"a" occurs 60% of the time and b and c each occur 20% of the time.

This query checks if the difference is greater than 30:

SQL> select max(rtr), min(rtr), max(rtr) - min(rtr)
 2   from (select col1, ratio_to_report(cnt) over() * 100 rtr
 3      from (select col1, count(*) cnt from mike group by col1))
 4   having max(rtr) - min(rtr) > 30;

 MAX(RTR)  MIN(RTR) MAX(RTR)-MIN(RTR)
---------- ---------- -----------------
     60      20           40

You would have to do this for all columns.

Mike

Josh Collier wrote:
> Greetings,
>
> I am trying to write some perl to query the database and spit out list of
> candidate columns for histograms. I know that dbms_stats has the SKEWONLY
> method opt, but that also builds histograms automatically, I don't want to
> do that, just want candidates that I can look investigate. I've traced a
> dbms_stats session with method opt 'SKEW ONLY', so I think I see how
> dbms_stats detects skew, but I'm still investigating.
>
> Any ideas how to identify columns that have skew in them? Besides the
> eyeball approach: select count(*),val from table group by val and examine
> the result set.
>
> josh
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------