Re: how to detect skew in a column?

From: Mike Spalinger <Michael.Spalinger_at_Sun.COM>
Date: Fri, 12 Mar 2004 11:09:08 -0700
Message-id: <4051FCC4.5050502@sun.com>

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_at_freelists.org
> 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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Mar 12 2004 - 12:06:00 CST