From oracle-l-bounce@freelists.org Fri Mar 12 12:06:00 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2CI60m27572 for ; Fri, 12 Mar 2004 12:06:00 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2CI60o27567 for ; Fri, 12 Mar 2004 12:06:00 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D65C0395AAD; Fri, 12 Mar 2004 13:05:13 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 12 Mar 2004 13:03:54 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from brmea-mail-3.sun.com (brmea-mail-3.Sun.COM [192.18.98.34]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3015B394911 for ; Fri, 12 Mar 2004 13:03:52 -0500 (EST) Received: from phys-giza-1 ([129.147.4.102]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id i2CI99ex006522 for ; Fri, 12 Mar 2004 11:09:09 -0700 (MST) Received: from sun.com (sr1-ubrm-14.Central.Sun.COM [129.147.4.67]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HUH008EA6F8EG@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Fri, 12 Mar 2004 11:09:09 -0700 (MST) Date: Fri, 12 Mar 2004 11:09:08 -0700 From: Mike Spalinger Subject: Re: how to detect skew in a column? In-reply-to: <219647973E94D211A8F20008C709725211D6B646@mercury.hq.vetsmart.com> To: oracle-l@freelists.org Message-id: <4051FCC4.5050502@sun.com> MIME-version: 1.0 Content-Type: text/plain; format=flowed; charset=us-ascii X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.2.1) Gecko/20030711 References: <219647973E94D211A8F20008C709725211D6B646@mercury.hq.vetsmart.com> X-archive-position: 712 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Michael.Spalinger@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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@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@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 -----------------------------------------------------------------